Skip to main content

JSON Functions and Operators

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.

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.

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

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​

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

json_get​

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

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

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​

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

json_get_str​

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

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

Arguments​

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

Example​

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

json_get_int​

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

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

Arguments​

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

Example​

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

json_get_float​

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

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

Arguments​

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

Example​

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

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

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

Arguments​

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

Example​

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

json_get_json​

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

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

Arguments​

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

Example​

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

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

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

Arguments​

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

Example​

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

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.

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

Arguments​

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

Example​

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

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.

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

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​

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

->​

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

json_string -> key
json_string -> key1 -> key2

Arguments​

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

Example​

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

->>​

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.

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

Arguments​

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

Example​

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

?​

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

json_string ? 'key'

Arguments​

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

Example​

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

Usage Examples​

Nested Object Access​

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

Array Access​

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

Conditional JSON Queries​

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

Using JSON Functions in Views​

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

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

Further Reading​