8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON_SERIALIZE in Oracle Database 19c
The JSON_SERIALIZE
function converts a JSON document from any supported data type to text.
- The Problem
- JSON_SERIALIZE Basic Usage
- Format Output
- Error Handling
- PL/SQL Support
- 21c Update : JSON Data Type Support
- 23ai Update : ORDERED Keyword
Related articles.
- JSON_SERIALIZE Function in 19c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
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>
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.
23ai Update : ORDERED Keyword
In Oracle 23ai 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>
For more information see:
- JSON_SERIALIZE
- JSON_SERIALIZE Function in 19c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
Hope this helps. Regards Tim...