8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- Setup
- Simplified Syntax - Dot Notation
- Simplified Syntax - Implied Path Expression
- JSON_TABLE Materialized Views : ON STATEMENT Support
Related articles.
- JSON Support in Oracle Database 12c Release 1 (12.1.0.2) : JSON_TABLE
- JSON Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All 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_mv;
CREATE MATERIALIZED VIEW json_documents_mv
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_mv; 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_mv;
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_mv; 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:
- JSON_TABLE
- CREATE MATERIALIZED VIEW
- JSON Support in Oracle Database 12c Release 1 (12.1.0.2) : JSON_TABLE
- JSON Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
Hope this helps. Regards Tim...