Oracle SQL Function JSON

19 Oracle SQL Function JSON_SERIALIZE

Oracle SQL function json_serialize takes JSON data (of any SQL data type) as input and returns a textual representation of it.

Function json_serialize is not itself a query function, but it is typically used to transform the result of a query. It supports an error clause and a returning clause.

You can use json_serialize to convert binary JSON data to textual form (CLOB or VARCHAR2), or to transform textual JSON data by pretty-printing it or escaping non-ASCII Unicode characters in it. An important use case is serializing JSON data that is stored in a BLOB column. A BLOB result is in the AL32UTF8 character set. But whatever the data type returned by json_serialize, the returned data represents textual JSON data..

Example 19-1 Using JSON_SERIALIZE To Convert BLOB Data To Pretty-Printed Text

This example serializes and pretty-prints the JSON purchase order that has 1600 as the value of field PONumber data, which is selected from column po_document of table j_purchaseorder The return-value data type is VARCHAR2(4000) (the default return type).

Example 4-2 shows the insertion of such purchase-order data into a VARCHAR2 table. But json_serialize is especially useful for serializing BLOB data. See Example 9-1 for how to create a table with a BLOB column of JSON data.

SELECT json_serialize(po_document, PRETTY) FROM j_purchaseorder;

Example 19-2 Using JSON_SERIALIZE with Clients

If you use json_serialize to return serialized query data to a client such as Oracle Call Interface (OCI) or Java Database Connectivity (JDBC) in LOB batches, there are some optimizations in particular to consider:

Use the LOB data API. In Java, this means use methods getBytes() and getString(), for BLOB and CLOB data, respectively.
Set the LOB prefetch size to a large number, such as 65 KB.
Free the fetched LOB after each row is read.

Otherwise, performance can be reduced, and memory can leak. This example illustrates how to do this.


Statement stmt = conn.createStatement(); // conn is a java.sql.Connection
stmt.setFetchSize(1000); // Set batch fetch size to 1 KB
// set LOB prefetch size to be 65 KB
((OraclePreparedStatement)stmt).setLobPrefetchSize(65000);
// Query the JSON data in column jdoc of table myTab, returning JSON as a BLOB instance
rset = stmt.executeQuery(
         "SELECT id, json_serialize(jdoc RETURNING BLOB) FROM myTab");
while (rset.next()) { // Iterate over the returned rows
  Blob blob = rset.getBlob (2);
  // Do something with the BLOB instance for the row
  blob.free(); // Free the LOB at the end of each iteration
}
rset.close();