Oracle JSON Functionality
Version 19.2.0.1

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose This page is a single-page collection of links to the new JavaScript Object Notation (JSON) functionality added as new features to Database 12.1.0.2.0 and 12.2.0.1
Dependencies
ALL_JSON_COLUMNS DBMS_FEATURE_JSON STANDARD
CDB_JSON_COLUMNS INT$DBA_JSON_COLUMNS USER_JSON_COLUMNS
DBA_JSON_COLUMNS    
Objects Creation Script {$ORACLE_HOME}/rdbms/admin/catjsonv.sql
Page Sections
 
JSON Table and Column
Create JSON table and constraint conn sys@pdbdev as sysdba

CREATE TABLE uwclass.j_purchase_order (
doc_id      RAW(16)            NOT NULL,
date_loaded TIMESTAMP(6) WITH TIME ZONE,
po_document CLOB);

SQL> SELECT object_id
  2  FROM dba_objects
  3  WHERE owner = 'UWCLASS'
  4  AND object_name = 'J_PURCHASE_ORDER';

OBJECT_ID
----------
     79128

SELECT defer, condition
FROM cdef$
WHERE obj# = 79128;

ALTER TABLE uwclass.j_purchase_order
ADD CONSTRAINT ensure_json
CHECK (po_document IS JSON);

SQL> col object_name format a60

SQL> SELECT owner, object_name, object_type
  2  FROM dba_objects
  3  WHERE object_name like '%JSON%'
  4* ORDER BY 2,3;

OWNER   OBJECT_NAME                                            OBJECT_TYPE
------- ------------------------------------------------------ ----------
PUBLIC  ALL_JSON_COLUMNS                                       SYNONYM
SYS     ALL_JSON_COLUMNS                                       VIEW
PUBLIC  ALL_JSON_DATAGUIDES                                    SYNONYM
SYS     ALL_JSON_DATAGUIDES                                    VIEW
PUBLIC  CDB_JSON_COLUMNS                                       SYNONYM
SYS     CDB_JSON_COLUMNS                                       VIEW
PUBLIC  CDB_JSON_DATAGUIDES                                    SYNONYM
SYS     CDB_JSON_DATAGUIDES                                    VIEW
PUBLIC  DBA_JSON_COLUMNS                                       SYNONYM
SYS     DBA_JSON_COLUMNS                                       VIEW
PUBLIC  DBA_JSON_DATAGUIDES                                    SYNONYM
SYS     DBA_JSON_DATAGUIDES                                    VIEW
SYS     DBMS_FEATURE_JSON                                      PROCEDURE
XDB     DBMS_JSON                                              PACKAGE
XDB     DBMS_JSON                                              PACKAGE BODY
PUBLIC  DBMS_JSON                                              SYNONYM
SYS     DBMS_JSON0                                             PACKAGE
SYS     DBMS_JSON0                                             PACKAGE BODY
XDB     DBMS_JSON_INT                                          PACKAGE
XDB     DBMS_JSON_INT                                          PACKAGE BODY
XDB     DBMS_JSON_LIB                                          LIBRARY
SYS     INT$DBA_JSON_COLUMNS                                   VIEW
SYS     INT$DBA_JSON_DATAGUIDES                                VIEW
XDB     JSON$COLLECTION_METADATA                               TABLE
XDB     JSON$COLLECTION_METADATA_PK                            INDEX
XDB     JSON$COLLECTION_METADATA_V                             VIEW
XDB     JSON$USER_COLLECTION_METADATA                          VIEW
SYS     JSONDGIMP                                              TYPE
SYS     JSONHDGIMP                                             TYPE
PUBLIC  JSON_ARRAY_T                                           SYNONYM
SYS     JSON_ARRAY_T                                           TYPE
SYS     JSON_ARRAY_T                                           TYPE BODY
SYS     JSON_DATAGUIDE                                         FUNCTION
PUBLIC  JSON_DATAGUIDE                                         SYNONYM
PUBLIC  JSON_ELEMENT_T                                         SYNONYM
SYS     JSON_ELEMENT_T                                         TYPE
SYS     JSON_ELEMENT_T                                         TYPE BODY
SYS     JSON_HIERDATAGUIDE                                     FUNCTION
PUBLIC  JSON_HIERDATAGUIDE                                     SYNONYM
PUBLIC  JSON_KEY_LIST                                          SYNONYM
SYS     JSON_KEY_LIST                                          TYPE
SYS     JSON_LIB                                               LIBRARY
PUBLIC  JSON_OBJECT_T                                          SYNONYM
SYS     JSON_OBJECT_T                                          TYPE
SYS     JSON_OBJECT_T                                          TYPE BODY
PUBLIC  JSON_SCALAR_T                                          SYNONYM
SYS     JSON_SCALAR_T                                          TYPE
SYS     JSON_SCALAR_T                                          TYPE BODY
PUBLIC  USER_JSON_COLUMNS                                      SYNONYM
SYS     USER_JSON_COLUMNS                                      VIEW
PUBLIC  USER_JSON_DATAGUIDES                                   SYNONYM
SYS     USER_JSON_DATAGUIDES                                   VIEW
SYS     jdk/nashorn/internal/ir/debug/JSONWriter               JAVA CLASS
SYS     jdk/nashorn/internal/ir/debug/JSONWriter$1             JAVA CLASS
SYS     jdk/nashorn/internal/objects/NativeJSON                JAVA CLASS
SYS     jdk/nashorn/internal/objects/NativeJSON$1              JAVA CLASS
SYS     jdk/nashorn/internal/objects/NativeJSON$2              JAVA CLASS
SYS     jdk/nashorn/internal/objects/NativeJSON$3              JAVA CLASS
SYS     jdk/nashorn/internal/objects/NativeJSON$Constructor    JAVA CLASS
SYS     jdk/nashorn/internal/objects/NativeJSON$StringifyState JAVA CLASS
SYS     jdk/nashorn/internal/parser/JSONParser                 JAVA CLASS
SYS     jdk/nashorn/internal/runtime/JSONFunctions             JAVA CLASS
SYS     jdk/nashorn/internal/runtime/JSONFunctions$1           JAVA CLASS
SYS     jdk/nashorn/internal/runtime/JSONListAdapter           JAVA CLASS

desc int$dba_json_columns

desc dba_json_columns

set linesize 161

SELECT owner, table_name, column_name, format, data_type
FROM dba_json_columns;

ALTER TABLE uwclass.j_purchase_order DROP CONSTRAINT ensure_json;

-- once a json document is inserted this constraint will fail to create
ALTER TABLE uwclass.j_purchase_order
ADD CONSTRAINT ensure_json
CHECK (po_document IS NOT JSON STRICT);

ALTER TABLE uwclass.j_purchase_order
ADD CONSTRAINT ensure_json_strict
CHECK (po_document IS JSON STRICT);

SELECT defer, condition
FROM cdef$
WHERE obj# = 79128;

ALTER TABLE uwclass.j_purchase_order DROP CONSTRAINT ensure_json;

ALTER TABLE uwclass.j_purchase_order
ADD CONSTRAINT ensure_json
CHECK (po_document IS JSON);
JSON Search Index CREATE SEARCH INDEX [<schema_name.>]<index_name>
ON <table_name><json_column_name>
FOR JSON;
SQL> CREATE SEARCH INDEX uwclass.podoc
  2  ON uwclass.j_purchase_order(po_document)
  3  FOR JSON;

Index created.
 
JSON Document DML
Insert

Note the DBNonsense entry intentionally put in to prove to the NOSentience crowd that we can do schemaless too. Now let's see them do a finance system with Point-in-Time recovery.
conn uwclass/uwclass@pdbdev

INSERT INTO j_purchase_order VALUES (SYS_GUID(), SYSTIMESTAMP,
  '{"PONumber"   : 1600,
    "Reference"  : "SCATZ-20140421",
    "Requestor"  : "Safra Catz",
    "User"       : "dmorgan",
    "CostCenter" : "A01",
    "ShippingInstructions" : {"name" : "Lawrence Ellison",
                              "address": {"street" : "500 Marine Dr.",
                              "city" : "Redwood Shores",
                              "state" : "CA",
                              "zipCode" : 95608,
                              "country" : "United States of America"},
                              "phones" : [{"type" : "Office", "number" : "650-506-7000"},
                                         {"type" : "Mobile", "number" : "415-555-1234"}]},
    "Special Instructions" : null,
    "AllowPartialShipment" : true,
    "LineItems" : [{"ItemNumber" : 1,
                    "Part" : {"Description" : "Hawaiian Island: Lanai",
                              "UnitPrice" : 99.95,
                              "UPCCode" : 13131092899},
                              "Quantity" : 1.0},
                   {"ItemNumber" : 2,
                    "Part" : {"Description" : "Hotels on Boardwalk",
                              "UnitPrice" : 19.95,
                              "UPCCode" : 85391628927},
                              "Quantity" : 4.0}]}');

COMMIT;

set linesize 181
col date_loaded format a36

SELECT * FROM j_purchase_order;
 
JSON Conditions
IS JSON <expression> IS JSON [<STRICT | LAX>] [<WITH | WITHOUT>] [UNIQUE KEYS]l
SELECT date_loaded
FROM j_purchase_order
WHERE po_document IS JSON STRICT;
IS NOT JSON <expression> IS NOT JSON [<STRICT | LAX>] [<WITH | WITHOUT>] [UNIQUE KEYS]
SELECT date_loaded
FROM j_purchase_order
WHERE po_document IS NOT JSON STRICT;
 
JSON Functions
JSON_ARRAY

Takes as its input one or more SQL expressions, converts each expression to a JSON value, and returns a JSON array that contains those JSON values.
JSON_ARRAY(<expression> [FORMAT JSON]
<NULL | ABSENT> ON NULL
[RETURNING VARCHAR2 [(SIZE <BYTE | CHAR>)]
]
TBD
 
JSON_ARRAYAGG

Aan aggregate function, it takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values.
JSON_ARRAYAGG(
[order_by_clause]
[<NULL | ABSENT> ON NULL]
[RETURNING VARCHAR2 [(SIZE <BYTE | CHAR>)]]
TBD
 
JSON_DATAGUIDE

The Oracle SQL function JSON_DATAGUIDE takes as its input a table column of JSON data. Each row in the column is referred to as a JSON document. For each JSON document in the column, this function returns a CLOB value that contains a flat data guide for that JSON document.
JSON_DATAGUIDE(<column_name>)
SQL> SELECT json_dataguide(po_document)
  2  FROM j_purchase_order;

JSON_DATAGUIDE(PO_DOCUMENT)
--------------------------------------------------------------------------------
[{"o:path":"$.User","type":"string","o:length":8},{"o:path":"$.PONumber","type":
"number","o:length":4},{"o:path":"$.LineItems","type":"array","o:length":256},{"
o:path":"$.LineItems.Part","type":"object","o:length":128},{"o:path":"$.LineItem
s.Part.UPCCode","type":"number","o:length":16},{"o:path":"$.LineItems.Part.UnitP
rice","type":"number","o:length":8},{"o:path":"$.LineItems.Part.Description","ty
pe":"string","o:length":32},{"o:path":"$.LineItems.Quantity","type":"number","o:
length":4},{"o:path":"$.LineItems.ItemNumber","type":"number","o:length":1},{"o:
path":"$.Reference","type":"string","o:length":16},{"o:path":"$.Requestor","type
":"string","o:length":16},{"o:path":"$.CostCenter","type":"string","o:length":4}
,{"o:path":"$.AllowPartialShipment","type":"boolean","o:length":4},{"o:path":"$.
ShippingInstructions","type":"object","o:length":256},{"o:path":"$.ShippingInstr
uctions.name","type":"string","o:length":16},{"o:path":"$.ShippingInstructions.p
hones","type":"array","o:length":128},{"o:path":"$.ShippingInstructions.phones.t
ype","type":"string","o:length":8},{"o:path":"$.ShippingInstructions.phones.numb
er","type":"string","o:length":16},{"o:path":"$.ShippingInstructions.address","t
ype":"object","o:length":128},{"o:path":"$.ShippingInstructions.address.city","t
ype":"string","o:length":16},{"o:path":"$.ShippingInstructions.address.state","t
ype":"string","o:length":2},{"o:path":"$.ShippingInstructions.address.street","t
ype":"string","o:length":16},{"o:path":"$.ShippingInstructions.address.country",
"type":"string","o:length":32},{"o:path":"$.ShippingInstructions.address.zipCode
","type":"number","o:length":8},{"o:path":"$.\"Special Instructions\"","type":"n
ull","o:length":4}]
 
JSON_EXISTS

Use this clause to specify the JSON data to be evaluated. Specify an expression that evaluates to a text literal. If expression is a column, then the column must be of data type VARCHAR2, CLOB, or BLOB. If expression evaluates to null or a text literal of length zero, then the condition returns UNKNOWN.

If expression is not a text literal of well-formed JSON data using strict or lax syntax, then the condition returns FALSE.
JSON_EXISTS(<column_name> [FORMAT JSON], <json_return_path>
[<PASSING <expression> AS <alias]
[<ERROR | TRUE | FALSE> ON ERROR>];
SELECT doc_id, date_loaded
FROM j_purchase_order
WHERE json_exists(po_document, '$[*].PONumber' TRUE ON ERROR);
 
JSON_OBJECT

Takes as its input one or more property key-value pairs. It returns a JSON object that contains an object member for each of those key-value pairs.
json_object([<key>] <string> VALUE <expression> [FORMAT JSON]
<NULL | ABSENT> ON NULL
RETURNING VARCHAR2 [(SIZE <BYTE | CHAR>)]
TBD
 
JSON_OBJECTAGG

Takes as its input a property key-value pair. Typically, the property key, the property value, or both are columns of SQL expressions. This function constructs an object member for each key-value pair and returns a single JSON object that contains those object members.
json_objectagg([<key>] <string> VALUE <expression> [FORMAT JSON]
<NULL | ABSENT> ON NULL
RETURNING <VARCHAR2 [(SIZE <BYTE | CHAR>)] | CLOB>
TBD
 
JSON_QUERY

Returns as a character string one or more specified JSON values in JSON data

Array Step Usage
JSON_QUERY(<expression>, <array_step>
RETURNING VARCHAR2(<integer> [BYTE | CHAR]) [PRETTY] [ASCII];
SELECT json_query('{a:100, b:200, c:300}', '$') AS VALUE
FROM dual;

SELECT json_query('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) AS VALUE
FROM dual;
Object Step Usage JSON_QUERY(<expression>, <object_step>
RETURNING VARCHAR2(<integer> [BYTE | CHAR]) [PRETTY] [ASCII];
TBD
 
JSON_TABLE

Creates a relational view of JSON data by mapping the result of a JSON data evaluation into relational rows and columns
JSON_TABLE(expression>, $.<array_step | object_step> [<<ERROR | NULL | DEFAULT <literal>> ON ERROR>] <COLUMNS <comma_delimited_JSON_column_definition>>)
SELECT jt.phones
FROM j_purchaseorder,
json_table(po_document, '$.ShippingInstructions'
columns (phones VARCHAR2(100) format json path '$.Phone')) AS jt;

PHONES
-----------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"}, {"type":"Mobile","number":"415-555-1234"}]
 
JSON_TEXTCONTAINS JSON_TEXTCONTAINS(<column_name>, <JSON_path_expression>, '<string>')
SQL> SELECT po_document
  2  FROM j_purchase_order
  3  WHERE json_textcontains(po_document, '$', '19.95');
FROM j_purchase_order
*
ERROR at line 2:
ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without JavaScript Object Notation (JSON) index


-- build the required index

SQL> CREATE SEARCH INDEX uwclass.podoc
  2  ON uwclass.j_purchase_order(po_document)
  3  FOR JSON;

Index created.

SELECT po_document
FROM j_purchase_order
WHERE json_textcontains(po_document, '$', '19.95');
 
JSON_VALUE

Finds a specified scalar JSON value in JSON data and returns it as a SQL value
JSON_VALUE(<expression>, $<object_step | array_step>
[<JSON_value_returning_clause>] [<JSON_value_on_error_clause>]]
SELECT json_value('{a:100}', '$.a') AS RETVAL
FROM dual;

VALUE
-----
  100
Partition by Virtual Column using JSON_VALUE CREATE TABLE json_orders(
tx_id    NUMBER(5),
tx_date  DATE,
jsondata VARCHAR2(4000),
site_id  AS (JSON_VALUE(jsondata, '$.siteId' RETURNING NUMBER)))
PARTITION BY RANGE (site_id) (
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION pm VALUES LESS THAN (MAXVALUE));

desc json_orders

SELECT table_name, tablespace_name, partitioned
FROM user_tables
ORDER BY 3;

col high_value format a20

SELECT partition_name, tablespace_name, high_value
FROM user_tab_partitions
WHERE table_name = 'JSON_ORDERS';

desc user_tab_cols

SELECT column_name, virtual_column, data_default
FROM user_tab_cols
WHERE table_name = 'JSON_ORDERS';

INSERT INTO json_orders
(tx_id, tx_date, jsondata)
VALUES
(1, SYSDATE, '{"Seattle": 1, "siteId": 9}');

INSERT INTO json_orders
(tx_id, tx_date, jsondata)
VALUES
(2, SYSDATE, '{"New York": 2, "siteId": 11}');

COMMIT;

col jsondata format a30

SELECT * FROM json_orders;

SELECT * FROM json_orders PARTITION(p1);

SELECT * FROM json_orders PARTITION(p2);
 
Related Queries
Get JSON Functional Index stats DECLARE
  CURSOR expr_cur IS
  SELECT COLUMN_EXPRESSION
  FROM DBA_IND_EXPRESSIONS;

  c           CLOB;
  num_je_fidx NUMBER := 0;
  num_jq_fidx NUMBER := 0;
  num_jv_fidx NUMBER := 0;
BEGIN
  FOR expr_rec IN expr_cur LOOP
    c := TO_CLOB(expr_rec.COLUMN_EXPRESSION);
    IF (UPPER(c) LIKE '%JSON_VALUE%') THEN
      num_jv_fidx := num_jv_fidx + 1;
    ELSIF (UPPER(c) LIKE '%JSON_EXISTS%') THEN
      num_je_fidx := num_je_fidx + 1;
    ELSIF (UPPER(c) LIKE '%JSON_QUERY%') THEN
      num_jq_fidx := num_jq_fidx + 1;
    END IF;
  END LOOP;
  dbms_output.put_line(c);
END;
/
Get JSON text indexes stats DECLARE
 num_json_cidx NUMBER := 0;
BEGIN
  BEGIN
    SELECT idx_name
    INTO num_json_cidx
    FROM ctxsys.dr$index
    WHERE idx_id IN (
      SELECT ixv_idx_id
      FROM ctxsys.dr$index_value
      WHERE IXV_OAT_ID = 50817;
  EXCEPTION
    WHEN OTHERS THEN
      num_json_cidx := 0;
  END;

  BEGIN
    SELECT idx_name
    INTO num_json_cidx
    FROM ctxsys.dr$index
    WHERE idx_id IN (
      SELECT ixv_idx_id
      FROM ctxsys.dr$index_value
      WHERE IXV_OAT_ID = 50819);
  EXCEPTION
    WHEN OTHERS THEN
      num_json_cidx := 0;
  END;
END;
/

Related Topics
Built-in Functions
Built-in Packages
Conditions
DBMS_JSON
DBMS_JSON0
DBMS_JSON_INT
HTMLDB_UTIL
JSON Object Expressions
What's New In 18cR3
What's New In 19cR2