Oracle JSON Functionality
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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.
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
{$ORACLE_HOME}/rdbms/admin/cat
 
JSON Table and Column
Create table and constraint CREATE TABLE j_purchase_order (
doc_id RAW (16) NOT NULL,
date_loaded TIMESTAMP(6) WITH TIME ZONE,
po_document CLOB);

SELECT DISTINCT defer, condition
FROM cdef$;

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

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

OWNER   OBJECT_NAME           OBJECT_TYPE
------- --------------------- ------------
PUBLIC  ALL_JSON_COLUMNS      SYNONYM
SYS     ALL_JSON_COLUMNS      VIEW
PUBLIC  CDB_JSON_COLUMNS      SYNONYM
SYS     CDB_JSON_COLUMNS      VIEW
PUBLIC  DBA_JSON_COLUMNS      SYNONYM
SYS     DBA_JSON_COLUMNS      VIEW
SYS     DBMS_FEATURE_JSON     PROCEDURE
SYS     INT$DBA_JSON_COLUMNS  VIEW
PUBLIC  USER_JSON_COLUMNS     SYNONYM
SYS     USER_JSON_COLUMNS     VIEW

desc int$dba_json_columns

desc dba_json_columns

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

ALTER TABLE j_purchase_order DROP CONSTRAINT ensure_json;

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

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

SELECT DISTINCT defer, condition
FROM cdef$;
 
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 LDAP and Point-in-Time recovery
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}]}');

col date_loaded format a36

SELECT * FROM j_purchase_order;
 
JSON Conditions
IS JSON <expression> IS JSON [<STRICT | LAX>] [<WITH | WITHOUT>] [UNIQUE KEYS]
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_EXISTS JSON_EXISTS(<column_name> <json_return_path> []);
SELECT doc_id, date_loaded
FROM j_purchase_order
WHERE json_exists (po_document, '$[*].PONumber' TRUE ON ERROR);
JSON_TEXTCONTAINS JSON_TEXTCONTAINS(<column_name>, <JSON_path_expression>, '<string>')
SELECT po_document
FROM j_purchase_order
WHERE json_textcontains(po_document, '$', '1.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

SELECT po_document
FROM j_purchase_order
WHERE json_textcontains(po_document, '$', '1.95');
 
JSON Functions
JSON_ARRAY (new 12.1)
Undocumented JSON_ARRAY(
TBD
 
JSON_ARRAYAGG (new 12.1)
Undocumented JSON_ARRAYAGG(
TBD
 
JSON_EQUAL (new 12.1)
Undocumented JSON_EQUAL(
TBD
 
JSON_OBJECT (new 12.1)
Undocumented  
TBD
 
JSON_OBJECTAGG (new 12.1)
Undocumented  
TBD
 
JSON_QUERY (new 12.1)
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_SERIALIZE (new 12.1)
Undocumented JSON_SERIALIZE(
TBD
 
JSON_TABLE (new 12.1)
? JSON_TABLE(
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_VALUE (new 12.1)
? 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
 
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_bson_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_bson_cidx := 0;
  END;
END;

Related Topics
Conditions
HTMLDB_UTIL
JSON Object Expressions