DBA Hub

📋Steps in this guide1/7

JSON_SERIALIZE in Oracle Database 19c

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

oracle 19cconfigurationintermediate
by OracleDba
33 views
1

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. If we try to display the data directly, we don't get anything useful. We can manually convert a BLOB to a CLOB. For example, we can use the function created by the blob_to_clob.sql script, or for small amounts of data using the package. We could also use the function to return the whole document, rather than a fragment.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 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;

select data from json_documents;

DATA
--------------------------------------------------------------------------------
7B226964223A312C2266697273745F6E616D65223A2249726F6E222C226C6173745F6E616D65223A

SQL>

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>

select json_query(data, '$') as data from json_documents;

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

1 row selected.

SQL>
2

JSON_SERIALIZE Basic Usage

The documentation provides the following description of the function. 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 function. We can use the function to convert the output from other SQL/JSON calls. In this case we use the function to produce a JSON document in binary form, then convert it text using the function.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
JSON_SERIALIZE (target_expr [ json_query_returning_clause ] [ PRETTY ]
                [ ASCII ] [ TRUNCATE ] [ json_query_on_error_clause ])

select json_serialize(data) as data from json_documents;

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

SQL>

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>
3

Format Output

The returning clause works like that of the other SQL/JSON functions, as described here . The keyword displays the output in a human readable form, rather than minified. The keyword indicates the output should be truncated to fit the return type. In the following example the return type is , so the output is truncated to fit. Unlike some of the other SQL/JSON functions, the function doesn't seem necessary, as it seems to truncate the output to match the returning clause. The keyword indicates the output should convert any non-ASCII characters to JSON escape sequences.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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>

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>

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>
4

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 clause.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 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>
5

PL/SQL Support

There is no support for in direct PL/SQL assignments. The simple workaround for this is to make the assignment using a query from dual.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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>

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>
6

21c Update : JSON Data Type Support

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

23ai Update : ORDERED Keyword

In Oracle 23ai the keyword has been added to the function. When present, the members are serialized in ascending alphabetical order by field name. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!