SQL NESTED Clause Instead of JSON

22.1 SQL NESTED Clause Instead of JSON_TABLE

In a SELECT clause you can often use a NESTED clause instead of SQL/JSON function json_table. This can mean a simpler query expression. It also has the advantage of including rows with non-NULL relational columns when the JSON column is NULL.

The NESTED clause is a shortcut for using json_table with an ANSI left outer join. That is, these two queries are equivalent:

SELECT ... 
  FROM mytable NESTED jcol COLUMNS (...);

SELECT ...
  FROM mytable t1 LEFT OUTER JOIN
       json_table(t1.jcol COLUMNS (...)
       ON 1=1;

Using a left outer join with json_table, or using the NESTED clause, allows the selection result to include rows with relational columns where there is no corresponding JSON-column data, that is, where the JSON column is NULL. The only semantic difference between the two is that if you use a NESTED clause then the JSON column itself is not included in the result.

The NESTED clause provides the same COLUMNS clause as json_table, including the possibility of nested columns. These are the advantages of using NESTED:

You need not provide a table alias, even if you use the simple dot notation.
You need not provide an is json check constraint, even if the JSON column is not JSON type. (The constraint is needed for json_table with the simple dot notation, unless the column is JSON type.)
You need not specify LEFT OUTER JOIN.

The NESTED clause syntax is simpler, it allows all of the flexibility of the COLUMNS clause, and it performs an implicit left outer join. This is illustrated in Example 22-2.

Example 22-3 shows the use of a NESTED clause with the simple dot notation.

Example 22-2 Equivalent: SQL NESTED and JSON_TABLE with LEFT OUTER JOIN

These two queries are equivalent. One uses SQL/JSON function json_table with an explicit LEFT OUTER JOIN. The other uses a SQL NESTED clause.

SELECT id, requestor, type, "number"
  FROM j_purchaseorder LEFT OUTER JOIN
       json_table(po_document
         COLUMNS (Requestor,
                  NESTED ShippingInstructions.Phone[*]
                    COLUMNS (type, "number")))
       ON 1=1);

SELECT id, requestor, type, "number"
  FROM j_purchaseorder NESTED
       po_document
         COLUMNS (Requestor,
                  NESTED ShippingInstructions.Phone[*]
                    COLUMNS (type, "number"));

The output is the same in both cases:

7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Office 909-555-7307
7C3A54B183056369E0536DE05A0A15E4 Alexis Bull Mobile 415-555-1234
7C3A54B183066369E0536DE05A0A15E4 Sarah Bell

If table j_purchaseorder had a row with non-NULL values for columns id and requestor, but a NULL value for column po_document then that row would appear in both cases. But it would not appear in the json_table case if LEFT OUTER JOIN were absent.

Example 22-3 Using SQL NESTED To Expand a Nested Array

This example selects columns id and date_loaded from table j_purchaseorder, along with the array elements of field Phone, which is nested in the value of field ShippingInstructions of JSON column po_document. It expands the Phone array value as columns type and number.

(Column specification "number" requires the double-quote marks because number is a reserved term in SQL.)

SELECT *
  FROM j_purchaseorder NESTED
       po_document.ShippingInstructions.Phone[*]
         COLUMNS (type, "number")

Parent topic: SQL/JSON Function JSON_TABLE