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

Home » Articles » 19c » Here

SQL NESTED Clause Instead of JSON_TABLE in Oracle Database 19c

The SQL NESTED clause allows us to write the equivalent of the LEFT OUTER JOIN JSON_TABLE using a simplified syntax.

Related articles.

Setup

Create and populate the following table to provide some JSON data to work with. Notice the third row has no JSON data included.

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

INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(), NULL);

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'json_documents');

Using JSON_TABLE

A typical example of using JSON_TABLE to access the data might look like this.

SET LINESIZE 120
COLUMN first_name FORMAT A10
COLUMN last_name FORMAT A10
COLUMN job FORMAT A10
COLUMN email FORMAT A21
COLUMN phone FORMAT A14
COLUMN active FORMAT A6

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j,
       JSON_TABLE(j.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;

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

SQL>

Notice the row without any JSON data isn't displayed. This is because we are effectively doing an inner join. The previous statement is functionally equivalent to the following.

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j JOIN
       JSON_TABLE(j.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 ON 1=1;

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

SQL>

If we want to show data from columns outside the JSON data column, even when the JSON data column is null, we need to do a left outer join to JSON_TABLE, as shown below.

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j LEFT OUTER JOIN
       JSON_TABLE(j.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 ON 1=1;

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

SQL>

Using SQL NESTED Clause

The SQL NESTED clause allows us to write the equivalent of the LEFT OUTER JOIN JSON_TABLE using a simplified syntax.

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j NESTED 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;

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

SQL>

This doesn't stop us using the NESTED clause to access nested columns or lists of columns.

SELECT j.id, jt.first_name, jt.last_name, jt.job, jt.email, jt.phone, jt.active
FROM   json_documents j NESTED 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,
                  NESTED PATH ContactDetails COLUMNS (
                    email       VARCHAR2(100 CHAR) PATH Email,
                    phone       VARCHAR2(50 CHAR)  PATH Phone
                  )) jt;

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

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.