Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

JSON_TABLE

JSON_TABLE presents JSON text as a table: a row for each item a path finds, and a column for each value taken of the item by a path of its own. It is a table reference of the FROM clause, read laterally, so its text may be a column of a table before it. Nested columns turn arrays within each item into rows of their own, joined to their parent's row as a plan says. JSON_VALUE, JSON_QUERY and the path language are in Value Expressions; the examples here read their table PURCHASES.

Syntax

JSON_TABLE ( <context> [ FORMAT JSON ] , '<path>' [ AS <path name> ]
             [ PASSING <value> [ FORMAT JSON ] AS <identifier> [ , ... ] ]
             COLUMNS ( <column definition> [ , ... ] )
             [ PLAN ( <plan> ) | PLAN DEFAULT ( <choices> ) ]
             [ { ERROR | EMPTY } ON ERROR ] )
  [ AS ] <correlation name> [ ( <derived column list> ) ]

<column definition> ::=
    <column name> FOR ORDINALITY
  | <column name> <data type> [ PATH '<path>' ]
        [ { ERROR | NULL | DEFAULT <value expression> } ON EMPTY ]
        [ { ERROR | NULL | DEFAULT <value expression> } ON ERROR ]
  | <column name> <data type> FORMAT JSON [ PATH '<path>' ]
        [ WITHOUT [ ARRAY ] WRAPPER | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] WRAPPER ]
        [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
        [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ]
        [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ]
  | NESTED [ PATH ] '<path>' [ AS <path name> ] COLUMNS ( <column definition> [ , ... ] )

<plan> ::=
    <path name>
  | <path name> { OUTER | INNER } <plan primary>
  | <plan primary> UNION <plan primary> [ UNION <plan primary> ]...
  | <plan primary> CROSS <plan primary> [ CROSS <plan primary> ]...

<plan primary> ::= <path name> | ( <plan> )

<choices> ::= { INNER | OUTER } [ , { UNION | CROSS } ] | { UNION | CROSS } [ , { INNER | OUTER } ]

The context is a character string of JSON text, or a value of the JSON type. Each path is a character string literal, read as the statement is prepared.

Columns

The path after the context is the row pattern: each item it finds is a row. Each column takes its value of that item:

Column Value
name FOR ORDINALITY the row's number, from 1, an INTEGER
name type [PATH ...] the one scalar its path finds, cast to its type, as JSON_VALUE gives it: a character string, numeric, BOOLEAN or datetime type
name type FORMAT JSON [PATH ...] what its path finds, as JSON text of its type, a character string type, as JSON_QUERY gives it, with its wrapper and quotes
name JSON [PATH ...] the same, a value of the JSON type

A column without PATH reads the member of its name: $."NAME", the name as SQL folds it, so a column sku reads the member SKU, and a column "sku" the member sku.

SELECT P.ID, JT.*
  FROM PURCHASES P,
       JSON_TABLE(P.DOC, 'lax $.items[*]'
         COLUMNS (N FOR ORDINALITY, "sku" VARCHAR(5), QTY INTEGER PATH 'lax $.qty',
                  ITEM VARCHAR(30) FORMAT JSON PATH 'lax $')) AS JT
  ORDER BY P.ID, N;
           ID |            N | sku   |          QTY | ITEM
--------------+--------------+-------+--------------+--------------------------------
            1 |            1 | A1    |            2 | {"sku":"A1","qty":2}
            1 |            2 | B7    |            1 | {"sku":"B7","qty":1}
(2 rows)

PURCHASES 2 has no items, so it has no rows. Written as the second operand of a LEFT join, a JSON table keeps such a row, its columns null:

SELECT P.ID, JT.SKU
  FROM PURCHASES P LEFT JOIN
       JSON_TABLE(P.DOC, 'lax $.items[*]' COLUMNS (SKU VARCHAR(5) PATH 'lax $.sku')) AS JT ON TRUE
  ORDER BY P.ID;
           ID | SKU
--------------+-------
            1 | A1
            1 | B7
            2 | NULL
(3 rows)

ON EMPTY and ON ERROR

A column's behavior ON EMPTY says what its value is when its path finds nothing, and ON ERROR what it is in place of a data exception, an SQLSTATE of class 22, as of a cast that fails or of two items found. A regular column takes ERROR, NULL or DEFAULT and a value expression, which may read the columns of the tables before the JSON table and is evaluated only when it is the value. A formatted column takes ERROR, NULL, EMPTY ARRAY or EMPTY OBJECT. Without one, ON EMPTY is NULL, and ON ERROR is NULL, or ERROR if the table says ERROR ON ERROR.

The table's own ERROR ON ERROR or EMPTY ON ERROR, EMPTY if not written, says what a data exception of a row pattern does, as of text that is no JSON text: EMPTY makes that pattern's table empty, ERROR raises it.

SELECT P.ID, JT.*
  FROM PURCHASES P,
       JSON_TABLE(P.DOC, 'lax $'
         COLUMNS (BUYER VARCHAR(10) PATH 'lax $.buyer',
                  NOTE VARCHAR(10) PATH 'lax $.note' DEFAULT 'none' ON EMPTY,
                  FIRST_QTY INTEGER PATH 'lax $.items[0].qty' DEFAULT -P.ID ON EMPTY,
                  QTYS INTEGER PATH 'lax $.items[*].qty' NULL ON ERROR)) AS JT
  ORDER BY P.ID;
SELECT * FROM JSON_TABLE('{"a":', 'lax $' COLUMNS (A INTEGER)) AS JT;
SELECT * FROM JSON_TABLE('{"a":', 'lax $' COLUMNS (A INTEGER) ERROR ON ERROR) AS JT;
           ID | BUYER      | NOTE       |    FIRST_QTY |         QTYS
--------------+------------+------------+--------------+--------------
            1 | Ada        | none       |            2 |         NULL
            2 | Grace      | NULL       |           -2 |         NULL
(2 rows)
            A
--------------
(0 rows)
            A
--------------
turborel: SQLCODE -301 (SQLSTATE 22032): the string is not a JSON text: a value is missing (at character 6) [ISO/IEC 9075-2:2023 §9.42 GR3a iii 1]

Ada's document has no member note, so its NOTE is the DEFAULT, where Grace's has the JSON null, which is NULL. Grace has no first item, so FIRST_QTY is the DEFAULT, of her row's ID. QTYS finds two numbers in Ada's items, an error ON ERROR makes NULL.

Nested columns and plans

NESTED PATH gives the rows of its own path of each row of the columns it is nested in, and nests to any depth. Its rows are joined to their parent's row: by default each parent's row is kept, null extended if it has none (OUTER), and the rows of siblings, two NESTED clauses side by side, follow one another, each with the other's columns null (UNION).

PLAN DEFAULT (INNER) drops a parent's row of no nested rows, and PLAN DEFAULT (CROSS) joins each row of a sibling with each of the other's. PLAN ( ... ) says how each pair is joined, every path named once: then the row pattern and each NESTED clause are named by AS. A plan joins a path by OUTER or INNER to the path its columns are nested in, so PLAN (R OUTER (I UNION T)) is the default here.

SELECT JT.*
  FROM PURCHASES P,
       JSON_TABLE(P.DOC, 'lax $' AS R
         COLUMNS (BUYER VARCHAR(10) PATH 'lax $.buyer',
                  NESTED PATH 'lax $.items[*]' AS I COLUMNS (SKU VARCHAR(5) PATH 'lax $.sku'),
                  NESTED PATH 'lax $.tags[*]' AS T COLUMNS (TAG VARCHAR(10) PATH 'lax $'))) AS JT
  ORDER BY BUYER, SKU;
SELECT JT.*
  FROM JSON_TABLE('{"buyer":"Ada","items":[{"sku":"A1"},{"sku":"B7"}],"tags":["new","gift"]}', 'lax $' AS R
         COLUMNS (BUYER VARCHAR(10) PATH 'lax $.buyer',
                  NESTED PATH 'lax $.items[*]' AS I COLUMNS (SKU VARCHAR(5) PATH 'lax $.sku'),
                  NESTED PATH 'lax $.tags[*]' AS T COLUMNS (TAG VARCHAR(10) PATH 'lax $'))
         PLAN (R INNER (I CROSS T))) AS JT;
 BUYER      | SKU   | TAG
------------+-------+------------
 Ada        | A1    | NULL
 Ada        | B7    | NULL
 Grace      | NULL  | NULL
(3 rows)
 BUYER      | SKU   | TAG
------------+-------+------------
 Ada        | A1    | new
 Ada        | A1    | gift
 Ada        | B7    | new
 Ada        | B7    | gift
(4 rows)

PASSING

The PASSING arguments are variables of the row pattern and of the nested paths, $ and the name, a regular identifier's in upper case. A column's path names none.

SELECT P.ID, JT.SKU
  FROM PURCHASES P,
       JSON_TABLE(P.DOC, 'lax $.items[*] ? (@.qty >= $LEAST)' PASSING 2 AS LEAST
         COLUMNS (SKU VARCHAR(5) PATH 'lax $.sku')) AS JT;
           ID | SKU
--------------+-------
            1 | A1
(1 row)

EXPLAIN

EXPLAIN (extension) shows a JSON table by its row pattern, the number of its columns, and its plan, the default one spelled out, a path of no name by its text.

EXPLAIN SELECT JT.* FROM PURCHASES P,
  JSON_TABLE(P.DOC, 'lax $' AS R COLUMNS (B VARCHAR(10) PATH 'lax $.buyer',
    NESTED 'lax $.items[*]' COLUMNS (S VARCHAR(5) PATH 'lax $.sku'))) AS JT;
 PLAN
--------------------------------------------------------------
 -- bound:
 Project JT.B, JT.S
   JSON_TABLE 'lax $' of 2 columns PLAN (R OUTER ('lax $.items[*]')) of P.DOC
     Scan ANN.PURCHASES
 -- rules: none
 -- optimized:
 Project JT.B, JT.S
   JSON_TABLE 'lax $' of 2 columns PLAN (R OUTER ('lax $.items[*]')) of P.DOC
     Scan ANN.PURCHASES
 -- physical:
 Project JT.B, JT.S
   JSON_TABLE 'lax $' of 2 columns PLAN (R OUTER ('lax $.items[*]'))
     SeqScan ANN.PURCHASES  (rows=2, cost=1.8)
(13 rows)

Errors

SQLSTATE SQLCODE Cause
22032 -301 ERROR ON ERROR, of a context item that is no JSON text, or of a parent's item of a NESTED clause that has none, a datetime
2203A and the path language's other SQLSTATEs -301 ERROR ON ERROR, of a row pattern in strict mode that finds no member or element
22034, 22035, 2203F, 2203G, 22018 -301 of a column ERROR ON EMPTY or ERROR ON ERROR, as of JSON_VALUE
22001 -301 of a formatted column whose text is longer than its type
2A000 -103 a Syntax Rule broken: a column of a type it does not take, a formatted column of a character string type without FORMAT JSON, a quotes behavior with a wrapper WITH, a column path that names a variable, a path name or column name written twice, a PLAN that names a path not once or joins it to a path it is not nested in, NESTED COLUMNS of no name with a PLAN, or JSON_TABLE in a CHECK constraint
2A000 -102 DEFAULT as a formatted column's behavior

Standard

JSON_TABLE is ISO/IEC 9075-2:2023 §7.11, Features T821, T824 (PLAN), T825 and T826 (ON EMPTY and ON ERROR), T827 (sibling NESTED COLUMNS) and T838 (PLAN DEFAULT), and T823 (PASSING). A path name not written is named by its row pattern in EXPLAIN alone.