8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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.

JSON Data Type
Query JSON Data
JSON_SCALAR Function
PL/SQL Object Types for JSON Support

JSON Data Type

The JSON 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 JSON data type, much like we would with any other data type.

-- drop table t1 purge;

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

We can populate the table using JSON data from a number of sources using the JSON constructor.

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;
/

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.

set linesize 200
column json_data format a65

select * from t1;

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

SQL>

Query JSON Data

The JSON_SERIALIZE 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 JSON data type.

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>

We could query values using the normal SQL/JSON functions introduced in previous releases, which also support the new JSON type.

Here is an example of using the JSON_VALUE function.

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>

Here is an example of using the JSON_QUERY function.

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>

Here is an example of using the JSON_TABLE function.

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>

We can also query the data using dot notation. Remember the data is binary, so we have to convert it to text using the JSON_SERIALIZE function.

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>

JSON_SCALAR Function

The JSON_SCALAR function creates an instance of a JSON type from a SQL scalar value.

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>

We can see what we created by converting the value back to text using the JSON_SERIALIZE function.

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>

PL/SQL Object Types for JSON Support

For many operations it may be simpler to use the JSON_TRANSFORM 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 JSON_OBJECT_T constructor supports the new JSON data type. The following example retrieves a JSON value from the T1 table and converts it to a JSON_OBJECT_T type. We can then process it with the PL/SQL Object Types for JSON.

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>

Once we've finished processing the JSON data in the JSON_OBJECT_T object, we can convert it back to a JSON data type using the TO_JSON member function, and use that to amend the database.

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>

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

Back to the Top.