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>
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
0 comments:
Post a Comment