JSON_SERIALIZE in Oracle Database 19c

The JSON_SERIALIZE function converts a JSON document from any supported data type to text.

◉ The Problem

We can store JSON data in a number of different data types, including binary types. Let's create a test table to demonstrate the issue.

-- DROP TABLE json_documents PURGE;

CREATE TABLE json_documents (

  id    NUMBER,

  data  BLOB,

  CONSTRAINT json_documents_is_json CHECK (data IS JSON)

);

INSERT INTO json_documents (id, data) VALUES (1, '{"id":1,"first_name":"Iron","last_name":"Man"}');

COMMIT;

If we try to display the data directly, we don't get anything useful.

SELECT data FROM json_documents;

DATA

--------------------------------------------------------------------------------

7B226964223A312C2266697273745F6E616D65223A2249726F6E222C226C6173745F6E616D65223A

SQL>

Read More: 1Z0-064: Oracle Database 12c - Performance Management and Tuning

We can manually convert a BLOB to a CLOB. For example, we can use the BLOB_TO_CLOB function created by the blob_to_clob.sql script, or for small amounts of data using the UTL_RAW package.

SELECT blob_to_clob(data) AS data FROM json_documents;

DATA

--------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

SELECT UTL_RAW.cast_to_varchar2(data) AS data FROM json_documents;

DATA

--------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

We could also use the JSON_QUERY function to return the whole document, rather than a fragment.

SELECT JSON_QUERY(data, '$') AS data FROM json_documents;

DATA

----------------------------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

1 row selected.

SQL>

◉ JSON_SERIALIZE Basic Usage

The documentation provides the following description of the JSON_SERIALIZE function.

JSON_SERIALIZE (target_expr [ json_query_returning_clause ] [ PRETTY ]

                [ ASCII ] [ TRUNCATE ] [ json_query_on_error_clause ])

The target expression is the JSON we want to convert.

In its basic form we can convert the JSON data from any supported data type to text, similar to what we did with the BLOB_TO_CLOB function.

SELECT JSON_SERIALIZE(data) AS data FROM json_documents;

DATA

--------------------------------------------------------------------------------

{"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

We can use the JSON_SERIALIZE function to convert the output from other SQL/JSON calls. In this case we use the JSON_OBJECT function to produce a JSON document in binary form, then convert it text using the JSON_SERIALIZE function.

SELECT JSON_SERIALIZE(

         JSON_OBJECT(empno, ename, hiredate RETURNING BLOB)

       PRETTY) AS data

FROM   emp

WHERE  empno = 7369;

DATA

--------------------------------------------------------------------------------

{

  "empno" : 7369,

  "ename" : "SMITH",

  "hiredate" : "1980-12-17T00:00:00"

}

SQL>

◉ Format Output

The returning clause works like that of the other SQL/JSON functions, as described here.

The PRETTY keyword displays the output in a human readable form, rather than minified.

SELECT JSON_SERIALIZE(a.data PRETTY) AS data

FROM   json_documents a

WHERE  a.data.first_name = 'Iron';

DATA

--------------------------------------------------------------------------------

{

  "id" : 1,

  "first_name" : "Iron",

  "last_name" : "Man"

}

SQL>

The TRUNCATE keyword indicates the output should be truncated to fit the return type. In the following example the return type is VARCHAR2(10), so the output is truncated to fit.

SELECT JSON_SERIALIZE(a.data RETURNING VARCHAR2(10) TRUNCATE) AS data

FROM   json_documents a

WHERE  a.data.first_name = 'Iron';

DATA

---------------------------------------------------------------------------------

{"id":1,"f

SQL>

Unlike some of the other SQL/JSON functions, the TRUNCATE function doesn't seem necessary, as it seems to truncate the output to match the returning clause.

SELECT JSON_SERIALIZE(a.data RETURNING VARCHAR2(10)) AS data

FROM   json_documents a

WHERE  a.data.first_name = 'Iron';

DATA

---------------------------------------------------------------------------------

{"id":1,"f

SQL>

The ASCII keyword indicates the output should convert any non-ASCII characters to JSON escape sequences.

◉ Error Handling

If there are any failures during the processing of the data the default response is to return a NULL value. The way an error is handled can be specified explicitly with the ON ERROR clause.

-- Default behaviour.

SELECT JSON_SERIALIZE('This is not JSON!' NULL ON ERROR) AS data

FROM   dual;

DATA

--------------------------------------------------------------------------------

SQL>

SELECT JSON_SERIALIZE('This is not JSON!' ERROR ON ERROR) AS data

FROM   dual;

       *

ERROR at line 2:

ORA-40441: JSON syntax error

SQL>

◉ PL/SQL Support

There is no support for JSON_SERIALIZE in direct PL/SQL assignments.

SET SERVEROUTPUT ON

DECLARE

  l_blob  BLOB;

  l_clob  CLOB;

BEGIN

  l_blob := UTL_RAW.cast_to_raw('{"id":1,"first_name":"Iron","last_name":"Man"}');

  l_clob := JSON_SERIALIZE(l_blob);

  DBMS_OUTPUT.put_line('After : ' || l_clob);

END;

/

  l_clob := JSON_SERIALIZE(l_blob);

            *

ERROR at line 6:

ORA-06550: line 6, column 13:

PLS-00201: identifier 'JSON_SERIALIZE' must be declared

ORA-06550: line 6, column 3:

PL/SQL: Statement ignored

SQL>

The simple workaround for this is to make the assignment using a query from dual.

DECLARE

  l_blob  BLOB;

  l_clob  CLOB;

BEGIN

  l_blob := UTL_RAW.cast_to_raw('{"id":1,"first_name":"Iron","last_name":"Man"}');

  SELECT JSON_SERIALIZE(l_blob)

  INTO   l_clob

  FROM   dual;

  DBMS_OUTPUT.put_line('After : ' || l_clob);

END;

/

After : {"id":1,"first_name":"Iron","last_name":"Man"}

SQL>

◉ 21c Update

Oracle 21c introduced the JSON data type. The JSON_SERIALIZE function also support this new data type.