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.

Setup
Using JSON_TABLE
Using SQL NESTED Clause

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:

SQL NESTED Clause Instead of JSON_TABLE
JSON Support in Oracle Database 12c Release 1 (12.1.0.2) - JSON_TABLE
JSON_TABLE Enhancements in Oracle Database 18c
JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, 26ai, All Articles

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)