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

Home » Articles » 18c » Here

JSON_TABLE Enhancements in Oracle Database 18c

This article covers the enhancements to the JSON_TABLE function in Oracle Database 18c. The JSON_TABLE function was first introduced in Oracle 12.1, as described here.

Related articles.

Setup

Create and populate the following table to provide some JSON data to work with.

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

Simplified Syntax - Dot Notation

In previous versions we might access the data in the table using the following type of JSON_TABLE syntax, where each projected column is defined in the COLUMNS clause using a JSON path expression. Notice some of the projected column names don't match the underlying path names.

COLUMN first_name FORMAT A10
COLUMN last_name FORMAT A10
COLUMN job FORMAT A10
COLUMN active FORMAT A6

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR) PATH '$.FirstName',
                  last_name     VARCHAR2(50 CHAR) PATH '$.LastName',
                  job           VARCHAR2(10 CHAR) PATH '$.Job',
                  active        VARCHAR2(5 CHAR)  PATH '$.Active')) jt;

FIRST_NAME LAST_NAME  JOB        ACTIVE
---------- ---------- ---------- ------
John       Doe        Clerk      true
Jayne      Doe        Manager    false

SQL>

In Oracle 18c we can use a simplified version of the JSON_TABLE syntax by replacing the path expressions with dot notation.

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR) PATH FirstName,
                  last_name     VARCHAR2(50 CHAR) PATH LastName,
                  job           VARCHAR2(10 CHAR) PATH Job,
                  active        VARCHAR2(5 CHAR)  PATH Active)) jt;

FIRST_NAME LAST_NAME  JOB        ACTIVE
---------- ---------- ---------- ------
John       Doe        Clerk      true
Jayne      Doe        Manager    false

SQL>

With the original syntax we can flatten nested objects using the dot notation in the search path expression. In the following example we pull out the "ContactDetails.Email" and "ContactDetails.Phone" values.

COLUMN email FORMAT A23
COLUMN phone FORMAT A13

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH '$.FirstName',
                  last_name     VARCHAR2(50 CHAR)  PATH '$.LastName',
                  job           VARCHAR2(10 CHAR)  PATH '$.Job',
                  email         VARCHAR2(100 CHAR) PATH '$.ContactDetails.Email',
                  phone         VARCHAR2(50 CHAR)  PATH '$.ContactDetails.Phone',
                  active        VARCHAR2(5 CHAR)   PATH '$.Active')) jt;

FIRST_NAME LAST_NAME  JOB        EMAIL                   PHONE         ACTIVE
---------- ---------- ---------- ----------------------- ------------- ------
John       Doe        Clerk      john.doe@example.com    44 123 123456 true
Jayne      Doe        Manager    jayne.doe@example.com                 false

SQL>

We can do the same thing with the simplified dot notation approach.

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (first_name    VARCHAR2(50 CHAR)  PATH FirstName,
                  last_name     VARCHAR2(50 CHAR)  PATH LastName,
                  job           VARCHAR2(10 CHAR)  PATH Job,
                  email         VARCHAR2(100 CHAR) PATH ContactDetails.Email,
                  phone         VARCHAR2(50 CHAR)  PATH ContactDetails.Phone,
                  active        VARCHAR2(5 CHAR)   PATH Active)) jt;

FIRST_NAME LAST_NAME  JOB        EMAIL                   PHONE         ACTIVE
---------- ---------- ---------- ----------------------- ------------- ------
John       Doe        Clerk      john.doe@example.com    44 123 123456 true
Jayne      Doe        Manager    jayne.doe@example.com                 false

SQL>

Simplified Syntax - Implied Path Expression

If we don't want the column names to differ from the search path names we can just list the elements we want and the path expressions will be implied. The element names must match the contents of the document, including case, and the projected columns will be in upper case.

COLUMN firstname FORMAT A10
COLUMN lastname FORMAT A10

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (FirstName, LastName, Job, Active)) jt;

FIRSTNAME  LASTNAME   JOB        ACTIVE
---------- ---------- ---------- ------
John       Doe        Clerk      true
Jayne      Doe        Manager    false

SQL>

If we try to flatten out nested objects this with the simplified syntax it fails, as it doesn't like the "." character.

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (FirstName, LastName, Job, ContactDetails.Email, ContactDetails.Phone, Active)) jt;
                                                          *
ERROR at line 4:
ORA-40484: invalid data type for JSON_TABLE column


SQL>

Instead we have to use the NESTED clause, like we would if were handling a nested array, but since this is a single nested JSON object we don't need to use "ContactDetails[*]" like we would if it was a nested array.

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (FirstName, LastName, Job, Active,
           NESTED ContactDetails COLUMNS (Email, Phone)
         )
       ) jt;

FIRSTNAME  LASTNAME   JOB        ACTIVE EMAIL                   PHONE
---------- ---------- ---------- ------ ----------------------- -------------
John       Doe        Clerk      true   john.doe@example.com    44 123 123456
Jayne      Doe        Manager    false  jayne.doe@example.com

SQL>

If we want to specify the column data types they can simply be added to the column list where required.

SELECT jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (FirstName VARCHAR2(50), LastName VARCHAR2(50), Job, Active,
           NESTED ContactDetails COLUMNS (Email VARCHAR2(100), Phone)
         )
       ) jt;

FIRSTNAME  LASTNAME   JOB        ACTIVE EMAIL                   PHONE
---------- ---------- ---------- ------ ----------------------- -------------
John       Doe        Clerk      true   john.doe@example.com    44 123 123456
Jayne      Doe        Manager    false  jayne.doe@example.com

SQL>

JSON_TABLE Materialized Views : ON STATEMENT Support

In previous database versions we were able to create materialized views containing the JSON_TABLE function using the ON DEMAND and ON COMMIT clauses, but attempting to use the ON STATEMENT clause resulted in the following error.

ORA-32428: on-statement materialized join view error: Shape of MV is not supported

This restriction has been lifted in Oracle 18c, as shown below.

Create a materialized view using the ON STATEMENT clause.

-- DROP MATERIALIZED VIEW json_documents_v;

CREATE MATERIALIZED VIEW json_documents_v
REFRESH FAST
ON STATEMENT
AS
SELECT id, jt.*
FROM   json_documents,
       JSON_TABLE(data, '$'
         COLUMNS (FirstName, LastName, Job, Active,
           NESTED ContactDetails COLUMNS (Email, Phone)
         )
       ) jt;

Query the data from the materialized view to check it is working as expected.

SET LINESIZE 120

COLUMN firstname FORMAT A10
COLUMN lastname FORMAT A10
COLUMN email FORMAT A23
COLUMN phone FORMAT A13

SELECT * FROM json_documents_v;

ID                               FIRSTNAME  LASTNAME   JOB        ACTIVE EMAIL                   PHONE
-------------------------------- ---------- ---------- ---------- ------ ----------------------- -------------
696878605B4F4508E05336BB1C0A6517 John       Doe        Clerk      true   john.doe@example.com    44 123 123456
696878605B504508E05336BB1C0A6517 Jayne      Doe        Manager    false  jayne.doe@example.com

SQL>

Add a new row but don't commit it. You will see the new row is present in the materialized view.

INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
        '{
          "FirstName"      : "Mary",
          "LastName"       : "Doe",
          "Job"            : "President",
          "Address"        : {
                              "Street"   : "101 My Street",
                              "City"     : "My City",
                              "Country"  : "UK",
                              "Postcode" : "A12 34B"
                             },
          "ContactDetails" : {
                              "Email"    : "mary.doe@example.com",
                              "Phone"    : "44 123 234567",
                              "Twitter"  : "@marydoe"
                             },
          "DateOfBirth"    : "01-JAN-1980",
          "Active"         : true
         }');

SELECT * FROM json_documents_v;

ID                               FIRSTNAME  LASTNAME   JOB        ACTIVE EMAIL                   PHONE
-------------------------------- ---------- ---------- ---------- ------ ----------------------- -------------
696878605B4F4508E05336BB1C0A6517 John       Doe        Clerk      true   john.doe@example.com    44 123 123456
696878605B504508E05336BB1C0A6517 Jayne      Doe        Manager    false  jayne.doe@example.com
696878605B514508E05336BB1C0A6517 Mary       Doe        President  true   mary.doe@example.com    44 123 234567

SQL>

Rollback the insert and check the materialized view again. The row is no longer present.

ROLLBACK;

SELECT * FROM json_documents_v;

ID                               FIRSTNAME  LASTNAME   JOB        ACTIVE EMAIL                   PHONE
-------------------------------- ---------- ---------- ---------- ------ ----------------------- -------------
696878605B4F4508E05336BB1C0A6517 John       Doe        Clerk      true   john.doe@example.com    44 123 123456
696878605B504508E05336BB1C0A6517 Jayne      Doe        Manager    false  jayne.doe@example.com

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.