Using JSON_TABLE with JSON Arrays

22.4 Using JSON_TABLE with JSON Arrays

A JSON value can be an array or can include one or more arrays, nested to any number of levels inside other JSON arrays or objects. You can use json_table with a NESTED PATH clause to project specific elements of an array.

Example 22-6 projects the requestor and associated phone numbers from the JSON data in column data. The entire JSON array Phone is projected as a column of JSON data, ph_arr. To format this JSON data as a VARCHAR2 column, the keywords FORMAT JSON are needed if the JSON data is not of JSON data type (and those keywords raise an error if the type is JSON data).

What if you wanted to project the individual elements of JSON array Phone and not the array as a whole? Example 22-7 shows one way to do this, which you can use if the array elements are the only data you need to project.

If you want to project both the requestor and the corresponding phone data then the row path expression of Example 22-7 ($.Phone[*]) is not appropriate: it targets only the (phone object) elements of array Phone.

Example 22-8 shows one way to target both: use a row path expression that targets both the name and the entire phones array, and use column path expressions that target fields type and number of individual phone objects.

In Example 22-8 as in Example 22-6, keywords FORMAT JSON are needed if the JSON data is not of JSON data type, because the resulting VARCHAR2 columns contain JSON data, namely arrays of phone types or phone numbers, with one array element for each phone. In addition, unlike the case for Example 22-6, a wrapper clause is needed for column phone_type and column phone_num, because array Phone contains multiple objects with fields type and number.

Sometimes you might not want the effect of Example 22-8. For example, you might want a column that contains a single phone number (one row per number), rather than one that contains a JSON array of phone numbers (one row for all numbers for a given purchase order).

To obtain that result, you need to tell json_table to project the array elements, by using a json_table NESTED path clause for the array. A NESTED path clause acts, in effect, as an additional row source (row pattern). Example 22-9 illustrates this.

You can use any number of NESTED keywords in a given json_table invocation.

In Example 22-9 the outer COLUMNS clause is the parent of the nested (inner) COLUMNS clause. The virtual tables defined are joined using an outer join, with the table defined by the parent clause being the outer table in the join.

(If there were a second columns clause nested directly under the same parent, the two nested clauses would be sibling COLUMNS clauses.)

Example 22-6 Projecting an Entire JSON Array as JSON Data

SELECT jt.*
  FROM j_purchaseorder,
       json_table(data, '$'
         COLUMNS (requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
                  ph_arr    VARCHAR2(100 CHAR) FORMAT JSON
                            PATH '$.ShippingInstructions.Phone')
                 ) AS "JT";

Example 22-7 Projecting Elements of a JSON Array

SELECT jt.*
  FROM j_purchaseorder,
       json_table(data, '$.ShippingInstructions.Phone[*]'
         COLUMNS (phone_type VARCHAR2(10) PATH '$.type',
                  phone_num  VARCHAR2(20) PATH '$.number')) AS "JT";

PHONE_TYPE     PHONE_NUM
----------     ---------
Office         909-555-7307
Mobile         415-555-1234

Example 22-8 Projecting Elements of a JSON Array Plus Other Data

SELECT jt.*
  FROM j_purchaseorder,
       json_table(data, '$'
         COLUMNS (
           requestor  VARCHAR2(32 CHAR) PATH '$.Requestor',
           phone_type VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
                      PATH '$.ShippingInstructions.Phone[*].type',
           phone_num  VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER
                      PATH '$.ShippingInstructions.Phone[*].number')) AS "JT";

REQUESTOR    PHONE_TYPE            PHONE_NUM
---------    ----------            ---------
Alexis Bull  ["Office", "Mobile"]  ["909-555-7307", "415-555-1234"]

Example 22-9 JSON_TABLE: Projecting Array Elements Using NESTED

This example shows two equivalent queries that project array elements. The first query uses the simple, dot-notation syntax for the expressions that target the row and column data. The second uses the full syntax.

Except for column number, whose SQL identifier is quoted ("number"), the SQL column names are, in effect, uppercase. (Column number is lowercase.)

In the first query the column names are written exactly the same as the field names that are targeted, including with respect to letter case. Regardless of whether they are quoted, they are interpreted case-sensitively for purposes of establishing the proper path.

The second query has:

Separate arguments of a JSON column-expression and a SQL/JSON row path-expression
Explicit column data types of VARCHAR2(4000)
Explicit PATH clauses with SQL/JSON column path expressions, to target the object fields that are projected

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.data
         COLUMNS (Requestor,
                  NESTED ShippingInstructions.Phone[*]
                    COLUMNS (type, "number"))) AS "JT";

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.data, '$'
         COLUMNS (Requestor VARCHAR2(4000) PATH '$.Requestor',
                  NESTED
                    PATH '$.ShippingInstructions.Phone[*]'
                    COLUMNS (type     VARCHAR2(4000) PATH '$.type',
                             "number" VARCHAR2(4000) PATH '$.number'))
       ) AS "JT";

Related Topics

Parent topic: SQL/JSON Function JSON_TABLE