JASON_TABLE
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(<schema_name>, 'json_documents');
COLUMN first_name FORMAT A10COLUMN last_name FORMAT A10COLUMN job FORMAT A10COLUMN 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 trueJayne 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 trueJayne 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 A23COLUMN 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 trueJayne 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 trueJayne Doe Manager jayne.doe@example.com false 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 supportedThis 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_mvREFRESH FASTON STATEMENT
ASSELECT 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 A10COLUMN lastname FORMAT A10COLUMN email FORMAT A23COLUMN phone FORMAT A13 SELECT * FROM json_documents_mv; ID FIRSTNAME LASTNAME JOB ACTIVE EMAIL PHONE---------- ---------- ---------- ---------- ------ ----------------------- -------------6968786012 John Doe Clerk true john.doe@example.com 44 123 12345669682105B5 Jayne Doe Manager false jayne.doe@example.com SQL>
Comments
Post a Comment