8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON Data Guide in Oracle Database 12c Release 2 (12.2)
Simplify the interaction with JSON data stored in the database using the JSON Data Guide functionality introduced in Oracle Database 12c Release 2 (12.2).
- Setup
- Create a JSON Search Index
- Display JSON Data Guide (GET_INDEX_DATAGUIDE, JSON_DATAGUIDE)
- Add and Remove Virtual Columns (ADD_VIRTUAL_COLUMNS, DROP_VIRTUAL_COLUMNS, RENAME_COLUMN)
- Create View (CREATE_VIEW, CREATE_VIEW_ON_PATH)
- Miscellaneous
Related articles.
- JSON Data Guide Enhancements in Oracle Database 18c
- GeoJSON Data Support in Oracle Database 19c - JSON Data Guide
- Indexing JSON Data in Oracle Database 12c Release 1 (12.1.0.2)
- JSON Data Guide
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
Create and populate the following table to provide some JSON data to work with. For the data guide functionality to work the table must include the IS JSON check constraint on the column holding the JSON data.
DROP TABLE json_documents PURGE;
CREATE TABLE json_documents (
id RAW(16) NOT NULL,
data CLOB,
CONSTRAINT json_documents_pk PRIMARY KEY (id),
CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);
INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
'{
"FirstName" : "John",
"LastName" : "Doe",
"Job" : "Clerk",
"Address" : {
"Street" : "99 My Street",
"City" : "My City",
"Country" : "UK",
"Postcode" : "A12 34B"
},
"ContactDetails" : {
"Email" : "john.doe@example.com",
"Phone" : "44 123 123456",
"Twitter" : "@johndoe"
},
"DateOfBirth" : "01-JAN-1980",
"Active" : true
}');
INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
'{
"FirstName" : "Jayne",
"LastName" : "Doe",
"Job" : "Manager",
"Address" : {
"Street" : "100 My Street",
"City" : "My City",
"Country" : "UK",
"Postcode" : "A12 34B"
},
"ContactDetails" : {
"Email" : "jayne.doe@example.com",
"Phone" : ""
},
"DateOfBirth" : "01-JAN-1982",
"Active" : false
}');
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'json_documents');
Create a JSON Search Index
The data guide information is included in the JSON search index by default.
CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON;
If you want the persistent data guide information without the search index, you can create the JSON search index with the following parameters.
DROP INDEX json_docs_search_idx;
CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON
PARAMETERS ('SEARCH_ON NONE');
You can remove and add the data guide functionality from an existing JSON search index using the ALTER INDEX command.
ALTER INDEX json_docs_search_idx REBUILD PARAMETERS ('DATAGUIDE OFF');
ALTER INDEX json_docs_search_idx REBUILD PARAMETERS ('DATAGUIDE ON');
You can choose how to handle virtual columns for new elements as they appear in the data guide. By default no new virtual columns are created for new elements appearing in the data guide. By adding the ON CHANGE ADD_VC parameter you can make sure virtual columns for all new elements are immediately added to the table. This parameter can set at create time, or using the ALTER INDEX command.
DROP INDEX json_docs_search_idx;
CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON PARAMETERS ('DATAGUIDE ON CHANGE ADD_VC');
DROP INDEX json_docs_search_idx;
CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON;
ALTER INDEX json_docs_search_idx REBUILD PARAMETERS ('DATAGUIDE ON CHANGE ADD_VC');
I would not advise using this for anything other than test. It's always better to add virtual columns in a planned manner.
Replace the index with a normal JSON search index.
DROP INDEX json_docs_search_idx; CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON;
Display JSON Data Guide (GET_INDEX_DATAGUIDE, JSON_DATAGUIDE)
There are some new features in Oracle 18c you might want to consider when displaying data guide contents.
The DBMS_JSON.GET_INDEX_DATAGUIDE function returns the data guide description for the data in the JSON column. The output can be altered using the input parameters, including displaying the output in a flat or hierarchical format as well as pretty print.
SET LONG 1000000 PAGESIZE 1000
SELECT DBMS_JSON.get_index_dataguide(
'json_documents',
'data',
DBMS_JSON.format_flat,
DBMS_JSON.pretty) AS dg
FROM dual;
DG
--------------------------------------------------------------------------------
[
{
"o:path" : "$.Job",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DG_JOB"
},
{
"o:path" : "$.Active",
"type" : "boolean",
"o:length" : 8,
"o:preferred_column_name" : "DG_ACTIVE"
},
{
"o:path" : "$.Address",
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "DATA$Address"
},
{
"o:path" : "$.Address.City",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$City"
},
{
"o:path" : "$.Address.Street",
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$Street"
},
{
"o:path" : "$.Address.Country",
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "DATA$Country"
},
{
"o:path" : "$.Address.Postcode",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DG_POSTCODE"
},
{
"o:path" : "$.LastName",
"type" : "string",
"o:length" : 4,
"o:preferred_column_name" : "DATA$LastName"
},
{
"o:path" : "$.FirstName",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$FirstName"
},
{
"o:path" : "$.DateOfBirth",
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$DateOfBirth"
},
{
"o:path" : "$.ContactDetails",
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "DATA$ContactDetails"
},
{
"o:path" : "$.ContactDetails.Email",
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "DATA$Email"
},
{
"o:path" : "$.ContactDetails.Phone",
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$Phone"
},
{
"o:path" : "$.ContactDetails.Twitter",
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$Twitter"
}
]
SQL>
SET LONG 1000000 PAGESIZE 1000
SELECT DBMS_JSON.get_index_dataguide(
'json_documents',
'data',
DBMS_JSON.format_hierarchical,
DBMS_JSON.pretty) AS dg
FROM dual;
DG
--------------------------------------------------------------------------------
{
"type" : "object",
"properties" :
{
"Job" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DG_JOB"
},
"Active" :
{
"type" : "boolean",
"o:length" : 8,
"o:preferred_column_name" : "DG_ACTIVE"
},
"Address" :
{
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "DATA$Address",
"properties" :
{
"City" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$City"
},
"Street" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$Street"
},
"Country" :
{
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "DATA$Country"
},
"Postcode" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DG_POSTCODE"
}
}
},
"LastName" :
{
"type" : "string",
"o:length" : 4,
"o:preferred_column_name" : "DATA$LastName"
},
"FirstName" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$FirstName"
},
"DateOfBirth" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$DateOfBirth"
},
"ContactDetails" :
{
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "DATA$ContactDetails",
"properties" :
{
"Email" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "DATA$Email"
},
"Phone" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$Phone"
},
"Twitter" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$Twitter"
}
}
}
}
}
SQL>
The information can also be queries using the JSON_DATAGUIDE function.
SELECT JSON_DATAGUIDE(data) dg_doc
FROM json_documents;
DG_DOC
--------------------------------------------------------------------------------
[{"o:path":"$.Job","type":"string","o:length":8},{"o:path":"$.Active","type":"bo
olean","o:length":8},{"o:path":"$.Address","type":"object","o:length":128},{"o:p
ath":"$.Address.City","type":"string","o:length":8},{"o:path":"$.Address.Street"
,"type":"string","o:length":16},{"o:path":"$.Address.Country","type":"string","o
:length":2},{"o:path":"$.Address.Postcode","type":"string","o:length":8},{"o:pat
h":"$.LastName","type":"string","o:length":4},{"o:path":"$.FirstName","type":"st
ring","o:length":8},{"o:path":"$.DateOfBirth","type":"string","o:length":16},{"o
:path":"$.ContactDetails","type":"object","o:length":128},{"o:path":"$.ContactDe
tails.Email","type":"string","o:length":32},{"o:path":"$.ContactDetails.Phone","
type":"string","o:length":16},{"o:path":"$.ContactDetails.Twitter","type":"strin
g","o:length":8}]
SQL>
The documentation suggests using this information along with JSON_TABLE to display the information in a flat fashion.
WITH dg_t AS (
SELECT JSON_DATAGUIDE(data) dg_doc
FROM json_documents
)
SELECT jt.*
FROM dg_t,
json_table(dg_doc, '$[*]'
COLUMNS
jpath VARCHAR2(40) PATH '$."o:path"',
type VARCHAR2(10) PATH '$."type"',
tlength NUMBER PATH '$."o:length"') jt
ORDER BY jt.jpath;
JPATH TYPE TLENGTH
---------------------------------------- ---------- ----------
$.Active boolean 8
$.Address object 128
$.Address.City string 8
$.Address.Country string 2
$.Address.Postcode string 8
$.Address.Street string 16
$.ContactDetails object 128
$.ContactDetails.Email string 32
$.ContactDetails.Phone string 16
$.ContactDetails.Twitter string 8
$.DateOfBirth string 16
$.FirstName string 8
$.Job string 8
$.LastName string 4
SQL>
We can do the same with the GET_INDEX_DATAGUIDE function.
WITH dg_t AS (
SELECT DBMS_JSON.get_index_dataguide(
'json_documents',
'data',
DBMS_JSON.format_flat) AS dg_doc
FROM dual
)
SELECT jt.*
FROM dg_t,
json_table(dg_doc, '$[*]'
COLUMNS
jpath VARCHAR2(40) PATH '$."o:path"',
type VARCHAR2(10) PATH '$."type"',
tlength NUMBER PATH '$."o:length"') jt
ORDER BY jt.jpath;
JPATH TYPE TLENGTH
---------------------------------------- ---------- ----------
$.Active boolean 8
$.Address object 128
$.Address.City string 8
$.Address.Country string 2
$.Address.Postcode string 8
$.Address.Street string 16
$.ContactDetails object 128
$.ContactDetails.Email string 32
$.ContactDetails.Phone string 16
$.ContactDetails.Twitter string 8
$.DateOfBirth string 16
$.FirstName string 8
$.Job string 8
$.LastName string 4
SQL>
By default the data guide doesn't include statistics for the elements, but these can be added by gathering statistics on the JSON search index as follows.
EXEC DBMS_STATS.gather_index_stats(USER, 'json_docs_search_idx', estimate_percent => 99);
You will now see statistical information in the data guide, as shown below in two elements taken from the data guide. Notice the "Twitter" element is only present in 50% of the documents.
"Phone" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DATA$Phone",
"o:frequency" : 100,
"o:low_value" : "",
"o:high_value" : "44 123 123456",
"o:num_nulls" : 0,
"o:last_analyzed" : "2018-01-01T13:12:30"
},
"Twitter" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "DATA$Twitter",
"o:frequency" : 50,
"o:low_value" : "@johndoe",
"o:high_value" : "@johndoe",
"o:num_nulls" : 0,
"o:last_analyzed" : "2018-01-01T13:12:30"
}
We can format this as rows using JSON_TABLE as before.
SET LINESIZE 160
WITH dg_t AS (
SELECT DBMS_JSON.get_index_dataguide(
'json_documents',
'data',
DBMS_JSON.format_flat) AS dg_doc
FROM dual
)
SELECT jt.*
FROM dg_t,
json_table(dg_doc, '$[*]'
COLUMNS
jpath VARCHAR2(30) PATH '$."o:path"',
type VARCHAR2(10) PATH '$."type"',
tlength NUMBER PATH '$."o:length"',
frequency NUMBER PATH '$."o:frequency"',
low_value VARCHAR2(25) PATH '$."o:low_value"',
high_value VARCHAR2(25) PATH '$."o:high_value"',
num_nulls NUMBER PATH '$."o:num_nulls"',
last_analyzed VARCHAR2(20) PATH '$."o:last_analyzed"') jt
ORDER BY jt.jpath;
JPATH TYPE TLENGTH FREQUENCY LOW_VALUE HIGH_VALUE NUM_NULLS LAST_ANALYZED
------------------------------ ---------- ---------- ---------- ------------------------- ------------------------- ---------- --------------------
$.Active boolean 8 100 true false 0 2018-01-01T13:17:50
$.Address object 128 100 2018-01-01T13:17:50
$.Address.City string 8 100 My City My City 0 2018-01-01T13:17:50
$.Address.Country string 2 100 UK UK 0 2018-01-01T13:17:50
$.Address.Postcode string 8 100 A12 34B A12 34B 0 2018-01-01T13:17:50
$.Address.Street string 16 100 99 My Street 100 My Street 0 2018-01-01T13:17:50
$.ContactDetails object 128 100 2018-01-01T13:17:50
$.ContactDetails.Email string 32 100 john.doe@example.com jayne.doe@example.com 0 2018-01-01T13:17:50
$.ContactDetails.Phone string 16 100 44 123 123456 0 2018-01-01T13:17:50
$.ContactDetails.Twitter string 8 50 @johndoe @johndoe 0 2018-01-01T13:17:50
$.DateOfBirth string 16 100 01-JAN-1980 01-JAN-1982 0 2018-01-01T13:17:50
$.FirstName string 8 100 John Jayne 0 2018-01-01T13:17:50
$.Job string 8 100 Clerk Manager 0 2018-01-01T13:17:50
$.LastName string 4 100 Doe Doe 0 2018-01-01T13:17:50
14 rows selected.
SQL>
Add and Remove Virtual Columns (ADD_VIRTUAL_COLUMNS, DROP_VIRTUAL_COLUMNS, RENAME_COLUMN)
The JSON data guide information can be used to add virtual columns to the table holding the JSON data using the ADD_VIRTUAL_COLUMNS procedure.
BEGIN
DBMS_JSON.add_virtual_columns(
tablename => 'json_documents',
jcolname => 'data',
dataguide => DBMS_JSON.get_index_dataguide(
'json_documents',
'data',
DBMS_JSON.format_hierarchical));
END;
/
If we describe the table we can see it now has some extra columns.
SQL> DESC json_documents Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL RAW(16) DATA CLOB DATA$Job VARCHAR2(8) DATA$Active VARCHAR2(8) DATA$City VARCHAR2(8) DATA$Street VARCHAR2(16) DATA$Country VARCHAR2(2) DATA$Postcode VARCHAR2(8) DATA$LastName VARCHAR2(4) DATA$FirstName VARCHAR2(8) DATA$DateOfBirth VARCHAR2(16) DATA$Email VARCHAR2(32) DATA$Phone VARCHAR2(16) DATA$Twitter VARCHAR2(8) SQL>
The virtual columns can be removed using the DROP_VIRTUAL_COLUMNS procedure.
BEGIN
DBMS_JSON.drop_virtual_columns(
tablename => 'json_documents',
jcolname => 'data');
END;
/
If we describe the table we can see the virtual columns are now gone.
SQL> DESC json_documents Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL RAW(16) DATA CLOB SQL>
The RENAME_COLUMN procedure can be used to alter the name of the virtual columns created.
-- Define the required column names.
BEGIN
DBMS_JSON.rename_column('json_documents', 'data', '$.Job', DBMS_JSON.TYPE_STRING, 'DG_JOB');
DBMS_JSON.rename_column('json_documents', 'data', '$.Active', DBMS_JSON.TYPE_BOOLEAN, 'DG_ACTIVE');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.City', DBMS_JSON.TYPE_STRING, 'DG_CITY');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.Street', DBMS_JSON.TYPE_STRING, 'DG_STREET');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.Country', DBMS_JSON.TYPE_STRING, 'DG_COUNTRY');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.Postcode', DBMS_JSON.TYPE_STRING, 'DG_POSTCODE');
DBMS_JSON.rename_column('json_documents', 'data', '$.LastName', DBMS_JSON.TYPE_STRING, 'DG_LAST_NAME');
DBMS_JSON.rename_column('json_documents', 'data', '$.FirstName', DBMS_JSON.TYPE_STRING, 'DG_FIRST_NAME');
DBMS_JSON.rename_column('json_documents', 'data', '$.DateOfBirth', DBMS_JSON.TYPE_STRING, 'DG_DOB');
DBMS_JSON.rename_column('json_documents', 'data', '$.ContactDetails.Email', DBMS_JSON.TYPE_STRING, 'DG_EMAIL');
DBMS_JSON.rename_column('json_documents', 'data', '$.ContactDetails.Phone', DBMS_JSON.TYPE_STRING, 'DG_PHONE');
DBMS_JSON.rename_column('json_documents', 'data', '$.ContactDetails.Twitter', DBMS_JSON.TYPE_STRING, 'DG_TWITTER');
END;
/
-- Add the virtual columns.
BEGIN
DBMS_JSON.add_virtual_columns(
tablename => 'json_documents',
jcolname => 'data',
dataguide => DBMS_JSON.get_index_dataguide(
'json_documents',
'data',
DBMS_JSON.format_hierarchical));
END;
/
DESC json_documents
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL RAW(16)
DATA CLOB
DG_JOB VARCHAR2(8)
DG_ACTIVE VARCHAR2(8)
DG_CITY VARCHAR2(8)
DG_STREET VARCHAR2(16)
DG_COUNTRY VARCHAR2(2)
DG_POSTCODE VARCHAR2(8)
DG_LAST_NAME VARCHAR2(4)
DG_FIRST_NAME VARCHAR2(8)
DG_DOB VARCHAR2(16)
DG_EMAIL VARCHAR2(32)
DG_PHONE VARCHAR2(16)
DG_TWITTER VARCHAR2(8)
SQL>
With the virtual columns in place we can query the JSON data as if it were a regular table.
SELECT id,
dg_first_name,
dg_last_name,
dg_job
FROM json_documents;
ID DG_FIRST DG_L DG_JOB
-------------------------------- -------- ---- --------
619198B94C4C4C75E0538838A8C0CFC3 John Doe Clerk
619198B94C4D4C75E0538838A8C0CFC3 Jayne Doe Manager
SQL>
Remove the virtual columns.
BEGIN
DBMS_JSON.drop_virtual_columns(
tablename => 'json_documents',
jcolname => 'data');
END;
/
We can also decide which columns should be projected based on the frequency of the elements in the documents stored in the JSON column. We need to gather statistics for the JSON search index for this to work. In the example below we project columns for elements that appear in 100% of the documents, so the DG_TWITTER column is no longer present.
EXEC DBMS_STATS.gather_index_stats(USER, 'json_docs_search_idx', NULL, 99);
BEGIN
DBMS_JSON.add_virtual_columns(
tablename => 'json_documents',
jcolname => 'data',
frequency => 100);
END;
/
DESC json_documents
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL RAW(16)
DATA CLOB
DG_JOB VARCHAR2(8)
DG_ACTIVE VARCHAR2(8)
DG_CITY VARCHAR2(8)
DG_STREET VARCHAR2(16)
DG_COUNTRY VARCHAR2(2)
DG_POSTCODE VARCHAR2(8)
DG_LAST_NAME VARCHAR2(4)
DG_FIRST_NAME VARCHAR2(8)
DG_DOB VARCHAR2(16)
DG_EMAIL VARCHAR2(32)
DG_PHONE VARCHAR2(16)
SQL>
Remove the virtual columns.
BEGIN
DBMS_JSON.drop_virtual_columns(
tablename => 'json_documents',
jcolname => 'data');
END;
/
Create View (CREATE_VIEW, CREATE_VIEW_ON_PATH)
Rather than adding virtual columns to the table we can create a view based on the JSON data guide information using the CREATE_VIEW procedure. The column names are defined using the RENAME_COLUMN procedure, like we used during the creation of the virtual columns.
-- Define the required column names.
BEGIN
DBMS_JSON.rename_column('json_documents', 'data', '$.Job', DBMS_JSON.TYPE_STRING, 'JOB');
DBMS_JSON.rename_column('json_documents', 'data', '$.Active', DBMS_JSON.TYPE_BOOLEAN, 'ACTIVE');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.City', DBMS_JSON.TYPE_STRING, 'CITY');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.Street', DBMS_JSON.TYPE_STRING, 'STREET');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.Country', DBMS_JSON.TYPE_STRING, 'COUNTRY');
DBMS_JSON.rename_column('json_documents', 'data', '$.Address.Postcode', DBMS_JSON.TYPE_STRING, 'POSTCODE');
DBMS_JSON.rename_column('json_documents', 'data', '$.LastName', DBMS_JSON.TYPE_STRING, 'LAST_NAME');
DBMS_JSON.rename_column('json_documents', 'data', '$.FirstName', DBMS_JSON.TYPE_STRING, 'FIRST_NAME');
DBMS_JSON.rename_column('json_documents', 'data', '$.DateOfBirth', DBMS_JSON.TYPE_STRING, 'DOB');
DBMS_JSON.rename_column('json_documents', 'data', '$.ContactDetails.Email', DBMS_JSON.TYPE_STRING, 'EMAIL');
DBMS_JSON.rename_column('json_documents', 'data', '$.ContactDetails.Phone', DBMS_JSON.TYPE_STRING, 'PHONE');
DBMS_JSON.rename_column('json_documents', 'data', '$.ContactDetails.Twitter', DBMS_JSON.TYPE_STRING, 'TWITTER');
END;
/
DROP VIEW json_documents_v1;
-- Create the view.
BEGIN
DBMS_JSON.create_view(
viewname => 'json_documents_v1',
tablename => 'json_documents',
jcolname => 'data',
dataguide => DBMS_JSON.get_index_dataguide(
'json_documents',
'data',
DBMS_JSON.format_hierarchical));
END;
/
DESC json_documents_v1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL RAW(16)
JOB VARCHAR2(8)
ACTIVE VARCHAR2(8)
CITY VARCHAR2(8)
STREET VARCHAR2(16)
COUNTRY VARCHAR2(2)
POSTCODE VARCHAR2(8)
LAST_NAME VARCHAR2(4)
FIRST_NAME VARCHAR2(8)
DOB VARCHAR2(16)
EMAIL VARCHAR2(32)
PHONE VARCHAR2(16)
TWITTER VARCHAR2(8)
SQL>
Alternatively the CREATE_VIEW_ON_PATH procedure can be used, which allows us to create the view based on the frequency of the elements. In the following example we only include columns in the view if the corresponding element is in all documents stored in the JSON column.
DROP VIEW json_documents_v2;
-- Create the view.
BEGIN
DBMS_JSON.create_view_on_path(
viewname => 'json_documents_v2',
tablename => 'json_documents',
jcolname => 'data',
path => '$',
frequency => 100);
END;
/
DESC json_documents_v2
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL RAW(16)
JOB VARCHAR2(8)
ACTIVE VARCHAR2(8)
CITY VARCHAR2(8)
STREET VARCHAR2(16)
COUNTRY VARCHAR2(2)
POSTCODE VARCHAR2(8)
LAST_NAME VARCHAR2(4)
FIRST_NAME VARCHAR2(8)
DOB VARCHAR2(16)
EMAIL VARCHAR2(32)
PHONE VARCHAR2(16)
SQL>
Miscellaneous
Some miscellaneous points to remember.
- This functionality only really makes sense if there is some consistency in the types of JSON documents held in a column. If each document were completely different, this isn't the functionality for you.
- Existing data guides can be displayed using the
{USER|ALL|DBA|CDB}_JSON_DATAGUIDESviews. - Data guides are maintained dynamically. If you add a new row containing additional elements, these will be reflected in the data guide. If the data guide includes statistics, these statistics will not be updated until the statistics are gathered again.
- When you create virtual columns or views using the data guide, these are static based on the data guide definition at the time of creation. If the document structure changes, for example adding new elements, these will not be reflected in the virtual columns or views based on the data guide unless they are recreated.
- You can index and gather statistics on virtual columns created using the data guide, which provides the optimizer with more information.
- Remember, statistics are only present in the data guide if you gather statistics on the JSON search index.
For more information see:
- JSON Data Guide
- DBMS_JSON
- JSON Data Guide Enhancements in Oracle Database 18c
- GeoJSON Data Support in Oracle Database 19c - JSON Data Guide
- Indexing JSON Data in Oracle Database 12c Release 1 (12.1.0.2)
- JSON Data Guide
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...