spiceai/docs

spiceai/

docs

Help Login

trunk

Edit on GitHub

Fork

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

spiceai/docs | Spice Cloud Platform

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.8.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 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 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:

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 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 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:

> 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_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;

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 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

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

Example

json_as_text

Arguments

Example

json_length

Arguments

Example

`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`

`->` {#op_json_get}

`->>` {#op_json_as_text}

`?` {#op_json_contains}

`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`

`->` {#op_json_get}

`->>` {#op_json_as_text}

`?` {#op_json_contains}