Oracle JSON Functionality
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Are you prepared for the release of Oracle Database 18c ... the first autonomous database? We are here at the Library. It is time for DBAs to stop fighting robots with their fingers and losing ... time to start using our intelligence and winning.
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 (new 12.2)

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 12cR1
What's New In 12cR2