DBA Hub

📋Steps in this guide1/4

JSON Data Type in Oracle Database 21c

The JSON data type was introduced in the Oracle 20c preview release to provide native JSON support and improve the performance of JSON processing. It has become generally available in Oracle 21c.

oracle 21cconfigurationintermediate
by OracleDba
57 views
1

JSON Data Type

The data type is an Oracle optimized binary JSON format called OSON. It is designed for faster query and DML performance in the database and in database clients from version 20c/21c upward. We create a column in a table using the data type, much like we would with any other data type. We can populate the table using JSON data from a number of sources using the constructor. Some inserts will work fine without explicitly using the JSON constructor, but it makes sense to use it explicitly. The data is stored in the table in binary format, so a basic query isn't very useful.

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
-- drop table t1 purge;

create table t1 (
  id         number generated always as identity,
  json_data  json,
  constraint ta_pk primary key (id)
);

declare
  l_varchar2  varchar2(32767);
  l_clob      clob;
  l_blob      blob;
begin
  l_varchar2 := '{"fruit":"apple","quantity":10}';
  l_clob     := '{"fruit":"orange","quantity":20}';
  l_blob     := utl_raw.cast_to_raw('{"fruit":"banana","quantity":30}');

  insert into t1 (json_data) values (json(l_varchar2));
  insert into t1 (json_data) values (json(l_clob));
  insert into t1 (json_data) values (json(l_blob));
  commit;
end;
/

set linesize 200
column json_data format a65

select * from t1;

        ID JSON_DATA
---------- -----------------------------------------------------------------
         1 7B226672756974223A226170706C65222C227175616E74697479223A31307D
         2 7B226672756974223A226F72616E6765222C227175616E74697479223A32307D
         3 7B226672756974223A2262616E616E61222C227175616E74697479223A33307D

SQL>
2

Query JSON Data

The function is used to convert JSON from any supported type into text. It was introduced in Oracle 19c, but it has been extended to support the data type. We could query values using the normal SQL/JSON functions introduced in previous releases, which also support the new type. Here is an example of using the function. We can also query the data using dot notation. Remember the data is binary, so we have to convert it to 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
set linesize 200
column json_data format a50

select id, json_serialize(json_data) as json_data from t1;

        ID JSON_DATA
---------- --------------------------------------------------
         1 {"fruit":"apple","quantity":10}
         2 {"fruit":"orange","quantity":20}
         3 {"fruit":"banana","quantity":30}

SQL>

column fruit format a10

select a.id,
       json_value(a.json_data, '$.fruit') as fruit,
       json_value(a.json_data, '$.quantity' returning number) as quantity
from   t1 a
order by 1;

        ID FRUIT        QUANTITY
---------- ---------- ----------
         1 apple              10
         2 orange             20
         3 banana             30

SQL>

column fruit format a10
column quantity format a10

select a.id,
       json_query(a.json_data, '$.fruit' returning varchar2) as fruit,
       json_query(a.json_data, '$.quantity' returning varchar2) as quantity
from   t1 a
order by 1;

        ID FRUIT      QUANTITY
---------- ---------- ----------
         1 "apple"    10
         2 "orange"   20
         3 "banana"   30

SQL>

select a.id,
       jt.fruit,
       jt.quantity
from   t1 a,
       json_table(a.json_data, '$'
         columns (fruit    varchar2(10 char) path '$.fruit',
                  quantity number path '$.quantity')) jt;

        ID FRUIT        QUANTITY
---------- ---------- ----------
         1 apple              10
         2 orange             20
         3 banana             30

SQL>

select a.id,
       json_serialize(a.json_data.fruit) as fruit,
       json_serialize(a.json_data.quantity) as quantity
from   t1 a
order by 1;

        ID FRUIT      QUANTITY
---------- ---------- ----------
         1 "apple"    10
         2 "orange"   20
         3 "banana"   30

SQL>
3

JSON_SCALAR Function

The function creates an instance of a JSON type from a SQL scalar value. We can see what we created by converting the value back to 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
column scalar_number format A20
column scalar_string format A20
column scalar_date format A50

select json_scalar(1) as scalar_number,
       json_scalar('string') as scalar_string,
       json_scalar(date '2020-12-13') as scalar_date
from   dual;

SCALAR_NUMBER        SCALAR_STRING        SCALAR_DATE
-------------------- -------------------- --------------------------------------------------
31                   22737472696E6722     22323032302D31322D31335430303A30303A303022

SQL>

select json_serialize(json_scalar(1)) as scalar_number,
       json_serialize(json_scalar('string')) as scalar_string,
       json_serialize(json_scalar(date '2020-12-13')) as scalar_date
from   dual;

SCALAR_NUMBER        SCALAR_STRING        SCALAR_DATE
-------------------- -------------------- --------------------------------------------------
1                    "string"             "2020-12-13T00:00:00"

SQL>
4

PL/SQL Object Types for JSON Support

For many operations it may be simpler to use the function, introduced in Oracle database 21c, rather than PL/SQL Object Types for JSON. If you do need to use PL/SQL Object Types for JSON, that's no problem. The constructor supports the new data type. The following example retrieves a value from the table and converts it to a type. We can then process it with the PL/SQL Object Types for JSON. Once we've finished processing the JSON data in the object, we can convert it back to a data type using the member function, and use that to amend the database. There are a number of new member functions, as well as overloads of existing member functions that support the new data type. 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
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
46
47
48
49
50
51
52
53
set serveroutput on
declare
  l_json  JSON;
  l_obj   json_object_t;
begin
  -- Get the JSON data.
  select json_data
  into   l_json
  from   t1
  where  id = 1;
  
  -- Create a JSON_OBJECT_T object and output the contents.
  l_obj := json_object_t(l_json);
  dbms_output.put_line('l_obj.stringify = ' || l_obj.stringify);
end;
/
l_obj.stringify = {"fruit":"apple","quantity":10}

PL/SQL procedure successfully completed.

SQL>

set serveroutput on
declare
  l_json  JSON;
  l_obj   json_object_t;
begin
  -- Get the JSON data.
  select json_data
  into   l_json
  from   t1
  where  id = 1;
  
  -- Create a JSON_OBJECT_T object and output the contents.
  l_obj := json_object_t(l_json);
  dbms_output.put_line('l_obj.stringify = ' || l_obj.stringify);

  -- Convert it back to JSON.
  l_json := l_obj.to_json;
  dbms_output.put_line('l_json = ' || json_serialize(l_json));
  
  -- Update the JSON column.
  update t1
  set    json_data = l_json
  where  id = 1;
end;
/
l_obj.stringify = {"fruit":"apple","quantity":10}
l_json = {"fruit":"apple","quantity":10}

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!