📋Steps in this guide1/6

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

oracle 12cconfigurationintermediate

by OracleDba

15 views

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 check constraint on the column holding the JSON data.

Code/Command (click line numbers to comment):

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"    : "[email protected]",
                              "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"    : "[email protected]",
                              "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. If you want the persistent data guide information without the search index, you can create the JSON search index with the following parameters. You can remove and add the data guide functionality from an existing JSON search index using the command. 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 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 command. 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.

Code/Command (click line numbers to comment):

CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON;

DROP INDEX json_docs_search_idx;
CREATE SEARCH INDEX json_docs_search_idx ON json_documents (data) FOR JSON
  PARAMETERS ('SEARCH_ON NONE');

ALTER INDEX json_docs_search_idx REBUILD PARAMETERS ('DATAGUIDE OFF');
ALTER INDEX json_docs_search_idx REBUILD PARAMETERS ('DATAGUIDE ON');

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

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 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. The information can also be queries using the function. The documentation suggests using this information along with to display the information in a flat fashion. We can do the same with the function. 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. 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. We can format this as rows using as before.

Code/Command (click line numbers to comment):

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>

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>

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>

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>

EXEC DBMS_STATS.gather_index_stats(USER, 'json_docs_search_idx', estimate_percent => 99);

"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"
        }

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 [email protected]      [email protected]              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 procedure. If we describe the table we can see it now has some extra columns. The virtual columns can be removed using the procedure. If we describe the table we can see the virtual columns are now gone. The procedure can be used to alter the name of the virtual columns created. With the virtual columns in place we can query the JSON data as if it were a regular table. Remove the virtual columns. 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 column is no longer present. Remove the virtual columns.

Code/Command (click line numbers to comment):

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;
/

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>

BEGIN
  DBMS_JSON.drop_virtual_columns(
    tablename  => 'json_documents',
    jcolname   => 'data');
END;
/

SQL> DESC json_documents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL RAW(16)
 DATA                                               CLOB

SQL>

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

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>

BEGIN
  DBMS_JSON.drop_virtual_columns(
    tablename  => 'json_documents',
    jcolname   => 'data');
END;
/

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>

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 procedure. The column names are defined using the procedure, like we used during the creation of the virtual columns. Alternatively the 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.

Code/Command (click line numbers to comment):

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

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 views. - 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: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!