ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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 and 12.2 and enhanced further in 18c and 19c.
In Database 19c Oracle has added an undocumented function, BSON, that converts JSON to Binary JSON. You can find it in the Related Topics links at the bottom of this page.
CREATE TABLE uwclass.j_purchase_order (
doc_id RAW(16) NOT NULL,
date_loaded TIMESTAMP(6) WITH TIME ZONE,
po_document CLOB);
SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS'
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);
col object_name format a60
SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_name like '%JSON%'
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 YNONYM
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);
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.
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.
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>)
SELECT json_dataguide(po_document)
FROM j_purchase_order;
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>
SELECT jt.phones
FROM j_purchaseorder,
json_table(po_document, '$.ShippingInstructions'
columns (phones VARCHAR2(100) format json path '$.Phone')) AS jt;
SELECT po_document
FROM j_purchase_order
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
CREATE SEARCH INDEX uwclass.podoc
ON uwclass.j_purchase_order(po_document)
FOR JSON;
Index created.
SELECT po_document
FROM j_purchase_order
WHERE json_textcontains(po_document, '$', '19.95');
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 jsontable (recid, jdata) VALUES (json(sVal));
INSERT INTO jsontable (recid, jdata) VALUES (json(bVal));
INSERT INTO jsontable (recid, jdata) VALUES (json(cVal));
COMMIT;
END;
/
SELECT COUNT(*) FROM jsontable;
SELECT * FROM jsontable;
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;
/
