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

Home » Articles » 12c » Here

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

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

Create a JSON Search Index

The data guide information is included in the JSON search index by default.

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

If you want the persistent data guide information without the search index, you can create the JSON search index with the following parameters.

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

You can remove and add the data guide functionality from an existing JSON search index using the ALTER INDEX command.

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

Replace the index with a normal JSON search index.

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)

The DBMS_JSON.GET_INDEX_DATAGUIDE 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.

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>

The information can also be queries using the JSON_DATAGUIDE function.

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>

The documentation suggests using this information along with JSON_TABLE to display the information in a flat fashion.

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>

We can do the same with the GET_INDEX_DATAGUIDE function.

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>

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.

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

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.

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

We can format this as rows using JSON_TABLE as before.

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 john.doe@example.com      jayne.doe@example.com              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 ADD_VIRTUAL_COLUMNS procedure.

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

If we describe the table we can see it now has some extra columns.

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>

The virtual columns can be removed using the DROP_VIRTUAL_COLUMNS procedure.

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

If we describe the table we can see the virtual columns are now gone.

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

SQL>

The RENAME_COLUMN procedure can be used to alter the name of the virtual columns created.

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

With the virtual columns in place we can query the JSON data as if it were a regular table.

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>

Remove the virtual columns.

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

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 DG_TWITTER column is no longer present.

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>

Remove the virtual columns.

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

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

Alternatively the CREATE_VIEW_ON_PATH 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.

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.