JSON_DATAGUIDE
Purpose
The aggregate function JSON_DATAGUIDE
computes the data guide of a set of JSON data. The data guide is returned as a CLOB
which can be in either flat or hierarchical format depending on the passing format parameter.
expr
expr
is a SQL expression that evaluates to a JSON object or a JSON array. It can also be a JSON column in a table.
format options
Use the format options to specify the format of the data guide that will be returned. It must be one of the following values:
-
dbms_json.format_flat
for a flat format. -
dbms_json.format_hierarchical
for a hierarchical format.
If the parameter is the absent, the default is dbms_json.format_flat
.
flag options
flag
can have the following values:
-
Specify
DBMS_JSON.PRETTY
to improve readability of the returned data guide with appropriate indentation. -
Specify
DBMS_JSON.GEOJSON
for the data guide to auto detect theGeoJSON
type. The corresponding view column created by the data guide will be ofsdo_geometry
type. -
Specify
DBMS_JSON.GATHER_STATS
for the data guide to collect statistical information. The data guide report generated withDBMS_JSON.GATHER_STATS
has a new fieldo:sample_size
, in addition to all of the other statistical fields that you get withDBMS_JSON.get_index_dataguide
. -
Specify
DBMS_JSON.DETECT_DATETIME
for the data guide to detect temporal types. The data guide reports a JSON field value that conforms to the ISO 8601 format as a timestamp type, not a string type. -
All values
DBMS_JSON.PRETTY
,DBMS_JSON.GEOJSON
, andDBMS_JSON.GATHER_STATS
, andDBMS_JSON.DETECT_DATETIME
can be combined with a plus sign. For example,DBMS_JSON.GEOJSON+DBMS_JSON.PRETTY
, orDBMS_JSON.GEOJSON+DBMS_JSON.PRETTY+DBMS_JSON.GATHER_STATS
.
See Also:
Examples
The following example uses the j_purchaseorder
table, which is created in "Creating a Table That Contains a JSON Document: Example". This table contains a column of JSON data called po_document
. This example returns a flat data guide for each year group.
SELECT EXTRACT(YEAR FROM date_loaded) YEAR, JSON_DATAGUIDE(po_document) "DATA GUIDE" FROM j_purchaseorder GROUP BY extract(YEAR FROM date_loaded) ORDER BY extract(YEAR FROM date_loaded) DESC; YEAR DATA GUIDE ---- ------------------------------------------ 2016 [ { "o:path" : "$.PO_ID", "type" : "number", "o:length" : 4 }, { "o:path" : "$.PO_Ref", "type" : "string", "o:length" : 16 }, { "o:path" : "$.PO_Items", "type" : "array", "o:length" : 64 }, { "o:path" : "$.PO_Items.Part_No", "type" : "number", "o:length" : 16 }, { "o:path" : "$.PO_Items.Item_Quantity", "type" : "number", "o:length" : 2 } ] . . .