JSON functions - Firebolt Documentation

Preview BigQuery JSON functions in BigQuery compatibility mode. See also the Firebolt JSON type.

Function	Support	Notes
`JSON_VALUE`	Supported	Extracts a scalar as text.
`JSON_EXTRACT_SCALAR`	Supported	Alias of `JSON_VALUE`.
`JSON_QUERY`	Supported	Extracts a JSON subtree.
`JSON_EXTRACT`	Supported	Alias of `JSON_QUERY`.
`JSON_QUERY_ARRAY`	Supported
`JSON_EXTRACT_ARRAY`	Supported
`JSON_VALUE_ARRAY`	Supported
`JSON_EXTRACT_STRING_ARRAY`	Supported
`JSON_KEYS`	Supported
`JSON_OBJECT`	Supported
`TO_JSON`	Supported
`TO_JSON_STRING`	Supported
`BOOL`	Supported	Typed accessor on a `JSON` value.
`INT64`	Supported	Typed accessor on a `JSON` value.
`FLOAT64`	Supported	Typed accessor on a `JSON` value.
`STRING`	Supported	Typed accessor on a `JSON` value.
`LAX_BOOL`, `LAX_INT64`, `LAX_FLOAT64`, `LAX_STRING`	Supported	Lenient typed accessors.
`JSON_ARRAY`, `JSON_ARRAY_APPEND`, `JSON_ARRAY_INSERT`, `JSON_FLATTEN`, `JSON_REMOVE`, `JSON_SET`, `JSON_STRIP_NULLS`, `JSON_TYPE`, `PARSE_JSON`	Not supported	JSON construction and mutation functions.

The typed integer accessors cover INT64 and INT32. UINT32 and UINT64 accessors are not supported, because Firebolt has no unsigned type.

Field access operator

The -> operator reads a field from a JSON value:

SELECT json_col -> 'user' -> 'id' FROM events;

JSON path support

Paths support the root $, dot access (.key), bracket access (['key'] or ["key"]), and array index ([n]). Wildcards ([*], .*), recursive descent (..), array slices, and filter expressions are not supported.

⌘I