Oracle DBMS_JSON
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose The new 12.2 DBMS_JSON built-in package 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 12.2 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
DBA_JSON_DATAGUIDES DG$GETDGQUOTENAME PLITBLM
DBMS_ASSERT INT$DBA_JSON_DATAGUIDES USER_JSON_COLUMNS
DBMS_JSON0 JSON_ARRAY_T 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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
JSON Data guide formatting FUNCTION FORMAT_FLAT return NUMBER;
SQL> SELECT dbms_json.format_flat
  2  FROM dual;

FORMAT_FLAT
-----------
          2
 
FORMAT_HIERARCHICAL (new 12.2)
JSON Data guide formatting FUNCTION FORMAT_HIERARCHICAL RETURN NUMBER;
SQL> SELECT dbms_json.format_hierarchical
  2  FROM dual;

FORMAT_HIERARCHICAL
-------------------
                  1
 
GET_INDEX_DATAGUIDE (new 12.2)
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 (new 12.2)
For all the 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 dbms_json.prepAllJColInM;
exec dbms_json.prepAllJColInM;
 
PREPJCOLINM (new 12.2)
For a json column created prior to 12.2 rupgrades the column to prepare to take advantage of in memory json processing in 12.2 dbms_json.prepJColInM(
tabName  IN VARCHAR2,
jcolName IN VARCHAR2);
TBD
 
PREPTABJCOLINM (new 12.2)
For a table containing json columns created prior to 12.2 upgrades all json columns in the table to to take advantage of in memory json processing in 12.2 dbms_json.prepTabJColInM(tabName IN VARCHAR2);
exec dbms_json.prepTabJColInM('PO');
 
PRETTY (new 12.2)
JSON Data guide formatting FUNCTION PRETTY RETURN NUMBER;
SQL> SELECT dbms_json.format_flat
  2  FROM dual;

FORMAT_FLAT
-----------
          2
 
RENAME_COLUMN (new 12.2)
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 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved