Oracle DBMS_JSON
Version 21c

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 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
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 (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 to overload 1)
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 to overload 1)
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');

Related Topics
Built-in Functions
Built-in Packages
DBMS_JSON0
DBMS_JSON_INT
JSON Functionality
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx