JSON DataType Support in Oracle 21c

February 26, 2021 | 5 minute read
Zhen Hua Liu
Text Size 100%:

Oracle database release 21c introduces a JSON datatype, which is an optimized native binary storage format using the OSON format, to store and process JSON data. Compared with JSON text storage, it delivers performance benefits to your JSON applications, including faster query performance, more efficient updates without the need of parsing JSON text.

JSON datatype can be used as column of a table, (materialized) view, PL/SQL procedure/function parameter, return value in Oracle Database 21c.   JDBC and OCI clients have provided support JSON datatype API in the 21c version.  A SODA collection uses JSON datatype as default to store JSON data.

The following examples shows how to create a table with a  json datatype column.

You can try out these example in a 21c (always free) Autonomous Database.

create table person_collection (id number primary key,  jdoc JSON);
insert into person_collection values(1,'{"person":{"birthdate":"1998-03-04",
                                         "creditscore":[700, 650, 720]}}' );

Multi-Value functional index and JSON_Transform() are two new 21c JSON features that are specifically designed for JSON datatype to improve query and update performance.


Multi-Value Functional Index

Prior to 21c, users can only use a single-value functional index to speed up JSON_VALUE()  predicate evaluation. Previously, a functional index was restricted to index at most one value per row – for JSON that meant a field value having at most one occurrence. In the above example, a person has at most one “birthdate” field, so the following single-value functional index can be created.

create index birthIdx
  on person_collection t (t.jdoc.person.birthdate.date());

explain plan
for select count(*) from
person_collection t
where JSON_VALUE(t.jdoc, '$.person.birthdate' returning date)  < :1;

|   0 | SELECT STATEMENT  |          |     1 |     9 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| BIRTHIDX |     1 |     9 |     0   (0)| 00:00:01 |

Please note that the index was created using simple dot notation (t.jdoc.person.birthdate.date())  which is equivalent to the JSON_VALUE() operator that is able to make use of the index.

In 21c, a user can create a multi-value functional index on a JSON datatype column to index elements within a JSON array. This speeds up the evaluation of JSON_EXISTS() – an operator allowing the use array of comparison predicates of the SQL/JSON path language.

In the example,  "person.creditscore" is an array of numbers. We can now create an index on all values of the array using a new syntax as follows:

create multivalue index creditsIdx
on person_collection t (t.jdoc.person.creditscore[*].number());

Please note the trailing function 'number()' which returns the credit score values as a sequence of numbers. 'string()' would be the option to index the values as strings, giving different results for range queries.

explain plan for
select count(*) from person_collection t
where json_exists(t.jdoc, '$.person.creditscore[*]?(@.number() > 700)')

|   0 | SELECT STATEMENT                 |            |     1 |    13 |     1

|   1 |  SORT AGGREGATE                  |            |     1 |    13 |
|   2 |   HASH UNIQUE                    |            |     1 |    13 |
|*  3 |    INDEX RANGE SCAN (MULTI VALUE)| CREDITSIDX |     1 |    13 |     1

The multi-value functional index is only supported on JSON datatype column, for now.


Update JSON via JSON_Transform()

A new operator JSON_Transform() supports declaratively updating JSON datatype instances without any overhead of parsing JSON text . If possible JSON_Transform performs a partial update: For example, when  updating  the first “creditscore” value, JSON_Transform performs a  partial update of the JSON data on disk avoiding a full update of the whole document. This significantly improves performance because it reduces database redo log size, especially when updating medium to large JSON documents.

update person_collection t
set t.jdoc = JSON_Transform(t.jdoc, set '$.person.creditscore[0]' = 710);

select jdoc from person_collection t


Existing SQL/JSON Functions for JSON datatype

All SQL/JSON query functions that accept JSON text as input also support  JSON datatype. All SQL/JSON generation functions can generate JSON datatype. In order to create a JSON type instance from textual JSON, there is a new constructor function called JSON(). To create a scalar JSON type instance from a SQL value, a new constructor function JSON_SCALAR() is added. This is because JSON type supports additional data types, it is possible to create a JSON type instance holding a DATE or TIMESTAMP value. Upon conversion to textual JSON, such value would have to be converted (serialized) to a string value because JSON text representation has no support of such types. JSON_Serialize() is the function to convert a JSON type instance to a JSON text. It gives the option to pretty print the data to make it easier to read.

select JSON_Serialize (jdoc PRETTY) from person_collection
  "person" :
    "birthdate" : "1998-03-04",
    "creditscore" :

The SQL/JSON function JSON_QUERY will now return a JSON type by default if the input is a JSON type. The existing default of VARCHAR2(4000) is maintained if the input is not a JSON type. Conceptually, JSON_QUERY is designed to return a JSON fragment – and in the case of a JSON type input, the fragment would also be a JSON type.

This default change has implication on the JSON simple dot notation syntax (which relies on JSON_QUERY) – it will also return a JSON type if evaluated on a JSON type.

As two JSON types are not directly comparable (in the same way you cannot compare an Object to an Array or Boolean value), one has to provide a trailing item method at the end of a simple dot notation expression in order to convert it to a comparable SQL value. This also needs to be done if the simple dot notation is used in a group by or order by or range comparison. Otherwise you'll see an ORA-40796 invalid comparison operation involving JSON type value.

For example

select t.jdoc.person.birthdate.date(), count(*)
from  person_collection t
where t.jdoc.person.birthdate.date() between sysdate - 40500 and sysdate  
order by t.jdoc.person.birthdate.date();

---------   --------
04-MAR-98          1

JSON datatype is fully integrated into all components of the 21c database. JSON search indexJSON_TABLE() materialized viewJSON dataguide, Parallel Query Processing, in-memory JSON storeOracle ExaData Smart Scanblockchain table have all been enhanced to fully take advantage of OSON formats to speed up JSON document processing.


21c Client Driver native JSON datatype Support

JSON datatype is transferred between 21c clients and 21c server without textual conversion. This not only allows the client to use the extended types (e.g Timestamp) but also greatly improves performance as it saves database CPU by avoiding JSON encoding on ingest, and text serialization on output.  Oracle 21c clients are also capable of directly processing the binary JSON format (OSON) to directly support JSON APIs – for example to navigate within a JSON datatype instance. JDBC JSON datatype supports  via oracle.sql.json interface and OCI JSON datatype supports C JSON API.  It is highly recommended to use 21c client drivers when working with JSON datatype. Otherwise, JSON datatype instances have to be converted to JSON text which loses binary efficiency and certain datatypes ((e.g. timestamp) that JSON text representation can not support.  Oracle instance client drivers can be downloaded here.  However, SODA default collections created in Oracle Database 21c must use the 21c SODA driver to make use of JSON type.


Migration to JSON Datatype from JSON Text Storage

JSON text storage can be migrated to JSON datatype via online redef migration to json datatype.


Zhen Hua Liu


The leading architect for semi-structured data management and multi-model data management for Oracle Converged Database Server and Autonomous Database Cloud Servcie. The originator of SQL/JSON standard for DBMS industry. Principal architect for Oracle database server support of SQL/JSON, SQL/XML. Original designer and developer of Oracle optimized binary JSON format, aka, OSON, SQL/JSON path OSON processor engine, JSON, full text, spatial in memory database processing, JSON  sharding support, , JSON/XML search index, JSON DOM API design. One of the Oracle standard representatives to standardize SQL/JSON, SQL/XML, W3C XQuery, SQL/PGQ (Property Graph Query Language). Prior to Oracle, he had done research and development of Object Relational DBMS with integration of Postgres/Illustra into Informix  RDBMS and had done  development of ANSI SQL 89/92 standard compliance feature for Sybase RDBMS.

Previous Post

Why applications work better on Autonomous Database than on PostgreSQL on AWS

Navita Sood | 8 min read

Next Post

Automatically Scaling Exadata Cloud Service Based on Workload (Part 1)

Bob Thome | 7 min read