Oracle | DBMS

Oracle DBMS_JSON Version 21c

General Information

Library Note

Morgan's Library Page Header

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 Provides an interface for dataguide operations for those working with Java Script Object Notation inside the Oracle database. Be sure to also learn about the new function JSON_DATAGUIDE that takes as its input a table column of JSON data and, for each JSON document in the column, returns a flat data guide as a CLOB.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Field Types
TYPE_ARRAY	NUMBER(2)	6
TYPE_BOOLEAN	NUMBER(2)	2
TYPE_GEOJSON	NUMBER(2)	7
TYPE_OBJECT	NUMBER(2)	5
TYPE_NULL	NUMBER(2)	1
TYPE_NUMBER	NUMBER(2)	3
TYPE_STRING	NUMBER(2)	4
Extended Types
TYPE_FLOAT	NUMBER(2)	11
TYPE_DOUBLE	NUMBER(2)	12
TYPE_DATE	NUMBER(2)	13
TYPE_TIMESTAMP	NUMBER(2)	14
TYPE_YMINTERVAL	NUMBER(2)	15
TYPE_DSINTERVAL	NUMBER(2)	16
TYPE_BINARY	NUMBER(2)	17
Materialized View Options
MV_REFRESH_ON_STATEMENT	NUMBER(2)	1
MV_REFRESH_ON_COMMIT	NUMBER(2)	2
MV_REFRESH_ON_DEMAND	NUMBER(2)	3

Dependencies

ALL_JSON_DATAGUIDES	DBMS_OUTPUT	JSON_OBJECT_T
ALL_JSON_DATAGUIDE_FIELDS	DBMS_SODA_ADMIN	PLITBLM
CDB_JSON_DATAGUIDES	DG$GETDGQUOTENAME	SODA_COLLECTION_T
DBA_JSON_DATAGUIDES	INT$DBA_JSON_DATAGUIDES	SYS_DGAGG
DBA_JSON_DATAGUIDE_FIELDS	INT$DBA_JSON_DG_COLS	USER_JSON_COLUMNS
DBMS_ASSERT	JSON_ARRAY_T	USER_JSON_DATAGUIDES
DBMS_JSON0	JSON_DATAGUIDE	USER_JSON_DATAGUIDE_FIELDS
DBMS_JSON_INT	JSON_ELEMENT_T	USER_TAB_COLS
DBMS_LOB	JSON_KEY_LIST

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-40582	Cannot find a data guide-enabled context index

First Available 12.2

Security Model Owned by XDB with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsjson.sql

Subprograms

ADD_VIRTUAL_COLUMNS	FORMAT_HIERARCHICAL	PREPALLJCOLINM
BLOB_TO_JSON	GATHER_STATS	PREPJCOLINM
CREATE_VIEW	GEOJSON	PREPTABJCOLINM
CREATE_VIEW_ON_PATH	GET_INDEX_DATAGUIDE	PRETTY
DROP_VIRTUAL_COLUMNS	GET_VIEW_SQL	RENAME_COLUMN
FORMAT_FLAT

ADD_VIRTUAL_COLUMNS (3 new 21c parameters to Overload 1)

Add virtual columns based on dataguide information. On a shard catalog server no virtual column is added.

Overload 1 dbms_json.add_virtual_columns( tablename IN VARCHAR2, jcolname IN VARCHAR2, dataguide IN CLOB, resolveNameConflicts IN BOOLEAN DEFAULT FALSE, colNamePrefix IN VARCHAR2 DEFAULT NULL, mixedCaseColumns IN BOOLEAN DEFAULT TRUE); PRAGMA SUPPLEMENTAL_LOG_DATA(add_virtual_columns, AUTO_WITH_COMMIT);

exec  dbms_json.add_virtual_columns('J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.get_index_dataguide('J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.format_hierarchical));

Overload 2 dbms_json.add_virtual_columns( tablename IN VARCHAR2, jcolname IN VARCHAR2, frequency IN NUMBER DEFAULT 0, hidden IN BOOLEAN DEFAULT FALSE); PRAGMA SUPPLEMENTAL_LOG_DATA(add_virtual_columns, AUTO_WITH_COMMIT);

exec dbms_json.add_virtual_columns('J_PURCHASEORDER', 'PO_DOCUMENT', 50);

BLOB_TO_JSON (new 21c)

Receives a table, and column and, if the column is of data type BLOB with an IS JSON FORMAT OSON constraint, converts the column to JSON type

Check the corresponding BLOB2JSON procedure in DBMS_JSON_INT and it is clear that this procedure's parameters are wrong and this will not work until Oracle patches it correcting the missing owner parameter. dbms_json.blob_to_json( tablename IN VARCHAR2, columnname IN VARCHAR2, throwError IN BOOLEAN DEFAULT TRUE); PRAGMA SUPPLEMENTAL_LOG_DATA(blob_to_json, AUTO_WITH_COMMIT);

CREATE TABLE B2JTEST (

      pkcol   INTEGER,

      testcol BLOB

      CONSTRAINT ensure CHECK (testcol IS JSON));

      

      desc b2jtest

      Name        Null?    Type

      ----------- -------- -----------------------

      PKCOL                NUMBER(38)

      TESTCOL              BLOB;

      

      exec xdb.dbms_json.blob_to_json('B2JTEST', 'ENSURE', FALSE);

      

      PL/SQL procedure successfully completed.

      

      exec xdb.dbms_json.blob_to_json('B2JTEST', 'TESTCOL', TRUE);

      BEGIN xdb.dbms_json.blob_to_json('B2JTEST', 'TESTCOL', TRUE); END;

      *

      ERROR at line 1:

      ORA-00942: table or view does not exist

      ORA-06512: at "XDB.DBMS_JSON_INT", line 238

      ORA-06512: at "XDB.DBMS_JSON", line 1986

      ORA-06512: at line 1

CREATE_VIEW (7 new 21c parameters)

Create a view with relational columns and scalar JSON fields as specified in a dataguide dbms_json.create_view( viewname IN VARCHAR2, tablename IN VARCHAR2, jcolname IN VARCHAR2, dataguide IN CLOB resourcePath IN VARCHAR2 DEFAULT NULL, materialize IN BOOLEAN DEFAULT FALSE, mvRefreshMode IN NUMBER DEFAULT MV_REFRESH_ON_STATEMENT, path IN VARCHAR2 DEFAULT '$', resolveNameConflicts IN BOOLEAN DEFAULT FALSE, colNamePrefix IN VARCHAR2 DEFAULT NULL, mixedCaseColumns IN BOOLEAN DEFAULT TRUE); PRAGMA SUPPLEMENTAL_LOG_DATA(create_view, AUTO_WITH_COMMIT);

exec dbms_json.create_view('VIEW1', 'J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.get_index_dataguide('J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.format_hierarchical));

      

      call dbms_json.createViewOnPath('MOVIE_TICKETS_VIEW', 'MOVIE_TICKETS', 'BOOKING_DETAILS', '$');

CREATE_VIEW_ON_PATH (3 new 21c parameters)

Create a view based on dataguide information, with relational columns, top level scalar types, and fully expanded subtree under a given path. When running on the shard catalog server this raises an error stating that the dataguide is empty. dbms_json.create_view_on_path( viewname IN VARCHAR2, tablename IN VARCHAR2, jcolname IN VARCHAR2, path IN VARCHAR2, frequency IN NUMBER DEFAULT 0 resourcePath IN VARCHAR2 DEFAULT NULL, materialize IN BOOLEAN DEFAULT FALSE, mvRefreshMode IN NUMBER DEFAULT MV_REFRESH_ON_STATEMENT); PRAGMA SUPPLEMENTAL_LOG_DATA(create_view_on_path, AUTO_WITH_COMMIT);

exec dbms_json.create_view_on_path('VIEW2', 'J_PURCHASEORDER', 'PO_DOCUMENT', '$');

DROP_VIRTUAL_COLUMNS

Drop virtual columns created by procedure add_virtual_columns. This has no effect when running on the shard catalog server. dbms_json.drop_virtual_columns( tablename IN VARCHAR2, jcolname IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(drop_virtual_columns, AUTO_WITH_COMMIT);

exec dbms_json.drop_virtual_columns(''J_PURCHASEORDER', 'PO_DOCUMENT');

FORMAT_FLAT

JSON Data guide formatting dbns_json.format_flat RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.format_flat

      FROM dual;

      

      FORMAT_FLAT

      -----------

                2

FORMAT_HIERARCHICAL

JSON Data guide formatting dbns_json.format_hierarchical RETURN NUMBER PARALLEL ENABLE;

SQL> SELECT dbms_json.format_hierarchical

        2  FROM dual;

      

      FORMAT_HIERARCHICAL

      -------------------

                        1

GATHER_STATS (new 21c)

Undocumented but used for Data Guide formatting dbms_json.gather_stats RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.gather_stats

      FROM dual;

      

      GATHER_STATS

      ------------

                 4

GEOJSON

JSON Data Guide formatting dbms_json.geoJSON RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.geoJSON

      FROM dual;

GET_INDEX_DATAGUIDE (new 21c overload)

Get JSON dataguide from a dataguide enabled JSON search index. When running on the shard catalog server returns NULL.

Overload 1 dbms_json.get_index_dataguide( owner IN VARCHAR2, tablename IN VARCHAR2, jcolname IN VARCHAR2, format IN NUMBER, pretty IN NUMBER DEFAULT 0) RETURN IN CLOB;

SELECT dbms_json.get_index_dataguide('UWJSON', 'PO', dbms_json.format_hierarchical, dbms_json.pretty)

      FROM DUAL;

Overload 2 dbms_json.get_index_dataguide( tablename IN VARCHAR2, jcolName IN VARCHAR2, format IN NUMBER, pretty IN NUMBER DEFAULT 0) RETURN CLOB;

SELECT dbms_json.get_index_dataguide('UWJSON', 'PO', dbms_json.format_hierarchical, dbms_json.pretty)

      FROM DUAL;

GET_VIEW_SQL (new 21c)

Similar to create_view, but returns the creating view DDL but does not execute the DDL and create the view dbms_json.get_view_sql( viewName IN VARCHAR2, tableName IN VARCHAR2, jcolName IN VARCHAR2, dataguide IN CLOB, materialize IN BOOLEAN DEFAULT FALSE, mvRefreshMode IN NUMBER DEFAULT MV_REFRESH_ON_STATEMENT, path IN VARCHAR2 DEFAULT '$', resolveNameConflicts IN BOOLEAN DEFAULT FALSE, colNamePrefix IN VARCHAR2 DEFAULT NULL, mixedCaseColumns IN BOOLEAN DEFAULT TRUE) RETURN CLOB; PRAGMA SUPPLEMENTAL_LOG_DATA(get_view_sql, READ_ONLY);

exec dbms_json.create_view_on_path('VIEW3, 'J_PURCHASEORDER', 'PO_DOCUMENT', '$');

PREPALLJCOLINM

For tables containing json columns created prior to 12.2 upgrades all JSON columns to to take advantage of in-memory JSON processing in 12.2 and above dbms_json.prepAllJColInM; PRAGMA SUPPLEMENTAL_LOG_DATA(prepAllJColInM, AUTO);

exec dbms_json.prepAllJColInM;

PREPJCOLINM

For JSON columns created prior to 12.2 upgrades the column to prepare to take advantage of in-memory JSON processing dbms_json.prepJColInM( tabName IN VARCHAR2, jcolName IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(prepJColInM, AUTO);

TBD

PREPTABJCOLINM

For tables containing JSON columns created prior to 12.2 upgrades all JSON columns to to take advantage of in-memory JSON processing dbms_json.prepTabJColInM(tabName IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(prepTabJColInM, AUTO);

exec dbms_json.prepTabJColInM('PO');

PRETTY

JSON Data guide formatting dbns_json.pretty RETURN NUMBER PARALLEL ENABLE;

SELECT dbms_json.pretty

      FROM dual;

      

      FORMAT_FLAT

      -----------

                1

RENAME_COLUMN

Set the preferred name for a view column or a virtual column creating using a dataguide. This has no effect when running on the shard catalog server. dbms_json.rename_column( tablename IN VARCHAR2, jcolname IN VARCHAR2, path IN VARCHAR2, type IN NUMBER, preferred_name IN VARCHAR2); PRAGMA SUPPLEMENTAL_LOG_DATA(rename_column, AUTO);

exec dbms_json.rename_column('UWJSON', 'PO', '$.purchaseOrder.items.name', dbms_json.type_string, 'item_name');