8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON Data Guide Enhancements in Oracle Database 18c
This article covers the enhancements to the JSON Data Guide functionality in Oracle Database 18c.
- Setup
- JSON_DATAGUIDE Function Enhancements
- {USER|ALL|DBA|CDB}_JSON_DATAGUIDE_FIELDS Views
- Arrays of Scalar Values
Related articles.
- JSON Data Guide in Oracle Database 12c Release 2 (12.2)
- 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 Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 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');
JSON_DATAGUIDE Function Enhancements
In Oracle 12.2 the JSON_DATAGUIDE function returned a flat formatted and minimized data guide.
SET LONG 1000000 PAGESIZE 1000
SELECT JSON_DATAGUIDE(data) dg_doc
FROM json_documents;
DG_DOC
--------------------------------------------------------------------------------
[{"o:path":"$.Job","type":"string","o:length":8},{"o:path":"$.Active","type":"
boolean","o:length":8},{"o:path":"$.Address","type":
"object","o:length":128},{"o:path":"$.Address.City",
"type":"string","o:length":8},{"o:path":"$.Address.S
treet","type":"string","o:length":16},{"o:path":"$.A
ddress.Country","type":"string","o:length":2},{"o:pa
th":"$.Address.Postcode","type":"string","o:length":
8},{"o:path":"$.LastName","type":"string","o:length"
:4},{"o:path":"$.FirstName","type":"string","o:lengt
h":8},{"o:path":"$.DateOfBirth","type":"string","o:l
ength":16},{"o:path":"$.ContactDetails","type":"obje
ct","o:length":128},{"o:path":"$.ContactDetails.Emai
l","type":"string","o:length":32},{"o:path":"$.Conta
ctDetails.Phone","type":"string","o:length":16},{"o:
path":"$.ContactDetails.Twitter","type":"string","o:
length":8}]
SQL>
If you wanted the hierarchical format, or you wanted to pretty print the data guide, you had to use the DBMS_JSON.GET_INDEX_DATAGUIDE function, shown here.
In Oracle 18c the JSON_DATAGUIDE function can return the flat or hierarchical data guide, and display in pretty print using new optional parameters.
SET LONG 1000000 PAGESIZE 1000
-- Flat format and pretty.
SELECT JSON_DATAGUIDE(data, DBMS_JSON.format_flat, DBMS_JSON.pretty) dg_doc
FROM json_documents;
DG_DOC
--------------------------------------------------------------------------------
[
{
"o:path" : "$.Job",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.Active",
"type" : "boolean",
"o:length" : 8
},
{
"o:path" : "$.Address",
"type" : "object",
"o:length" : 128
},
{
"o:path" : "$.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:path" : "$.LastName",
"type" : "string",
"o:length" : 4
},
{
"o:path" : "$.FirstName",
"type" : "string",
"o:length" : 8
},
{
"o:path" : "$.DateOfBirth",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.ContactDetails",
"type" : "object",
"o:length" : 128
},
{
"o:path" : "$.ContactDetails.Email",
"type" : "string",
"o:length" : 32
},
{
"o:path" : "$.ContactDetails.Phone",
"type" : "string",
"o:length" : 16
},
{
"o:path" : "$.ContactDetails.Twitter",
"type" : "string",
"o:length" : 8
}
]
SQL>
-- Hierarchical format and pretty.
SELECT JSON_DATAGUIDE(data, DBMS_JSON.format_hierarchical, DBMS_JSON.pretty) dg_doc
FROM json_documents;
DG_DOC
--------------------------------------------------------------------------------
{
"type" : "object",
"properties" :
{
"Job" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "Job"
},
"Active" :
{
"type" : "boolean",
"o:length" : 8,
"o:preferred_column_name" : "Active"
},
"Address" :
{
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "Address",
"properties" :
{
"City" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "City"
},
"Street" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "Street"
},
"Country" :
{
"type" : "string",
"o:length" : 2,
"o:preferred_column_name" : "Country"
},
"Postcode" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "Postcode"
}
}
},
"LastName" :
{
"type" : "string",
"o:length" : 4,
"o:preferred_column_name" : "LastName"
},
"FirstName" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "FirstName"
},
"DateOfBirth" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "DateOfBirth"
},
"ContactDetails" :
{
"type" : "object",
"o:length" : 128,
"o:preferred_column_name" : "ContactDetails",
"properties" :
{
"Email" :
{
"type" : "string",
"o:length" : 32,
"o:preferred_column_name" : "Email"
},
"Phone" :
{
"type" : "string",
"o:length" : 16,
"o:preferred_column_name" : "Phone"
},
"Twitter" :
{
"type" : "string",
"o:length" : 8,
"o:preferred_column_name" : "Twitter"
}
}
}
}
}
SQL>
{USER|ALL|DBA|CDB}_JSON_DATAGUIDE_FIELDS Views
In Oracle 12.2 if you wanted to display the fields present in the data guide in a relational format you had to combine the data guide with JSON_TABLE to explode the output, as shown here. You still have to use this approach id you want to display the data guide statistics.
In Oracle 18c the {USER|ALL|DBA|CDB}_JSON_DATAGUIDE_FIELDS views have been added to simplify displaying this field information.
COLUMN path FORMAT A40
COLUMN type FORMAT A10
SELECT path,
type,
length
FROM user_json_dataguide_fields
WHERE table_name = 'JSON_DOCUMENTS'
AND column_name = 'DATA'
ORDER BY 1;
PATH TYPE LENGTH
---------------------------------------- ---------- ----------
$.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>
Arrays of Scalar Values
When a JSON document contains an array of scalar values the data guide now records both the array itself and the scalar type of the array.
Truncate the test table and populate it with some JSON data containing scalar arrays.
TRUNCATE TABLE json_documents;
INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
'{
"element1" : [1,2,3,4,5],
"element2" : ["one","two","three"]
}');
COMMIT;
The data guide is automatically updated, so we can query the field information to see the impact of this. Notice the array path is listed with the type array, and the scalar type in the array is listed using the array path with "[*]" appended to it.
COLUMN path FORMAT A40
COLUMN type FORMAT A10
SELECT path,
type,
length
FROM user_json_dataguide_fields
WHERE table_name = 'JSON_DOCUMENTS'
AND column_name = 'DATA'
ORDER BY 1;
PATH TYPE LENGTH
---------------------------------------- ---------- ----------
$.element1 array 16
$.element1[*] number 1
$.element2 array 32
$.element2[*] string 8
SQL>
For more information see:
- JSON_DATAGUIDE
- ALL_JSON_DATAGUIDE_FIELDS
- JSON Data Guide in Oracle Database 12c Release 2 (12.2)
- JSON Data Guide
- GeoJSON Data Support in Oracle Database 19c - JSON Data Guide
- Indexing JSON Data in Oracle Database 12c Release 1 (12.1.0.2)
- JSON Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Hope this helps. Regards Tim...