8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

JSON Data Guide Enhancements in Oracle Database 18c

This article covers the enhancements to the JSON Data Guide functionality in Oracle Database 18c.

Related 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:

Hope this helps. Regards Tim...

Back to the Top.