8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON Support in Oracle Database 12c Release 1 (12.1.0.2)
This article describes the server side support for JSON in Oracle Database 12c Release 1 (12.1.0.2). There is a lot of variety in the syntax of the JSON functionality, which for simplicity is avoided here. After using this article to familiarise yourself with the concepts, you really need to spend some time working through the documentation to be able to make full use of the JSON support.
- Introduction to JSON
- Creating Tables to Hold JSON
- Querying JSON Data
- Identifying Columns Containing JSON
- Loading JSON Files Using External Tables
- Dot Notation Query Transformation
- Error Handling
Related articles.
- JSON Support in Oracle Database 12c
- Indexing JSON Data in Oracle Database 12c
- Indexing JSON Data in Oracle Database 12c Release 1 (12.1.0.2)
- SQL/JSON Functions in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Introduction to JSON
If you are new to JSON, you should probably start by working through these links.
- w3schools.com : JSON Tutorial
- Overview of JSON
- Overview of JSON Syntax and the Data It Represents
- Overview of JSON Compared with XML
Creating Tables to Hold JSON
No new data type has been added to support JSON. Instead, it is stored in regular VARCHAR2
or CLOB
columns. It can also be stored in NVARCHAR
, NCLOB
and BLOB
columns, but it's less likely you will want to do this.
Oracle 21c introduced a new JSON data type. You should use this in preference to other data types. You can read more about it here.
The addition of the IS JSON
constraint indicates the column contains valid JSON data. In the following example, a table is created to hold JSON documents.
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) );
The IS JSON
check constraint enforces lax JSON syntax by default. If you want to make it enforce strict JSON syntax, you need to add the (STRICT)
qualifier.
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 CHECK (data IS JSON (STRICT)) );
The rest of the article will assume the lax JSON syntax enforcement is used.
With the table in place, it is possible to insert data into the JSON column like any other VARCHAR2
or CLOB
column.
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;
The presence of the IS JSON
constraint means that invalid JSON will cause an error during insert or update.
UPDATE json_documents a SET a.data = '{"FirstName" : "Invalid Document"' WHERE a.data.FirstName = 'Jayne'; * ERROR at line 1: ORA-02290: check constraint (TEST.DOCUMENT_JSON) violated SQL>
Querying JSON Data
Dot Notation
Provided the associated column has an IS JSON
check constraint, the individual elements of a JSON document can be referenced directly from SQL using dot notation.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN Postcode FORMAT A10 COLUMN Email FORMAT A25 SELECT a.data.FirstName, a.data.LastName, a.data.Address.Postcode AS Postcode, a.data.ContactDetails.Email AS Email FROM json_documents a ORDER BY a.data.FirstName, a.data.LastName; FIRSTNAME LASTNAME POSTCODE EMAIL --------------- --------------- ---------- ------------------------- Jayne Doe A12 34B jayne.doe@example.com John Doe A12 34B john.doe@example.com 2 rows selected. SQL>
If you use the dot notation to drill down into an element, the column name of the returned value remains set to the top-level element. In the example above, without the aliases, "Postcode" would return as "Address" and "Email" would return as "ContactDetails".
SELECT a.data.Address.Postcode FROM json_documents a; ADDRESS ---------- A12 34B A12 34B 2 rows selected. SQL>
If a non-scalar value is a referenced, the result is returned as a JSON fragment.
SELECT a.data.ContactDetails FROM json_documents a; CONTACTDETAILS ----------------------------------------------------------------------------- {"Email":"john.doe@example.com","Phone":"44 123 123456","Twitter":"@johndoe"} {"Email":"jayne.doe@example.com","Phone":""} 2 rows selected. SQL>
The optimizer typically performs a query transformation from dot notation to a JSON_QUERY
call, as discussed here. You need to consider the performance impact of that. It may be preferable to use a single JSON_TABLE
call, rather than making multiple dot notation references. It can also cause some confusion over your indexing strategy.
IS JSON
We have already seen the IS JSON
condition used as part of a check constraint when creating a table to hold JSON data. The IS JSON
condition can be used to test if a column contains JSON data. The following example creates a test table with a CLOB column, but no IS JSON
check constraint to control the contents. The subsequent queries show the use of the IS JSON
condition to test the contents of the column.
DROP TABLE json_documents_no_constraint PURGE; CREATE TABLE json_documents_no_constraint ( id RAW(16) NOT NULL, data CLOB, CONSTRAINT json_documents_nocon_pk PRIMARY KEY (id) ); INSERT INTO json_documents_no_constraint VALUES (SYS_GUID(), '{"FirstName" : "John"}'); INSERT INTO json_documents_no_constraint VALUES (SYS_GUID(), 'John'); COMMIT; -- JSON_VALUE using NULL ON ERROR returns NULL for non-JSON data. SELECT JSON_VALUE(a.data, '$.FirstName') AS first_name FROM json_documents_no_constraint a; FIRST_NAME --------------- John 2 rows selected. SQL> -- Only rows containing JSON are returned. SELECT JSON_VALUE(a.data, '$.FirstName') AS first_name FROM json_documents_no_constraint a WHERE a.data IS JSON; FIRST_NAME --------------- John 1 row selected. SQL>
Not surprisingly, you can use IS NOT JSON
to target those rows that do not contain valid JSON in the column.
JSON_EXISTS
When you query JSON using the dot notation it isn't possible to tell the difference between an element that is missing and one that is present, but has a null value. We know from the data we inserted that Jayne Doe has a phone element with a null value and a missing Twitter element. The query below shows these are equivalent using dot notation.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN Email FORMAT A25 COLUMN Phone FORMAT A15 COLUMN Twitter FORMAT A10 SELECT a.data.FirstName, a.data.LastName, a.data.ContactDetails.Email AS Email, a.data.ContactDetails.Phone AS Phone, a.data.ContactDetails.Twitter AS Twitter FROM json_documents a WHERE a.data.ContactDetails.Phone IS NULL AND a.data.ContactDetails.Twitter IS NULL; FIRSTNAME LASTNAME EMAIL PHONE TWITTER --------------- --------------- ------------------------- --------------- ---------- Jayne Doe jayne.doe@example.com 1 row selected. SQL>
The JSON_EXISTS
condition allows you to make the distinction between empty and missing elements.
-- Check for records where a Phone element is present, but has a null value. COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN Email FORMAT A25 SELECT a.data.FirstName, a.data.LastName, a.data.ContactDetails.Email AS Email FROM json_documents a WHERE JSON_EXISTS(a.data.ContactDetails, '$.Phone' FALSE ON ERROR) AND a.data.ContactDetails.Phone IS NULL; FIRSTNAME LASTNAME EMAIL --------------- --------------- ------------------------- Jayne Doe jayne.doe@example.com 1 row selected. SQL> -- Check for records where Twitter element is missing. COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN Email FORMAT A25 SELECT a.data.FirstName, a.data.LastName, a.data.ContactDetails.Email AS Email FROM json_documents a WHERE NOT JSON_EXISTS(a.data.ContactDetails, '$.Twitter' FALSE ON ERROR); FIRSTNAME LASTNAME EMAIL --------------- --------------- ------------------------- Jayne Doe jayne.doe@example.com 1 row selected. SQL>
The default error handling is FALSE ON ERROR
. Alternatives include TRUE ON ERROR
and ERROR ON ERROR
. The ERROR ON ERROR
option, as the name implies, does not trap any errors produced by the JSON_EXISTS
function.
JSON_VALUE
The JSON_VALUE
function returns an element from the JSON document, based on the specified JSON path.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 SELECT JSON_VALUE(a.data, '$.FirstName') AS first_name, JSON_VALUE(a.data, '$.LastName') AS last_name FROM json_documents a ORDER BY 1, 2; FIRST_NAME LAST_NAME --------------- --------------- Jayne Doe John Doe 2 rows selected. SQL>
It will only return scalar values, not complex values like nested records or arrays. The default error handling is NULL OR ERROR
, so an attempt to return a non-scalar value results in NULL. The follow example attempt to return a non-scalar value, using both the default error handling an the ERROR ON ERROR
option.
SELECT JSON_VALUE(a.data, '$.ContactDetails') AS contact_details FROM json_documents a ORDER BY 1; CONTACT_DETAILS ---------------------------------------- 2 rows selected. SQL> SELECT JSON_VALUE(a.data, '$.ContactDetails' ERROR ON ERROR) AS contact_details FROM json_documents a ORDER BY 1; * ERROR at line 2: ORA-40456: JSON_VALUE evaluated to non-scalar value SQL>
JSON supports boolean values true and false, which are not supported by SQL. The JSON_VALUE
function converts boolean values to the strings true/false or the numbers 1/0. Returning 1/0 is deprecated in Oracle 18c, so you should avoid this in your code.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN Active FORMAT A10 SELECT a.data.FirstName, a.data.LastName, JSON_VALUE(a.data, '$.Active') AS Active, JSON_VALUE(a.data, '$.Active' RETURNING NUMBER) AS ActiveNum FROM json_documents a ORDER BY a.data.FirstName, a.data.LastName; FIRSTNAME LASTNAME ACTIVE ACTIVENUM --------------- --------------- ---------- ---------- Jayne Doe false 0 John Doe true 1 2 rows selected. SQL>
As mentioned previously, the default error handling is NULL ON ERROR
, which means if the value found is non-scalar, no error will be raised. The ERROR ON ERROR
option will return an error if the value found is a non-scalar, like an array.
The JSON_VALUE
function supports the following return types.
{ VARCHAR2 [ ( size [BYTE | CHAR] ) ] | NUMBER [ ( precision [, scale] ) ] | DATE | TIMESTAMP | TIMESTAMP WITH TIME ZONE | SDO_GEOMETRY }
In addition, Oracle 18c added support for CLOB
and BLOB
types also.
The return type is specified using the RETURNING
clause, an example of which is shown below. If the RETURNING
clause is not specified or if RETURNING VARCHAR2
is specified, the VARCHAR2(4000)
return type is assumed.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 SELECT JSON_VALUE(a.data, '$.FirstName' RETURNING VARCHAR2) AS first_name, JSON_VALUE(a.data, '$.LastName' RETURNING VARCHAR2(50)) AS last_name FROM json_documents a ORDER BY 1, 2; FIRST_NAME LAST_NAME --------------- --------------- Jayne Doe John Doe 2 rows selected. SQL>
In Oracle database 23c the RETURNING
clause of the JSON_VALUE
function has been enhanced allowing it to convert JSON data to user-defined types.
JSON_QUERY
The JSON_QUERY
function returns a JSON fragment representing one or more values. In the following example, JSON_QUERY
is used to return a JSON fragment representing all the contact details for each person. The WITH WRAPPER
option surrounds the fragment with square brackets.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN contact_details FORMAT A40 SELECT a.data.FirstName, a.data.LastName, JSON_QUERY(a.data, '$.ContactDetails' WITH WRAPPER) AS contact_details FROM json_documents a ORDER BY a.data.FirstName, a.data.Last_name; FIRSTNAME LASTNAME CONTACT_DETAILS --------------- --------------- ---------------------------------------- Jayne Doe [{"Email":"jayne.doe@example.com","Phone ":""}] John Doe [{"Email":"john.doe@example.com","Phone" :"44 123 123456","Twitter":"@johndoe"}] 2 rows selected. SQL>
The JSON_QUERY
function supports the following return types.
VARCHAR2 [ ( size [BYTE | CHAR] ) ]
In addition, Oracle 18c added support for CLOB
and BLOB
types also.
The return type is specified using the RETURNING
clause, an example of which is shown below. If the RETURNING
clause is not specified or if RETURNING VARCHAR2
is specified, the VARCHAR2(4000)
return type is assumed.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN contact_details FORMAT A40 SELECT a.data.FirstName, a.data.LastName, JSON_QUERY(a.data, '$.ContactDetails' RETURNING VARCHAR2(1000) WITH WRAPPER) AS contact_details FROM json_documents a ORDER BY a.data.FirstName, a.data.Last_name; FIRSTNAME LASTNAME CONTACT_DETAILS --------------- --------------- ---------------------------------------- Jayne Doe [{"Email":"jayne.doe@example.com","Phone ":""}] John Doe [{"Email":"john.doe@example.com","Phone" :"44 123 123456","Twitter":"@johndoe"}] 2 rows selected. SQL>
JSON_TABLE
Oracle 18c includes a number of enhancements to the JSON_TABLE
function, including a simplified syntax, which you can read about here.
The JSON_TABLE
function incorporates all the functionality of JSON_VALUE
, JSON_EXISTS
and JSON_QUERY
. The syntax is a little more complicated that using the individual JSON functions, but it is more efficient to use a single JSON_TABLE
call than combining multiple calls to the other individual functions into a single query.
JSON_TABLE
is used for making JSON data look like relational data, which is especially useful when creating relational views over JSON data, as show below.
CREATE OR REPLACE VIEW json_documents_v AS SELECT jt.first_name, jt.last_name, jt.job, jt.addr_street, jt.addr_city, jt.addr_country, jt.addr_postcode, jt.email, jt.phone, jt.twitter, TO_DATE(jt.dob, 'DD-MON-YYYY') AS dob, jt.active 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', addr_street VARCHAR2(50 CHAR) PATH '$.Address.Street', addr_city VARCHAR2(50 CHAR) PATH '$.Address.City', addr_country VARCHAR2(50 CHAR) PATH '$.Address.Country', addr_postcode VARCHAR2(50 CHAR) PATH '$.Address.Postcode', email VARCHAR2(100 CHAR) PATH '$.ContactDetails.Email', phone VARCHAR2(50 CHAR) PATH '$.ContactDetails.Phone', twitter VARCHAR2(50 CHAR) PATH '$.ContactDetails.Twitter', dob VARCHAR2(11 CHAR) PATH '$.DateOfBirth', active VARCHAR2(5 CHAR) PATH '$.Active')) jt; COLUMN first_name FORMAT A15 COLUMN last_name FORMAT A15 SELECT first_name, last_name, dob FROM json_documents_v ORDER BY first_name, last_name; FIRST_NAME LAST_NAME DOB --------------- --------------- -------------------- Jayne Doe 01-JAN-1982 00:00:00 John Doe 01-JAN-1980 00:00:00 2 rows selected. SQL>
The COLUMNS
clause defines the how the data for each column is identified and presented (column projection).
There are variety of options to traverse the JSON documents, including processing arrays using the NESTED
clause (example here), to control how data is presented. In the following example, contact details are presented in JSON format.
COLUMN first_name FORMAT A15 COLUMN last_name FORMAT A15 COLUMN contact_details FORMAT A40 SELECT jt.first_name, jt.last_name, jt.contact_details FROM json_documents, JSON_TABLE(data, '$' COLUMNS (first_name VARCHAR2(50 CHAR) PATH '$.FirstName', last_name VARCHAR2(50 CHAR) PATH '$.LastName', contact_details VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.ContactDetails')) jt; FIRST_NAME LAST_NAME CONTACT_DETAILS --------------- --------------- ---------------------------------------- John Doe [{"Email":"john.doe@example.com","Phone" :"44 123 123456","Twitter":"@johndoe"}] Jayne Doe [{"Email":"jayne.doe@example.com","Phone ":""}] 2 rows selected. SQL>
In Oracle 12.2 multiple calls to JSON_EXISTS
, JSON_VALUE
, and JSON_QUERY
may be rewritten to fewer JSON_TABLE
calls to improve performance.
JSON_TEXTCONTAINS
The JSON_TEXTCONTAINS
condition is only available once a JSON search index has been created. An example of this can be found here.
JSON_EQUAL (18c)
The JSON_EQUAL
condition was introduced in 18c to allow JSON documents to be compared regardless of member order of document formatting. You can read more about this in the following article.
FORMAT JSON Clause
The FORMAT JSON
clause is optional and is provided for "semantic clarity". For the most part Oracle understands if data is in JSON format, so this clause is redundant, but if you are supplying JSON in the form of a BLOB you must use the FORMAT JSON
clause. You will see it being used in some of the examples in this article.
Identifying Columns Containing JSON
The [USER|ALL|DBA]_JSON_COLUMNS
views can be used to identify tables and columns containing JSON data.
COLUMN table_name FORMAT A15 COLUMN column_name FORMAT A15 SELECT table_name, column_name, format, data_type FROM user_json_columns; TABLE_NAME COLUMN_NAME FORMAT DATA_TYPE --------------- --------------- --------- ------------- JSON_DOCUMENTS DATA TEXT CLOB 1 row selected. SQL>
Loading JSON Files Using External Tables
Oracle provide an example JSON dump file from a popular NoSQL database, which you can use to test the external table functionality. This example is taken from the Oracle documentation.
Create the directory objects for use with the external table.
CONN sys@pdb1 AS SYSDBA CREATE OR REPLACE DIRECTORY order_entry_dir AS '/u01/app/oracle/product/12.1.0.2/db_1/demo/schema/order_entry'; GRANT READ, WRITE ON DIRECTORY order_entry_dir TO test; CREATE OR REPLACE DIRECTORY loader_output_dir AS '/tmp'; GRANT READ, WRITE ON DIRECTORY loader_output_dir TO test;
Create the external table and query it to check it is working.
CONN test/test@pdb1 CREATE TABLE json_dump_file_contents (json_document CLOB) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY order_entry_dir ACCESS PARAMETERS (RECORDS DELIMITED BY 0x'0A' DISABLE_DIRECTORY_LINK_CHECK BADFILE loader_output_dir: 'JSONDumpFile.bad' LOGFILE order_entry_dir: 'JSONDumpFile.log' FIELDS (json_document CHAR(5000))) LOCATION (order_entry_dir:'PurchaseOrders.dmp')) PARALLEL REJECT LIMIT UNLIMITED; SELECT COUNT(*) FROM json_dump_file_contents; COUNT(*) ---------- 10000 1 row selected. SQL>
You can now load the database table with the contents of the external table.
TRUNCATE TABLE json_documents; INSERT /*+ APPEND */ INTO json_documents SELECT SYS_GUID(), json_document FROM json_dump_file_contents WHERE json_document IS JSON; COMMIT;
Dot Notation Query Transformation
It's worth keeping in mind the dot notation syntax is a query transformation. If we run a query using dot notation and perform a 10053 trace we can see this.
Check the trace file for the session.
SELECT value FROM v$diag_info WHERE name = 'Default Trace File'; VALUE ---------------------------------------------------------------- /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_2861.trc 1 row selected. SQL>
Perform a 10053 trace of the statement.
ALTER SESSION SET EVENTS '10053 trace name context forever'; SELECT a.data.FirstName, a.data.LastName FROM json_documents a; ALTER SESSION SET EVENTS '10053 trace name context off';
The section beginning with "Final query after transformations" shows the statement that was actually processed, after the query transformation.
Final query after transformations:******* UNPARSED QUERY IS ******* SELECT JSON_QUERY("A"."DATA" FORMAT JSON , '$.FirstName' RETURNING VARCHAR2(4000) ASIS WITHOUT ARRAY WRAPPER NULL ON ERROR) "FIRSTNAME", JSON_QUERY("A"."DATA" FORMAT JSON , '$.LastName' RETURNING VARCHAR2(4000) ASIS WITHOUT ARRAY WRAPPER NULL ON ERROR) "LASTNAME" FROM "TEST"."JSON_DOCUMENTS" "A"
As you can see, the statement has been rewritten to use JSON_QUERY
calls. That is important for two reasons.
- Performance : It may be more efficient to use a single
JSON_TABLE
call to retrieve the data, rather than have a separateJSON_QUERY
call for each dot notation reference. - Indexing : When you start to index JSON data, you have to understand how the index usage will be affected when using combinations of direct function calls and dot notation. Depending on the circumstances, the query transformation can vary, causing your expensive indexes not to be used.
It may be a good idea to avoid dot notation entirely, as it will probably make the process of deciding on an indexing strategy much simpler. You can read more about indexing JSON data here.
Error Handling
Most SQL/JSON functions have some form of ON ERROR
clause, which will be a variant on this.
{ ERROR | NULL | DEFAULT literal } ON ERROR
The default for most functions is to return NULL in the event of an error. You can alter this by adding the required ON ERROR
clause. The examples below show the usage with JSON_VALUE
and JSON_TABLE
calls.
COLUMN FirstName FORMAT A15 COLUMN LastName FORMAT A15 COLUMN Active FORMAT A10 SELECT a.data.FirstName, a.data.LastName, JSON_VALUE(a.data, '$.Active' ERROR ON ERROR) AS Active, JSON_VALUE(a.data, '$.Active' RETURNING NUMBER ERROR ON ERROR) AS ActiveNum FROM json_documents a ORDER BY a.data.FirstName, a.data.LastName; COLUMN first_name FORMAT A15 COLUMN last_name FORMAT A15 COLUMN contact_details FORMAT A40 SELECT jt.first_name, jt.last_name, jt.contact_details FROM json_documents, JSON_TABLE(data, '$' ERROR ON ERROR COLUMNS (first_name VARCHAR2(50 CHAR) PATH '$.FirstName' ERROR ON ERROR, last_name VARCHAR2(50 CHAR) PATH '$.LastName' ERROR ON ERROR, contact_details VARCHAR2(4000 CHAR) FORMAT JSON WITH WRAPPER PATH '$.ContactDetails' ERROR ON ERROR)) jt;
For more information see:
- JSON in Oracle Database
- JSON Conditions : IS JSON, JSON_EXISTS, JSON_TEXTCONTAINS
- JSON_QUERY
- JSON_TABLE
- JSON_VALUE
- JSON Support in Oracle Database 12c
- Indexing JSON Data in Oracle Database 12c
- Indexing JSON Data in Oracle Database 12c Release 1 (12.1.0.2)
- SQL/JSON Functions in Oracle Database 12c Release 2 (12.2)
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...