8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- 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, 23c, All 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:
- 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, 23c, All Articles
Hope this helps. Regards Tim...