Oracle DBMS_JSON
Version 19.3

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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
Schema Types
TYPE_ARRAY NUMBER(2) 6
TYPE_BOOLEAN NUMBER(2) 2
TYPE_OBJECT NUMBER(2) 5
TYPE_NULL NUMBER(2) 1
TYPE_NUMBER NUMBER(2) 3
TYPE_STRING NUMBER(2) 4
Dependencies
ALL_JSON_DATAGUIDES DBMS_XDB JSON_OBJECT_T
CDB_JSON_DATAGUIDES DG$GETDGQUOTENAME PLITBLM
DBA_JSON_DATAGUIDES INT$DBA_JSON_DATAGUIDES SYS_DGAGG
DBMS_ASSERT JSON_ARRAY_T USER_JSON_COLUMNS
DBMS_JSON0 JSON_DATAGUIDE USER_JSON_DATAGUIDES
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.0.1
Security Model Owned by XDB with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsjson.sql
Subprograms
 
ADD_VIRTUAL_COLUMNS
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);
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);
exec dbms_json.add_virtual_columns('J_PURCHASEORDER', 'PO_DOCUMENT', 50);
 
CREATE_VIEW
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);
SQL> exec dbms_json.create_view('VIEW1', 'J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.get_index_dataguide('J_PURCHASEORDER', 'PO_DOCUMENT', dbms_json.format_hierarchical));

SQL> call dbms_json.createViewOnPath('MOVIE_TICKETS_VIEW', 'MOVIE_TICKETS', 'BOOKING_DETAILS', '$');
 
CREATE_VIEW_ON_PATH
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);
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);
exec dbms_json.drop_virtual_columns(''J_PURCHASEORDER', 'PO_DOCUMENT');
 
FORMAT_FLAT
JSON Data guide formatting dbns_json.format_flat RETURN NUMBER PARALLEL ENABLE;
SQL> SELECT dbms_json.format_flat
  2  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
 
GEOJSON (new 19c)
JSON Data Guide formatting dbms_json.geoJSON RETURN NUMBER PARALLEL ENABLE;
SELECT dbms_json.geoJSON
FROM dual;
 
GET_INDEX_DATAGUIDE
Get JSON dataguide from a dataguide enabled JSON search index. When running on the shard catalog server returns NULL. dbms_json.get_index_dataguide(
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;
 
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;
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);
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);
exec dbms_json.prepTabJColInM('PO');
 
PRETTY
JSON Data guide formatting dbns_json.pretty RETURN NUMBER PARALLEL ENABLE;
SQL> SELECT dbms_json.pretty
  2  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);
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 18cR3
What's New In 19cR3

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