Thursday, 24 May 2018

JSON Data Guide Enhancements in Oracle Database 18c

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

1. 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');

2. 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>

3. {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>

4. 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>