12.17.6 JSON Table Functions

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

Extracts data from a JSON document and returns it as a relational table having the specified columns. The complete syntax for this function is shown here:

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_error] [on_empty]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_error:
    {NULL | ERROR | DEFAULT json_string} ON ERROR

on_empty:
    {NULL | ERROR | DEFAULT json_string} ON EMPTY

expr: This is an expression that returns JSON data. This can be a constant ('{"a":1}'), a column (t1.json_data, given table t1 specified prior to JSON_TABLE() in the FROM clause), or a function call (JSON_EXTRACT(t1,jsn_data,'$.post.comments')).

path: A JSON path expression, which is applied to the data source. We refer to the JSON value matching the path as the row source; this is used to generate a row of relational data. The COLUMNS clause evaluates the row source, finds specific JSON values within the row source, and returns those JSON values as SQL values in individual columns of a row of relational data.

The alias is required. The usual rules for table aliases apply (see Section 9.2, “Schema Object Names”).

JSON_TABLE() supports four types of columns, described in the following list:

Column names are subject to the usual rules and limitations governing table column names. See Section 9.2, “Schema Object Names”.

All JSON and JSON path expressions are checked for validity; an invalid expression of either type causes an error.

Each match for the path preceding the COLUMNS keyword maps to an individual row in the result table. For example, the following query gives the result shown here:

The expression "$[*]" matches each element of the array. You can filter the rows in the result by modifying the path. For example, using "$[1]" limits extraction to the second element of the JSON array used as the source, as shown here:

Within a column definition, "$" passes the entire match to the column; "$.x" and "$.y" pass only the values corresponding to the keys x and y, respectively, within that match. For more information, see JSON Path Syntax.

NESTED PATH (or simply NESTED; PATH is optional) produces a set of records for each match in the COLUMNS clause to which it belongs. If there is no match, all columns of the nested path are set to NULL. This implements an outer join between the topmost clause and NESTED [PATH]. An inner join can be emulated by applying a suitable condition in the WHERE clause, as shown here:

Sibling nested paths—that is, two or more instances of NESTED [PATH] in the same COLUMNS clause—are processed one after another, one at a time. While one nested path is producing records, columns of any sibling nested path expressions are set to NULL. This means that the total number of records for a single match within a single containing COLUMNS clause is the sum and not the product of all records produced by NESTED [PATH] modifiers, as shown here:

A FOR ORDINALITY column enumerates records produced by the COLUMNS clause, and can be used to distinguish parent records of a nested path, especially if values in parent records are the same, as can be seen here:

The source document contains an array of two elements; each of these elements produces two rows. The values of apath and bpath are the same over the entire result set; this means that they cannot be used to determine whether lpath values came from the same or different parents. The value of the ord column remains the same as the set of records having top_ord equal to 1, so these two values are from a single object. The remaining two values are from different objects, since they have different values in the ord column.