spiceai/docs

spiceai/

docs

Help Login

evgenii/docs-spicepod-v2

Edit on GitHub

Fork

/docs/website/versioned_docs/version-2.0.x/reference/sql/json.md

spiceai/docs | Spice Cloud Platform

evgenii/docs-spicepod-v2

Edit on GitHub

Fork

/docs/website/versioned_docs/version-2.0.x/reference/sql/json.md

spiceai/docs/README.md

title: 'JSON Functions and Operators' sidebar_label: 'JSON' description: 'Reference for JSON functions and operators in Spice SQL' sidebar_position: 7

JSON support in Spice is based on datafusion-functions-json, which provides functions and operators to extract, query, and manipulate JSON data stored as strings. Advanced features for JSON creation, modification, or complex path expressions are not supported.

:::warning[Limitations]

JSON functions and operators are supported only during DataFusion (Arrow) execution.
Federated or accelerated sources (non-Arrow) may not support all JSON functions.

:::

JSON Functions
- json_contains
  - Arguments
  - Example
- json_get
  - Arguments
  - Example
- json_get_str
  - Arguments
  - Example
- json_get_int
  - Arguments
  - Example
- json_get_float
  - Arguments
  - Example
- json_get_bool
  - Arguments
  - Example
- json_get_json
  - Arguments
  - Example
- json_get_array
  - Arguments
  - Example
- json_as_text
  - Arguments
  - Example
- json_length
  - Arguments
  - Example
- json_object_keys
  - Arguments
  - Example
JSON Operators
- ->
  - Arguments
  - Example
- ->>
  - Arguments
  - Example
- ?
  - Arguments
  - Example
Usage Examples
Further Reading

JSON Functions

Enables extracting and manipulating data from JSON strings. Each function takes a JSON string as the first argument, followed by one or more keys or indices to specify the path.

`json_contains`

Returns true if a JSON string contains a specific key at the specified path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the key to check. Can be string keys for objects or integer indices for arrays.

Example

`json_get`

Retrieves a value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the value. Can be string keys for objects or integer indices for arrays.

Example

`json_get_str`

Retrieves a string value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the string value.

Example

`json_get_int`

Retrieves an integer value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the integer value.

Example

`json_get_float`

Retrieves a float value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the float value.

Example

`json_get_bool`

Retrieves a boolean value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the boolean value.

Example

`json_get_json`

Retrieves a nested JSON object or array as a raw JSON string from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the nested JSON value.

Example

`json_get_array`

Retrieves an arrow array from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the array value.

Example

`json_as_text`

Retrieves any value from a JSON string based on its path and represents it as a string. This is useful for converting JSON values to text format.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the value to convert to text.

Example

`json_length`

Returns the length of a JSON string, array, or object. For objects, returns the number of key-value pairs. For arrays, returns the number of elements. For strings, returns the character count.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Optional path to a nested value. If omitted, returns the length of the root JSON value.

Example

`json_object_keys`

Returns the top-level keys of a JSON object as an array of strings. If a path is provided, returns the keys of the object at that path. Returns NULL if the value at the path is not an object.

Alias: json_keys.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Optional path to a nested object. If omitted, returns the keys of the root object.

Example

JSON Operators

`->` {#op_json_get}

JSON access operator. Retrieves a value from a JSON string based on its path. This operator is an alias for json_get.

Arguments

json_string: String containing valid JSON data.
key: Object key (string) or array index (integer).

Example

`->>` {#op_json_as_text}

JSON access operator for text extraction. Retrieves any value from a JSON string and converts it to text. This operator is an alias for json_as_text.

Arguments

json_string: String containing valid JSON data.
key: Object key (string) or array index (integer).

Example

`?` {#op_json_contains}

JSON containment operator. Returns true if a JSON string contains the specified key. This operator is an alias for json_contains.

Arguments

json_string: String containing valid JSON data.
key: Key to check for existence.

Example

Usage Examples

Nested Object Access

Array Access

Conditional JSON Queries

Using JSON Functions in Views

JSON functions can be used in views to simplify access to nested JSON data:

JSON Table Functions (UDTFs)

Spice includes table-valued functions for decomposing JSON structures into relational rows. Each function is available as both a UDTF (in the FROM clause with literal input) and a scalar UDF returning a list of structs (for per-row use with UNNEST).

`flatten_json`

Walks an arbitrary JSON value and emits one row per reachable leaf.

Options (named arguments):

Option	Type	Default	Description
`max_depth`	UInt	`64`	Maximum recursion depth.
`max_rows`	UInt	`1000000`	Per-document row cap.
`max_bytes`	UInt	`8388608`	Input size limit (bytes).
`path_style`	Utf8	`"dot"`	`"dot"` or `"json-pointer"`.
`include_internal`	Bool	`false`	Also emit interior object/array rows.
`array_wildcard`	Bool	`false`	Collapse array indices to `[*]` instead of , , etc.

UDTF example:

path	value	type
`user.name`	`Alice`	`string`
`user.scores[0]`	`95`	`integer`
`user.scores[1]`	`87`	`integer`

Scalar UDF example (per-row with UNNEST):

`flatten_json_properties`

Decomposes a JSON Schema document into one row per field, extracting metadata such as types, descriptions, required status, enums, and format.

Handles properties recursion, items.properties (arrays of objects), additionalProperties maps, allOf/oneOf/anyOf merging, and local $ref pointers with cycle detection.

Options (named arguments):

Option	Type	Default	Description
`max_depth`	UInt	`32`	Maximum recursion depth.
`max_rows`	UInt	`100000`	Per-document row cap.
`max_bytes`	UInt	`8388608`	Input size limit (bytes).
`path_style`	Utf8	`"dot"`	`"dot"` or `"json-pointer"`.
`dialect`	Utf8	`"json-schema"`	`"json-schema"` or `"openapi"` (metrics tagging).
`include_internal`	Bool	`false`

Example:

path	type	required	description
`name`	`string`	`true`	`User name`
`age`	`integer`	`false`

Expanding maps:

When a JSON Schema uses additionalProperties to describe map values, enable expand_maps to produce JSONPath-style paths:

path	type
`[*].id`	`string`
`[*].primary`	`boolean`

`json_tree`

Recursive depth-first walk of an arbitrary JSON document. Schema-agnostic sibling of flatten_json_properties that mirrors the json_tree table function in DuckDB and SQLite: one row per node (interior and leaf) in depth-first order, with JSON-Path addresses and a parent pointer for reconstructing the tree.

Options (named arguments, UDTF form only):

Option	Type	Default	Description
`max_depth`	UInt	`64`	Maximum recursion depth.
`max_rows`	UInt	`1000000`	Per-document row cap.
`max_bytes`	UInt	`8388608`	Input size limit (bytes).

UDTF example:

id	parent	fullkey	type	atom
`0`		`$`	`object`
`1`	`0`	`$.user`	`object`
`2`	`1`	`$.user.name`	`string`	`Alice`
`3`	`1`	`$.user.scores`	`array`
`4`

Scalar UDF example (per-row with UNNEST):

The scalar form takes only the JSON argument and always runs with default caps; the named options above are only accepted in the UDTF (FROM clause) form.

title: 'JSON Functions and Operators' sidebar_label: 'JSON' description: 'Reference for JSON functions and operators in Spice SQL' sidebar_position: 7

:::warning[Limitations]

JSON functions and operators are supported only during DataFusion (Arrow) execution.
Federated or accelerated sources (non-Arrow) may not support all JSON functions.

:::

JSON Functions
- json_contains
  - Arguments
  - Example
- json_get
  - Arguments
  - Example
- json_get_str
  - Arguments
  - Example
- json_get_int
  - Arguments
  - Example
- json_get_float
  - Arguments
  - Example
- json_get_bool
  - Arguments
  - Example
- json_get_json
  - Arguments
  - Example
- json_get_array
  - Arguments
  - Example
- json_as_text
  - Arguments
  - Example
- json_length
  - Arguments
  - Example
- json_object_keys
  - Arguments
  - Example
JSON Operators
- ->
  - Arguments
  - Example
- ->>
  - Arguments
  - Example
- ?
  - Arguments
  - Example
Usage Examples
Further Reading

JSON Functions

Enables extracting and manipulating data from JSON strings. Each function takes a JSON string as the first argument, followed by one or more keys or indices to specify the path.

`json_contains`

Returns true if a JSON string contains a specific key at the specified path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the key to check. Can be string keys for objects or integer indices for arrays.

Example

`json_get`

Retrieves a value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the value. Can be string keys for objects or integer indices for arrays.

Example

`json_get_str`

Retrieves a string value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the string value.

Example

`json_get_int`

Retrieves an integer value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the integer value.

Example

`json_get_float`

Retrieves a float value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the float value.

Example

`json_get_bool`

Retrieves a boolean value from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the boolean value.

Example

`json_get_json`

Retrieves a nested JSON object or array as a raw JSON string from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the nested JSON value.

Example

`json_get_array`

Retrieves an arrow array from a JSON string based on its path.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the array value.

Example

`json_as_text`

Retrieves any value from a JSON string based on its path and represents it as a string. This is useful for converting JSON values to text format.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Path to the value to convert to text.

Example

`json_length`

Returns the length of a JSON string, array, or object. For objects, returns the number of key-value pairs. For arrays, returns the number of elements. For strings, returns the character count.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Optional path to a nested value. If omitted, returns the length of the root JSON value.

Example

`json_object_keys`

Returns the top-level keys of a JSON object as an array of strings. If a path is provided, returns the keys of the object at that path. Returns NULL if the value at the path is not an object.

Alias: json_keys.

Arguments

json_string: String containing valid JSON data.
key1, key2, ...: Optional path to a nested object. If omitted, returns the keys of the root object.

Example

JSON Operators

`->` {#op_json_get}

JSON access operator. Retrieves a value from a JSON string based on its path. This operator is an alias for json_get.

Arguments

json_string: String containing valid JSON data.
key: Object key (string) or array index (integer).

Example

`->>` {#op_json_as_text}

JSON access operator for text extraction. Retrieves any value from a JSON string and converts it to text. This operator is an alias for json_as_text.

Arguments

json_string: String containing valid JSON data.
key: Object key (string) or array index (integer).

Example

`?` {#op_json_contains}

JSON containment operator. Returns true if a JSON string contains the specified key. This operator is an alias for json_contains.

Arguments

json_string: String containing valid JSON data.
key: Key to check for existence.

Example

Usage Examples

Nested Object Access

Array Access

Conditional JSON Queries

Using JSON Functions in Views

JSON functions can be used in views to simplify access to nested JSON data:

JSON Table Functions (UDTFs)

`flatten_json`

Walks an arbitrary JSON value and emits one row per reachable leaf.

Options (named arguments):

Option	Type	Default	Description
`max_depth`	UInt	`64`	Maximum recursion depth.
`max_rows`	UInt	`1000000`	Per-document row cap.
`max_bytes`	UInt	`8388608`	Input size limit (bytes).
`path_style`	Utf8	`"dot"`	`"dot"` or `"json-pointer"`.
`include_internal`	Bool	`false`	Also emit interior object/array rows.
`array_wildcard`	Bool	`false`	Collapse array indices to `[*]` instead of , , etc.

UDTF example:

path	value	type
`user.name`	`Alice`	`string`
`user.scores[0]`	`95`	`integer`
`user.scores[1]`	`87`	`integer`

Scalar UDF example (per-row with UNNEST):

`flatten_json_properties`

Decomposes a JSON Schema document into one row per field, extracting metadata such as types, descriptions, required status, enums, and format.

Handles properties recursion, items.properties (arrays of objects), additionalProperties maps, allOf/oneOf/anyOf merging, and local $ref pointers with cycle detection.

Options (named arguments):

Option	Type	Default	Description
`max_depth`	UInt	`32`	Maximum recursion depth.
`max_rows`	UInt	`100000`	Per-document row cap.
`max_bytes`	UInt	`8388608`	Input size limit (bytes).
`path_style`	Utf8	`"dot"`	`"dot"` or `"json-pointer"`.
`dialect`	Utf8	`"json-schema"`	`"json-schema"` or `"openapi"` (metrics tagging).
`include_internal`	Bool	`false`

Example:

path	type	required	description
`name`	`string`	`true`	`User name`
`age`	`integer`	`false`

Expanding maps:

When a JSON Schema uses additionalProperties to describe map values, enable expand_maps to produce JSONPath-style paths:

path	type
`[*].id`	`string`
`[*].primary`	`boolean`

`json_tree`

Options (named arguments, UDTF form only):

Option	Type	Default	Description
`max_depth`	UInt	`64`	Maximum recursion depth.
`max_rows`	UInt	`1000000`	Per-document row cap.
`max_bytes`	UInt	`8388608`	Input size limit (bytes).

UDTF example:

id	parent	fullkey	type	atom
`0`		`$`	`object`
`1`	`0`	`$.user`	`object`
`2`	`1`	`$.user.name`	`string`	`Alice`
`3`	`1`	`$.user.scores`	`array`
`4`

Scalar UDF example (per-row with UNNEST):

The scalar form takes only the JSON argument and always runs with default caps; the named options above are only accepted in the UDTF (FROM clause) form.

> SELECT json_contains('{"a": 1, "b": 2}', 'a');
+-------------------------------------------------+
| json_contains(Utf8("{\"a\": 1, \"b\": 2}"),Utf8("a")) |
+-------------------------------------------------+
| true                                            |
+-------------------------------------------------+

> SELECT json_contains('{"a": 1, "b": 2}', 'a');
+-------------------------------------------------+
| json_contains(Utf8("{\"a\": 1, \"b\": 2}"),Utf8("a")) |
+-------------------------------------------------+
| true                                            |
+-------------------------------------------------+

json_get(json_string, key1[, key2, ...])

json_get(json_string, key1[, key2, ...])

> SELECT json_get('{"a": 1, "b": 2}', 'a');
+----------------------------------------------+
| json_get(Utf8("{"a": 1, "b": 2}"),Utf8("a")) |
+----------------------------------------------+
| {int=1}                                      |
+----------------------------------------------+

> SELECT json_get('{"a": 1, "b": 2}', 'a');
+----------------------------------------------+
| json_get(Utf8("{"a": 1, "b": 2}"),Utf8("a")) |
+----------------------------------------------+
| {int=1}                                      |
+----------------------------------------------+

json_get_str(json_string, key1[, key2, ...])

json_get_str(json_string, key1[, key2, ...])

> SELECT json_get_str('{"name": "John", "age": 30}', 'name');
+----------------------------------------------------------------+
| json_get_str(Utf8("{"name": "John", "age": 30}"),Utf8("name")) |
+----------------------------------------------------------------+
| John                                                           |
+----------------------------------------------------------------+

> SELECT json_get_str('{"name": "John", "age": 30}', 'name');
+----------------------------------------------------------------+
| json_get_str(Utf8("{"name": "John", "age": 30}"),Utf8("name")) |
+----------------------------------------------------------------+
| John                                                           |
+----------------------------------------------------------------+

json_get_int(json_string, key1[, key2, ...])

json_get_int(json_string, key1[, key2, ...])

> SELECT json_get_int('{"name": "John", "age": 30}', 'age');
+---------------------------------------------------------------+
| json_get_int(Utf8("{"name": "John", "age": 30}"),Utf8("age")) |
+---------------------------------------------------------------+
| 30                                                            |
+---------------------------------------------------------------+

> SELECT json_get_int('{"name": "John", "age": 30}', 'age');
+---------------------------------------------------------------+
| json_get_int(Utf8("{"name": "John", "age": 30}"),Utf8("age")) |
+---------------------------------------------------------------+
| 30                                                            |
+---------------------------------------------------------------+

json_get_float(json_string, key1[, key2, ...])

json_get_float(json_string, key1[, key2, ...])

> SELECT json_get_float('{"price": 19.99, "quantity": 2}', 'price');
+-----------------------------------------------------------------------+
| json_get_float(Utf8("{"price": 19.99, "quantity": 2}"),Utf8("price")) |
+-----------------------------------------------------------------------+
| 19.99                                                                 |
+-----------------------------------------------------------------------+

> SELECT json_get_float('{"price": 19.99, "quantity": 2}', 'price');
+-----------------------------------------------------------------------+
| json_get_float(Utf8("{"price": 19.99, "quantity": 2}"),Utf8("price")) |
+-----------------------------------------------------------------------+
| 19.99                                                                 |
+-----------------------------------------------------------------------+

json_get_bool(json_string, key1[, key2, ...])

json_get_bool(json_string, key1[, key2, ...])

> SELECT json_get_bool('{"active": true, "visible": false}', 'active');
+--------------------------------------------------------------------------+
| json_get_bool(Utf8("{"active": true, "visible": false}"),Utf8("active")) |
+--------------------------------------------------------------------------+
| true                                                                     |
+--------------------------------------------------------------------------+

> SELECT json_get_bool('{"active": true, "visible": false}', 'active');
+--------------------------------------------------------------------------+
| json_get_bool(Utf8("{"active": true, "visible": false}"),Utf8("active")) |
+--------------------------------------------------------------------------+
| true                                                                     |
+--------------------------------------------------------------------------+

json_get_json(json_string, key1[, key2, ...])

json_get_json(json_string, key1[, key2, ...])

> SELECT json_get_json('{"user": {"name": "John", "age": 30}}', 'user');
+---------------------------------------------------------------------------+
| json_get_json(Utf8("{"user": {"name": "John", "age": 30}}"),Utf8("user")) |
+---------------------------------------------------------------------------+
| {"name": "John", "age": 30}                                               |
+---------------------------------------------------------------------------+

> SELECT json_get_json('{"user": {"name": "John", "age": 30}}', 'user');
+---------------------------------------------------------------------------+
| json_get_json(Utf8("{"user": {"name": "John", "age": 30}}"),Utf8("user")) |
+---------------------------------------------------------------------------+
| {"name": "John", "age": 30}                                               |
+---------------------------------------------------------------------------+

json_get_array(json_string, key1[, key2, ...])

json_get_array(json_string, key1[, key2, ...])

> SELECT json_get_array('{"numbers": [1, 2, 3, 4]}', 'numbers');
+-------------------------------------------------------------------+
| json_get_array(Utf8("{"numbers": [1, 2, 3, 4]}"),Utf8("numbers")) |
+-------------------------------------------------------------------+
| [1, 2, 3, 4]                                                      |
+-------------------------------------------------------------------+

> SELECT json_get_array('{"numbers": [1, 2, 3, 4]}', 'numbers');
+-------------------------------------------------------------------+
| json_get_array(Utf8("{"numbers": [1, 2, 3, 4]}"),Utf8("numbers")) |
+-------------------------------------------------------------------+
| [1, 2, 3, 4]                                                      |
+-------------------------------------------------------------------+

json_as_text(json_string, key1[, key2, ...])

json_as_text(json_string, key1[, key2, ...])

> SELECT json_as_text('{"age": 30, "active": true}', 'age');
+---------------------------------------------------------------+
| json_as_text(Utf8("{"age": 30, "active": true}"),Utf8("age")) |
+---------------------------------------------------------------+
| 30                                                            |
+---------------------------------------------------------------+

> SELECT json_as_text('{"age": 30, "active": true}', 'age');
+---------------------------------------------------------------+
| json_as_text(Utf8("{"age": 30, "active": true}"),Utf8("age")) |
+---------------------------------------------------------------+
| 30                                                            |
+---------------------------------------------------------------+

json_length(json_string[, key1, key2, ...])

json_length(json_string[, key1, key2, ...])

> SELECT json_length('{"a": 1, "b": 2, "c": 3}');
+-----------------------------------------------+
| json_length(Utf8("{"a": 1, "b": 2, "c": 3}")) |
+-----------------------------------------------+
| 3                                             |
+-----------------------------------------------+

> SELECT json_length('[1, 2, 3, 4, 5]');
+--------------------------------------+
| json_length(Utf8("[1, 2, 3, 4, 5]")) |
+--------------------------------------+
| 5                                    |
+--------------------------------------+

> SELECT json_length('{"a": 1, "b": 2, "c": 3}');
+-----------------------------------------------+
| json_length(Utf8("{"a": 1, "b": 2, "c": 3}")) |
+-----------------------------------------------+
| 3                                             |
+-----------------------------------------------+

> SELECT json_length('[1, 2, 3, 4, 5]');
+--------------------------------------+
| json_length(Utf8("[1, 2, 3, 4, 5]")) |
+--------------------------------------+
| 5                                    |
+--------------------------------------+

json_object_keys(json_string[, key1, key2, ...])

json_object_keys(json_string[, key1, key2, ...])

> SELECT json_object_keys('{"a": 1, "b": 2, "c": 3}');
+-----------------------------------------------------+
| json_object_keys(Utf8("{"a": 1, "b": 2, "c": 3}"))  |
+-----------------------------------------------------+
| [a, b, c]                                           |
+-----------------------------------------------------+

> SELECT json_object_keys('{"user": {"name": "John", "age": 30}}', 'user');
+-----------------------------------------------------------------------------+
| json_object_keys(Utf8("{"user": {"name": "John", "age": 30}}"),Utf8("user")) |
+-----------------------------------------------------------------------------+
| [name, age]                                                                 |
+-----------------------------------------------------------------------------+

> SELECT json_object_keys('{"a": 1, "b": 2, "c": 3}');
+-----------------------------------------------------+
| json_object_keys(Utf8("{"a": 1, "b": 2, "c": 3}"))  |
+-----------------------------------------------------+
| [a, b, c]                                           |
+-----------------------------------------------------+

> SELECT json_object_keys('{"user": {"name": "John", "age": 30}}', 'user');
+-----------------------------------------------------------------------------+
| json_object_keys(Utf8("{"user": {"name": "John", "age": 30}}"),Utf8("user")) |
+-----------------------------------------------------------------------------+
| [name, age]                                                                 |
+-----------------------------------------------------------------------------+

json_string -> key
json_string -> key1 -> key2

json_string -> key
json_string -> key1 -> key2

> SELECT '{"user": {"name": "John", "age": 30}}' -> 'user' -> 'name';
+-------------------------------------------------------------+
| '{"user": {"name": "John", "age": 30}}' -> 'user' -> 'name' |
+-------------------------------------------------------------+
| {str=John}                                                  |
+-------------------------------------------------------------+

> SELECT '{"user": {"name": "John", "age": 30}}' -> 'user' -> 'name';
+-------------------------------------------------------------+
| '{"user": {"name": "John", "age": 30}}' -> 'user' -> 'name' |
+-------------------------------------------------------------+
| {str=John}                                                  |
+-------------------------------------------------------------+

json_string ->> key
json_string -> key1 ->> key2

json_string ->> key
json_string -> key1 ->> key2

> SELECT '{"user": {"name": "John", "age": 30}}' -> 'user' ->> 'age';
+-------------------------------------------------------------+
| '{"user": {"name": "John", "age": 30}}' -> 'user' ->> 'age' |
+-------------------------------------------------------------+
| 30                                                          |
+-------------------------------------------------------------+

> SELECT '{"user": {"name": "John", "age": 30}}' -> 'user' ->> 'age';
+-------------------------------------------------------------+
| '{"user": {"name": "John", "age": 30}}' -> 'user' ->> 'age' |
+-------------------------------------------------------------+
| 30                                                          |
+-------------------------------------------------------------+

json_string ? 'key'

json_string ? 'key'

> SELECT '{"user": {"name": "John", "age": 30}}' ? 'user';
+--------------------------------------------------+
| '{"user": {"name": "John", "age": 30}}' ? 'user' |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+

> SELECT '{"user": {"name": "John", "age": 30}}' ? 'user';
+--------------------------------------------------+
| '{"user": {"name": "John", "age": 30}}' ? 'user' |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+

> SELECT '{"inventory": {"stock": {"S": 12, "M": 20}}}' -> 'inventory' -> 'stock' ->> 'S' as size_s_stock;
+---------------+
| size_s_stock  |
+---------------+
| 12            |
+---------------+

> SELECT '{"inventory": {"stock": {"S": 12, "M": 20}}}' -> 'inventory' -> 'stock' ->> 'S' as size_s_stock;
+---------------+
| size_s_stock  |
+---------------+
| 12            |
+---------------+

> SELECT '{"sizes": ["S", "M", "L", "XL"]}' -> 'sizes' ->> 0 as first_size;
+------------+
| first_size |
+------------+
| S          |
+------------+

> SELECT '{"sizes": ["S", "M", "L", "XL"]}' -> 'sizes' ->> 0 as first_size;
+------------+
| first_size |
+------------+
| S          |
+------------+

> SELECT name, properties ->> 'color' as color
  FROM products
  WHERE properties ? 'color' AND properties ->> 'color' IN ('black', 'white');

> SELECT name, properties ->> 'color' as color
  FROM products
  WHERE properties ? 'color' AND properties ->> 'color' IN ('black', 'white');

CREATE VIEW products_with_color AS
SELECT
  id,
  name,
  properties ->> 'color' as color,
  json_get_int(properties, 'stock') as stock_count
FROM products;

CREATE VIEW products_with_color AS
SELECT
  id,
  name,
  properties ->> 'color' as color,
  json_get_int(properties, 'stock') as stock_count
FROM products;

flatten_json(input Utf8 [, options...]) -> TABLE(
    path         Utf8,
    parent_path  Utf8,
    key          Utf8,
    value        Utf8,
    type         Utf8     -- "object"|"array"|"string"|"number"|"integer"|"boolean"|"null"
)

flatten_json(input Utf8 [, options...]) -> TABLE(
    path         Utf8,
    parent_path  Utf8,
    key          Utf8,
    value        Utf8,
    type         Utf8     -- "object"|"array"|"string"|"number"|"integer"|"boolean"|"null"
)

SELECT path, value, type
FROM flatten_json('{"user": {"name": "Alice", "scores": [95, 87]}}');

SELECT path, value, type
FROM flatten_json('{"user": {"name": "Alice", "scores": [95, 87]}}');

SELECT rows.path, rows.value, rows.type
FROM (SELECT UNNEST(flatten_json(body)) AS rows FROM documents);

SELECT rows.path, rows.value, rows.type
FROM (SELECT UNNEST(flatten_json(body)) AS rows FROM documents);

flatten_json_properties(input Utf8 [, options...]) -> TABLE(
    path         Utf8,
    parent_path  Utf8,
    name         Utf8,
    description  Utf8,
    type         Utf8,
    required     Boolean,
    format       Utf8,
    enum_values  List<Utf8>,
    metadata     Utf8
)

flatten_json_properties(input Utf8 [, options...]) -> TABLE(
    path         Utf8,
    parent_path  Utf8,
    name         Utf8,
    description  Utf8,
    type         Utf8,
    required     Boolean,
    format       Utf8,
    enum_values  List<Utf8>,
    metadata     Utf8
)

SELECT path, type, required, description
FROM flatten_json_properties('{
  "type": "object",
  "properties": {
    "name": {"type": "string", "description": "User name"},
    "age": {"type": "integer"}
  },
  "required": ["name"]
}');

SELECT path, type, required, description
FROM flatten_json_properties('{
  "type": "object",
  "properties": {
    "name": {"type": "string", "description": "User name"},
    "age": {"type": "integer"}
  },
  "required": ["name"]
}');

SELECT path, type
FROM flatten_json_properties(
  '{"type": "object", "additionalProperties": {"type": "object", "properties": {"id": {"type": "string"}, "primary": {"type": "boolean"}}}}',
  expand_maps => true
);

SELECT path, type
FROM flatten_json_properties(
  '{"type": "object", "additionalProperties": {"type": "object", "properties": {"id": {"type": "string"}, "primary": {"type": "boolean"}}}}',
  expand_maps => true
);

json_tree(input Utf8 [, options...]) -> TABLE(
    key       Utf8,    -- key under the parent (object field name) or array index; NULL for the root
    value     Utf8,    -- JSON-encoded value of the node
    type      Utf8,    -- "object"|"array"|"string"|"number"|"integer"|"boolean"|"null"
    atom      Utf8,    -- scalar value for primitive nodes; NULL for objects and arrays
    id        Int64,   -- depth-first row id (root = 0)
    parent    Int64,   -- id of the parent node; NULL at the root
    fullkey   Utf8,    -- absolute JSON-Path of this node, e.g. $.user.scores[0]
    path      Utf8     -- JSON-Path of the parent node; NULL at the root
)

json_tree(input Utf8 [, options...]) -> TABLE(
    key       Utf8,    -- key under the parent (object field name) or array index; NULL for the root
    value     Utf8,    -- JSON-encoded value of the node
    type      Utf8,    -- "object"|"array"|"string"|"number"|"integer"|"boolean"|"null"
    atom      Utf8,    -- scalar value for primitive nodes; NULL for objects and arrays
    id        Int64,   -- depth-first row id (root = 0)
    parent    Int64,   -- id of the parent node; NULL at the root
    fullkey   Utf8,    -- absolute JSON-Path of this node, e.g. $.user.scores[0]
    path      Utf8     -- JSON-Path of the parent node; NULL at the root
)

SELECT id, parent, fullkey, type, atom
FROM json_tree('{"user": {"name": "Alice", "scores": [95, 87]}}');

SELECT id, parent, fullkey, type, atom
FROM json_tree('{"user": {"name": "Alice", "scores": [95, 87]}}');

SELECT rows.fullkey, rows.atom
FROM (SELECT UNNEST(json_tree(body)) AS rows FROM documents);

SELECT rows.fullkey, rows.atom
FROM (SELECT UNNEST(json_tree(body)) AS rows FROM documents);

title: 'JSON Functions and Operators' sidebar_label: 'JSON' description: 'Reference for JSON functions and operators in Spice SQL' sidebar_position: 7

JSON Functions

json_contains

Arguments

Example

json_get

Arguments

Example

json_get_str

Arguments

Example

json_get_int

Arguments

Example

json_get_float

Arguments

Example

json_get_bool

Arguments

Example

json_get_json

Arguments

Example

json_get_array

Arguments

Example

json_as_text

Arguments

Example

json_length

Arguments

Example

json_object_keys

Arguments

Example

JSON Operators

-> {#op_json_get}

Arguments

Example

->> {#op_json_as_text}

Arguments

Example

? {#op_json_contains}

Arguments

Example

Usage Examples

Nested Object Access

Array Access

Conditional JSON Queries

Using JSON Functions in Views

JSON Table Functions (UDTFs)

flatten_json

flatten_json_properties

json_tree

Further Reading

title: 'JSON Functions and Operators' sidebar_label: 'JSON' description: 'Reference for JSON functions and operators in Spice SQL' sidebar_position: 7

JSON Functions

json_contains

Arguments

Example

json_get

Arguments

Example

json_get_str

Arguments

Example

json_get_int

Arguments

Example

json_get_float

Arguments

Example

json_get_bool

Arguments

Example

json_get_json

Arguments

Example

json_get_array

Arguments

`json_contains`

`json_get`

`json_get_str`

`json_get_int`

`json_get_float`

`json_get_bool`

`json_get_json`

`json_get_array`

`json_as_text`

`json_length`

`json_object_keys`

`->` {#op_json_get}

`->>` {#op_json_as_text}

`?` {#op_json_contains}

`flatten_json`

`flatten_json_properties`

`json_tree`

`json_contains`

`json_get`

`json_get_str`

`json_get_int`

`json_get_float`

`json_get_bool`

`json_get_json`

`json_get_array`

`json_as_text`

`json_length`

`json_object_keys`

`->` {#op_json_get}

`->>` {#op_json_as_text}

`?` {#op_json_contains}

`flatten_json`

`flatten_json_properties`

`json_tree`