22.3 JSON_TABLE Generalizes SQL/JSON Query Functions and Conditions

SQL/JSON function json_table generalizes SQL/JSON condition json_exists and SQL/JSON functions json_value and json_query. Everything that you can do using these functions you can do using json_table. For the jobs they accomplish, the syntax of these functions is simpler to use than is the syntax of json_table.

If you would otherwise use any of json_exists, json_value, or json_query more than once, or use them in combination, to access the same data, then you can instead use a single invocation of json_table. This can often make a query more readable, and it ensures that the query is optimized to read the data only once.

Because of this, the optimizer typically automatically rewrites multiple invocations of json_exists, json_value and json_query (any combination) to fewer invocations of json_table. (You can examine an execution plan, to check whether such rewriting occurs for a given query.)

Example 22-4 and Example 22-5 illustrate this. They each select the requestor and the set of phones used by each object in column j_purchaseorder.data. But the example with json_table reads that column only once, not four times.

These examples use BOOLEAN SQL values to represent a Boolean JSON values. (Oracle SQL support for data type BOOLEAN is introduced in Oracle Database Release 23ai.)

A JSON value of null is a value as far as SQL is concerned; it is not NULL, which in SQL represents the absence of a value (missing, unknown, or inapplicable data). In these examples, if the JSON value of object attribute zipCode is null then the SQL BOOLEAN value TRUE is returned.

Example 22-4 Accessing JSON Data Multiple Times to Extract Data

This example uses four invocations of SQL functions to access SQL column j_purchaseorder.data, so it reads that column four times. 

SELECT json_value(data, '$.Requestor' RETURNING VARCHAR2(32)),
       json_query(data, '$.ShippingInstructions.Phone'
                  RETURNING VARCHAR2(100))
  FROM j_purchaseorder
  WHERE json_exists(data, '$.ShippingInstructions.Address.zipCode')
    AND json_value(data,  '$.AllowPartialShipment'
                   RETURNING BOOLEAN) = TRUE;

Example 22-5 Using JSON_TABLE to Extract Data Without Multiple Reads

This example uses a single json_table invocation to access SQL column j_purchaseorder.data, so it reads that column only once.

The example uses BOOLEAN SQL values for both virtual columns:

  • Column partial corresponds to a JSON Boolean value in the data (field AllowPartialShipment). json_value semantics are used for this column.

  • Column has_zip results from the use of json_table keyword EXISTS, which says to use the semantics of json_exists.

Note: If the JSON data is of JSON data type then do not use keywords FORMAT JSON; otherwise, an error is raised. 

SELECT jt.requestor, jt.phones
  FROM j_purchaseorder,
       json_table(data, '$'
         COLUMNS (
           requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
           phones    VARCHAR2(100 CHAR) FORMAT JSON
                     PATH '$.ShippingInstructions.Phone',
           partial   BOOLEAN PATH '$.AllowPartialShipment',
           has_zip   BOOLEAN EXISTS
                     PATH '$.ShippingInstructions.Address.zipCode')) jt
  WHERE jt.partial AND jt.has_zip;

The WHERE clause could alternatively be written this way: 

WHERE jt.partial = TRUE AND jt.has_zip = TRUE

Related Topics

Parent topic: SQL/JSON Function JSON_TABLE