📋Steps in this guide1/15

JSON Support in Oracle Database 12c Release 1 (12.1.0.2)

This article describes the server side support for JSON in the database.

oracle 12cconfigurationintermediate

by OracleDba

19 views

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 or columns. It can also be stored in , and 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 constraint indicates the column contains valid JSON data. In the following example, a table is created to hold JSON documents. The check constraint enforces lax JSON syntax by default. If you want to make it enforce strict JSON syntax, you need to add the qualifier. The rest of the article will assume the lax JSON syntax enforcement is used. The presence of the constraint means that invalid JSON will cause an error during insert or update. Provided the associated column has an check constraint, the individual elements of a JSON document can be referenced directly from SQL using dot notation. 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". If a non-scalar value is a referenced, the result is returned as a JSON fragment. The optimizer typically performs a query transformation from dot notation to a call, as discussed here . You need to consider the performance impact of that. It may be preferable to use a single call, rather than making multiple dot notation references. It can also cause some confusion over your indexing strategy. We have already seen the condition used as part of a check constraint when creating a table to hold JSON data. The 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 check constraint to control the contents. The subsequent queries show the use of the condition to test the contents of the column. Not surprisingly, you can use to target those rows that do not contain valid JSON in the column. 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. The condition allows you to make the distinction between empty and missing elements. The default error handling is . Alternatives include and . The option, as the name implies, does not trap any errors produced by the function. The function returns an element from the JSON document, based on the specified JSON path. It will only return scalar values, not complex values like nested records or arrays. The default error handling is , 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 option. JSON supports boolean values true and false, which are not supported by SQL. The 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. As mentioned previously, the default error handling is , which means if the value found is non-scalar, no error will be raised. The option will return an error if the value found is a non-scalar, like an array. The function supports the following return types. In addition, Oracle 18c added support for and types also. The return type is specified using the clause, an example of which is shown below. If the clause is not specified or if is specified, the return type is assumed. In Oracle database 23ai the clause of the function has been enhanced allowing it to convert JSON data to user-defined types. - JSON_VALUE Function Enhancements in Oracle Database 23ai The function returns a JSON fragment representing one or more values. In the following example, is used to return a JSON fragment representing all the contact details for each person. The option surrounds the fragment with square brackets. The function supports the following return types. In addition, Oracle 18c added support for and types also. The return type is specified using the clause, an example of which is shown below. If the clause is not specified or if is specified, the return type is assumed. Oracle 18c includes a number of enhancements to the function, including a simplified syntax, which you can read about here . The function incorporates all the functionality of , and . The syntax is a little more complicated that using the individual JSON functions, but it is more efficient to use a single call than combining multiple calls to the other individual functions into a single query. is used for making JSON data look like relational data, which is especially useful when creating relational views over JSON data, as show below. The 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 clause (example here ), to control how data is presented. In the following example, contact details are presented in JSON format. In Oracle 12.2 multiple calls to , , and may be rewritten to fewer calls to improve performance. The condition is only available once a JSON search index has been created. An example of this can be found here . The 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. The 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 clause. You will see it being used in some of the examples in this article. The views can be used to identify tables and columns containing JSON data. 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. Create the external table and query it to check it is working. You can now load the database table with the contents of the external table. 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. Perform a 10053 trace of the statement. The section beginning with "Final query after transformations" shows the statement that was actually processed, after the query transformation. As you can see, the statement has been rewritten to use calls. That is important for two reasons. - Performance : It may be more efficient to use a single call to retrieve the data, rather than have a separate 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 . Most SQL/JSON functions have some form of clause, which will be a variant on this. The default for most functions is to return NULL in the event of an error. You can alter this by adding the required clause. The examples below show the usage with and calls. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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)
);

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))
);

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"    : "[email protected]",
                              "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"    : "[email protected]",
                              "Phone"    : ""
                             },
          "DateOfBirth"    : "01-JAN-1982",
          "Active"         : false
         }');

COMMIT;

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>

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    [email protected]
John            Doe             A12 34B    [email protected]

2 rows selected.

SQL>

SELECT a.data.Address.Postcode
FROM   json_documents a;

ADDRESS
----------
A12 34B
A12 34B

2 rows selected.

SQL>

SELECT a.data.ContactDetails
FROM   json_documents a;

CONTACTDETAILS
-----------------------------------------------------------------------------
{"Email":"[email protected]","Phone":"44 123 123456","Twitter":"@johndoe"}
{"Email":"[email protected]","Phone":""}

2 rows selected.

SQL>

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>

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             [email protected]

1 row selected.

SQL>

-- 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             [email protected]

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             [email protected]

1 row selected.

SQL>

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>

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>

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>

{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| NUMBER [ ( precision [, scale] ) ]
| DATE
| TIMESTAMP
| TIMESTAMP WITH TIME ZONE
| SDO_GEOMETRY
}

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>

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":"[email protected]","Phone
                                ":""}]

John            Doe             [{"Email":"[email protected]","Phone"
                                :"44 123 123456","Twitter":"@johndoe"}]


2 rows selected.

SQL>

VARCHAR2 [ ( size [BYTE | CHAR] ) ]

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":"[email protected]","Phone
                                ":""}]

John            Doe             [{"Email":"[email protected]","Phone"
                                :"44 123 123456","Twitter":"@johndoe"}]


2 rows selected.

SQL>

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>

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":"[email protected]","Phone"
                                :"44 123 123456","Twitter":"@johndoe"}]

Jayne           Doe             [{"Email":"[email protected]","Phone
                                ":""}]


2 rows selected.

SQL>

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>

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;

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>

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;

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>

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';

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"

{ ERROR | NULL | DEFAULT literal } ON ERROR

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;

Dot Notation

Provided the associated column has an check constraint, the individual elements of a JSON document can be referenced directly from SQL using dot notation. 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". If a non-scalar value is a referenced, the result is returned as a JSON fragment. The optimizer typically performs a query transformation from dot notation to a call, as discussed here . You need to consider the performance impact of that. It may be preferable to use a single call, rather than making multiple dot notation references. It can also cause some confusion over your indexing strategy.

Code/Command (click line numbers to comment):

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    [email protected]
John            Doe             A12 34B    [email protected]

2 rows selected.

SQL>

SELECT a.data.Address.Postcode
FROM   json_documents a;

ADDRESS
----------
A12 34B
A12 34B

2 rows selected.

SQL>

SELECT a.data.ContactDetails
FROM   json_documents a;

CONTACTDETAILS
-----------------------------------------------------------------------------
{"Email":"[email protected]","Phone":"44 123 123456","Twitter":"@johndoe"}
{"Email":"[email protected]","Phone":""}

2 rows selected.

SQL>

IS JSON

We have already seen the condition used as part of a check constraint when creating a table to hold JSON data. The 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 check constraint to control the contents. The subsequent queries show the use of the condition to test the contents of the column. Not surprisingly, you can use to target those rows that do not contain valid JSON in the column.

Code/Command (click line numbers to comment):

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>

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. The condition allows you to make the distinction between empty and missing elements. The default error handling is . Alternatives include and . The option, as the name implies, does not trap any errors produced by the function.

Code/Command (click line numbers to comment):

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             [email protected]

1 row selected.

SQL>

-- 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             [email protected]

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             [email protected]

1 row selected.

SQL>

JSON_VALUE

The function returns an element from the JSON document, based on the specified JSON path. It will only return scalar values, not complex values like nested records or arrays. The default error handling is , 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 option. JSON supports boolean values true and false, which are not supported by SQL. The 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. As mentioned previously, the default error handling is , which means if the value found is non-scalar, no error will be raised. The option will return an error if the value found is a non-scalar, like an array. The function supports the following return types. In addition, Oracle 18c added support for and types also. The return type is specified using the clause, an example of which is shown below. If the clause is not specified or if is specified, the return type is assumed. In Oracle database 23ai the clause of the function has been enhanced allowing it to convert JSON data to user-defined types. - JSON_VALUE Function Enhancements in Oracle Database 23ai

Code/Command (click line numbers to comment):

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>

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>

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>

{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| NUMBER [ ( precision [, scale] ) ]
| DATE
| TIMESTAMP
| TIMESTAMP WITH TIME ZONE
| SDO_GEOMETRY
}

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>

JSON_QUERY

The function returns a JSON fragment representing one or more values. In the following example, is used to return a JSON fragment representing all the contact details for each person. The option surrounds the fragment with square brackets. The function supports the following return types. In addition, Oracle 18c added support for and types also. The return type is specified using the clause, an example of which is shown below. If the clause is not specified or if is specified, the return type is assumed.

Code/Command (click line numbers to comment):

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":"[email protected]","Phone
                                ":""}]

John            Doe             [{"Email":"[email protected]","Phone"
                                :"44 123 123456","Twitter":"@johndoe"}]


2 rows selected.

SQL>

VARCHAR2 [ ( size [BYTE | CHAR] ) ]

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":"[email protected]","Phone
                                ":""}]

John            Doe             [{"Email":"[email protected]","Phone"
                                :"44 123 123456","Twitter":"@johndoe"}]


2 rows selected.

SQL>

JSON_TABLE

Oracle 18c includes a number of enhancements to the function, including a simplified syntax, which you can read about here . The function incorporates all the functionality of , and . The syntax is a little more complicated that using the individual JSON functions, but it is more efficient to use a single call than combining multiple calls to the other individual functions into a single query. is used for making JSON data look like relational data, which is especially useful when creating relational views over JSON data, as show below. The 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 clause (example here ), to control how data is presented. In the following example, contact details are presented in JSON format. In Oracle 12.2 multiple calls to , , and may be rewritten to fewer calls to improve performance.

Code/Command (click line numbers to comment):

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>

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":"[email protected]","Phone"
                                :"44 123 123456","Twitter":"@johndoe"}]

Jayne           Doe             [{"Email":"[email protected]","Phone
                                ":""}]


2 rows selected.

SQL>

JSON_TEXTCONTAINS

The condition is only available once a JSON search index has been created. An example of this can be found here .

JSON_EQUAL (18c)

The 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 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 clause. You will see it being used in some of the examples in this article.

Identifying Columns Containing JSON

The views can be used to identify tables and columns containing JSON data.

Code/Command (click line numbers to comment):

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. Create the external table and query it to check it is working. You can now load the database table with the contents of the external table.

Code/Command (click line numbers to comment):

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;

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>

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. Perform a 10053 trace of the statement. The section beginning with "Final query after transformations" shows the statement that was actually processed, after the query transformation. As you can see, the statement has been rewritten to use calls. That is important for two reasons. - Performance : It may be more efficient to use a single call to retrieve the data, rather than have a separate 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 .

Code/Command (click line numbers to comment):

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>

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';

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"

Error Handling

Most SQL/JSON functions have some form of clause, which will be a variant on this. The default for most functions is to return NULL in the event of an error. You can alter this by adding the required clause. The examples below show the usage with and calls. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

{ ERROR | NULL | DEFAULT literal } ON ERROR

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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!