JSON_TUPLE

​

Syntax

​

Table

JSON_TUPLE(json, path1 [, path2 ...])

​

Arguments

​

Returns

• Type: TABLE of STRING columns
• NULL behavior: NULL/invalid JSON, JSON null and missing paths produce NULL output values; the row generator still emits one output row per input row.
• Empty input: The function has no collection-row suppression; an empty or invalid JSON text yields NULL columns.
• Shape: Exactly one output STRING column per requested path and one generated row per input row.
• Ordering: One row is associated with each input row; SQL output order requires ORDER BY.

​

Properties

​

Usage notes

• Nested paths and array wildcards are supported. Dot-name fields are matched without regard to case; quoted names in brackets are case-sensitive.
• Scalar strings are unquoted; objects/arrays are JSON text.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT first_value, ids, missing_value FROM TABLE(json_tuple('{"Store":{"items":[{"id":10},{"id":20}]}}', 'store.items[1].id', 'store.items[*].id', 'missing')) AS t(first_value, ids, missing_value);

​

Read text and boolean values

SELECT name, active FROM TABLE(json_tuple('{"name":"Ada","active":true}', 'name', 'active')) AS t(name, active);

​

Related functions

• json_extract_scalar
• get_json_object