Monday, May 8, 2023

JSON_SERIALIZE in Oracle Database 19c

Oracle Database 19c, Oracle Database Career, Oracle Database Prep, Oracle Database Preparation, Database Skills, Database Jobs, Database Guides, Database Learning

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>

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>

Oracle Database 19c, Oracle Database Career, Oracle Database Prep, Oracle Database Preparation, Database Skills, Database Jobs, Database Guides, Database Learning
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 : JSON Data Type Support


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

23c Update : ORDERED Keyword


In Oracle 23c the ORDERED keyword has been added to the JSON_SERIALIZE function. When present, the members are serialized in ascending alphabetical order by field name.

declare
  l_json  json;
  l_clob  clob;
begin
  l_json := json('{"id":1,"first_name":"Iron","last_name":"Man"}');

  select json_serialize(l_json ordered)
  into   l_clob;

  dbms_output.put_line('After : ' || l_clob);
end;
/
After : {"first_name":"Iron","id":1,"last_name":"Man"}

SQL>

Source: oracle-base.com

Related Posts

0 comments:

Post a Comment