JSON functions

There are two sets of functions to parse JSON:

  • simpleJSON* (visitParam*) which is made for parsing a limited subset of JSON extremely fast.
  • JSONExtract* which is made for parsing ordinary JSON.

simpleJSON (visitParam) functions

Tinybird has special functions for working with simplified JSON. All these JSON functions are based on strong assumptions about what the JSON can be. They try to do as little as possible to get the job done as quickly as possible.

The following assumptions are made:

  1. The field name (function argument) must be a constant.
  2. The field name is somehow canonically encoded in JSON. For example: simpleJSONHas('{"abc":"def"}', 'abc') = 1, but simpleJSONHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
  3. Fields are searched for on any nesting level, indiscriminately. If there are multiple matching fields, the first occurrence is used.
  4. The JSON does not have space characters outside of string literals.

simpleJSONHas

Checks whether there is a field named field_name. The result is UInt8.

Syntax

simpleJSONHas(json, field_name)

Alias: visitParamHas.

### Parameters

  • json: The JSON in which the field is searched for. String
  • field_name: The name of the field to search for. String literal

Returned value

  • Returns 1 if the field exists, 0 otherwise. UInt8.

simpleJSONExtractUInt

Parses UInt64 from the value of the field named field_name. If this is a string field, it tries to parse a number from the beginning of the string. If the field does not exist, or it exists but does not contain a number, it returns 0.

Syntax

simpleJSONExtractUInt(json, field_name)

Alias: visitParamExtractUInt.

### Parameters

  • json: The JSON in which the field is searched for. String
  • field_name: The name of the field to search for. String literal

Returned value

  • Returns the number parsed from the field if the field exists and contains a number, 0 otherwise. UInt64.

simpleJSONExtractInt

Parses Int64 from the value of the field named field_name. If this is a string field, it tries to parse a number from the beginning of the string. If the field does not exist, or it exists but does not contain a number, it returns 0.

Syntax

simpleJSONExtractInt(json, field_name)

Alias: visitParamExtractInt.

### Parameters

  • json: The JSON in which the field is searched for. String
  • field_name: The name of the field to search for. String literal

Returned value

  • Returns the number parsed from the field if the field exists and contains a number, 0 otherwise. Int64.

simpleJSONExtractFloat

Parses Float64 from the value of the field named field_name. If this is a string field, it tries to parse a number from the beginning of the string. If the field does not exist, or it exists but does not contain a number, it returns 0.

Syntax

simpleJSONExtractFloat(json, field_name)

Alias: visitParamExtractFloat.

### Parameters

  • json: The JSON in which the field is searched for. String
  • field_name: The name of the field to search for. String literal

Returned value

  • Returns the number parsed from the field if the field exists and contains a number, 0 otherwise. Float64.

simpleJSONExtractBool

Parses a true/false value from the value of the field named field_name. The result is UInt8.

Syntax

simpleJSONExtractBool(json, field_name)

Alias: visitParamExtractBool.

### Parameters

  • json: The JSON in which the field is searched for. String
  • field_name: The name of the field to search for. String literal

Returned value

It returns 1 if the value of the field is true, 0 otherwise. This means this function will return 0 including (and not only) in the following cases:

  • If the field doesn't exists.
  • If the field contains true as a string, e.g.: {"field":"true"}.
  • If the field contains 1 as a numerical value.

simpleJSONExtractRaw

Returns the value of the field named field_name as a String, including separators.

Syntax

simpleJSONExtractRaw(json, field_name)

Alias: visitParamExtractRaw.

### Parameters

  • json: The JSON in which the field is searched for. String
  • field_name: The name of the field to search for. String literal

Returned value

  • Returns the value of the field as a string, including separators if the field exists, or an empty string otherwise. String

simpleJSONExtractString

Parses String in double quotes from the value of the field named field_name.

Syntax

simpleJSONExtractString(json, field_name)

Alias: visitParamExtractString.

### Parameters

  • json: The JSON in which the field is searched for. String
  • field_name: The name of the field to search for. String literal

Returned value

  • Returns the unescaped value of a field as a string, including separators. An empty string is returned if the field doesn't contain a double quoted string, if unescaping fails or if the field doesn't exist. String.

Implementation details

There is currently no support for code points in the format \uXXXX\uYYYY that are not from the basic multilingual plane (they are converted to CESU-8 instead of UTF-8).

Before version 21.11 the order of arguments was wrong, i.e. JSON_EXISTS(path, json)

Returned value

  • Returns 1 if the value exists in the JSON document, otherwise 0.

Examples

SELECT JSON_EXISTS('{"hello":1}', '$.hello')
SELECT JSON_EXISTS('{"hello":{"world":1}}', '$.hello.world')
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[*]')
SELECT JSON_EXISTS('{"hello":["world"]}', '$.hello[0]')

JSON_QUERY

Parses a JSON and extract a value as a JSON array or JSON object. If the value does not exist, an empty string will be returned.

Syntax

JSON_QUERY(json, path)

### Parameters

  • json: A string with valid JSON. String.
  • path: A string representing the path. String.

Before version 21.11 the order of arguments was wrong, i.e. JSON_EXISTS(path, json)

Returned value

  • Returns the extracted value as a JSON array or JSON object. Otherwise it returns an empty string if the value does not exist. String.

Example

Query:

SELECT JSON_QUERY('{"hello":"world"}', '$.hello')
SELECT JSON_QUERY('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]')
SELECT JSON_QUERY('{"hello":2}', '$.hello')
SELECT toTypeName(JSON_QUERY('{"hello":2}', '$.hello'))

Result:

["world"]
[0, 1, 4, 0, -1, -4]
[2]
String

JSON_VALUE

Parses a JSON and extract a value as a JSON scalar. If the value does not exist, an empty string will be returned by default.

This function is controlled by the following settings:

  • by SET function_json_value_return_type_allow_nullable = true, NULL will be returned. If the value is complex type (such as: struct, array, map), an empty string will be returned by default.
  • by SET function_json_value_return_type_allow_complex = true, the complex value will be returned.

Syntax

JSON_VALUE(json, path)

### Parameters

  • json: A string with valid JSON. String.
  • path: A string representing the path. String.

Before version 21.11 the order of arguments was wrong, i.e. JSON_EXISTS(path, json)

Returned value

  • Returns the extracted value as a JSON scalar if it exists, otherwise an empty string is returned. String.

Example

Query:

SELECT JSON_VALUE('{"hello":"world"}', '$.hello')
SELECT JSON_VALUE('{"array":[[0, 1, 2, 3, 4, 5], [0, -1, -2, -3, -4, -5]]}', '$.array[*][0 to 2, 4]')
SELECT JSON_VALUE('{"hello":2}', '$.hello')
SELECT toTypeName(JSON_VALUE('{"hello":2}', '$.hello'))
select JSON_VALUE('{"hello":"world"}', '$.b') settings function_json_value_return_type_allow_nullable=true
select JSON_VALUE('{"hello":{"world":"!"}}', '$.hello') settings function_json_value_return_type_allow_complex=true

Result:

world
0
2
String

toJSONString

Serializes a value to its JSON representation. Various data types and nested structures are supported. 64-bit integers or bigger (like UInt64 or Int128) are enclosed in quotes by default. output_format_json_quote_64bit_integers controls this behavior. Special values NaN and inf are replaced with null. Enable output_format_json_quote_denormals setting to show them. When serializing an Enum value, the function outputs its name.

Syntax

toJSONString(value)

Arguments

  • value: Value to serialize. Value may be of any data type.

Returned value

  • JSON representation of the value. String.

Example

The first example shows serialization of a Map. The second example shows some special values wrapped into a Tuple.

Query:

SELECT toJSONString(map('key1', 1, 'key2', 2))
SELECT toJSONString(tuple(1.25, NULL, NaN, +inf, -inf, [])) SETTINGS output_format_json_quote_denormals = 1

Result:

{"key1":1,"key2":2}
[1.25,null,"nan","inf","-inf",[]]

JSONArrayLength

Returns the number of elements in the outermost JSON array. The function returns NULL if input JSON string is invalid.

Syntax

JSONArrayLength(json)

Alias: JSON_ARRAY_LENGTH(json).

Arguments

  • json: String with valid JSON.

Returned value

  • If json is a valid JSON array string, returns the number of array elements, otherwise returns NULL. Nullable(UInt64).

Example

SELECT
    JSONArrayLength(''),
    JSONArrayLength('[1,2,3]')

┌─JSONArrayLength('')─┬─JSONArrayLength('[1,2,3]')─┐
│                ᴺᵁᴸᴸ │                          3 │
└─────────────────────┴────────────────────────────┘

jsonMergePatch

Returns the merged JSON object string which is formed by merging multiple JSON objects.

Syntax

jsonMergePatch(json1, json2, ...)

Arguments

  • json: String with valid JSON.

Returned value

  • If JSON object strings are valid, return the merged JSON object string. String.

Example

SELECT jsonMergePatch('{"a":1}', '{"name": "joey"}', '{"name": "tom"}', '{"name": "zoey"}') AS res

┌─res───────────────────┐
│ {"a":1,"name":"zoey"} │
└───────────────────────┘

JSONAllPaths

Returns the list of all paths stored in each row in JSON column.

Syntax

JSONAllPaths(json)

Arguments

  • json: JSON.

Returned value

  • An array of paths. Array(String).

Example

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONAllPaths(json) FROM test

┌─json─────────────────────────────────┬─JSONAllPaths(json)─┐
│ {"a":"42"}                           │ ['a']              │
│ {"b":"Hello"}                        │ ['b']              │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ ['a','c']          │
└──────────────────────────────────────┴────────────────────┘

JSONAllPathsWithTypes

Returns the map of all paths and their data types stored in each row in JSON column.

Syntax

JSONAllPathsWithTypes(json)

Arguments

  • json: JSON.

Returned value

  • An array of paths. Map(String, String).

Example

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONAllPathsWithTypes(json) FROM test

┌─json─────────────────────────────────┬─JSONAllPathsWithTypes(json)───────────────┐
│ {"a":"42"}                           │ {'a':'Int64'}                             │
│ {"b":"Hello"}                        │ {'b':'String'}                            │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ {'a':'Array(Nullable(Int64))','c':'Date'} │
└──────────────────────────────────────┴───────────────────────────────────────────┘

JSONDynamicPaths

Returns the list of dynamic paths that are stored as separate subcolumns in JSON column.

Syntax

JSONDynamicPaths(json)

Arguments

  • json: JSON.

Returned value

  • An array of paths. Array(String).

Example

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONDynamicPaths(json) FROM test

┌─json─────────────────────────────────┬─JSONDynamicPaths(json)─┐
| {"a":"42"}                           │ ['a']                  │
│ {"b":"Hello"}                        │ []                     │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ ['a']                  │
└──────────────────────────────────────┴────────────────────────┘

JSONDynamicPathsWithTypes

Returns the map of dynamic paths that are stored as separate subcolumns and their types in each row in JSON column.

Syntax

JSONAllPathsWithTypes(json)

Arguments

  • json: JSON.

Returned value

  • An array of paths. Map(String, String).

Example

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONDynamicPathsWithTypes(json) FROM test

┌─json─────────────────────────────────┬─JSONDynamicPathsWithTypes(json)─┐
│ {"a":"42"}                           │ {'a':'Int64'}                   │
│ {"b":"Hello"}                        │ {}                              │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ {'a':'Array(Nullable(Int64))'}  │
└──────────────────────────────────────┴─────────────────────────────────┘

JSONSharedDataPaths

Returns the list of paths that are stored in shared data structure in JSON column.

Syntax

JSONSharedDataPaths(json)

Arguments

  • json: JSON.

Returned value

  • An array of paths. Array(String).

Example

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONSharedDataPaths(json) FROM test

┌─json─────────────────────────────────┬─JSONSharedDataPaths(json)─┐
│ {"a":"42"}                           │ []                        │
│ {"b":"Hello"}                        │ ['b']                     │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ ['c']                     │
└──────────────────────────────────────┴───────────────────────────┘

JSONSharedDataPathsWithTypes

Returns the map of paths that are stored in shared data structure and their types in each row in JSON column.

Syntax

JSONSharedDataPathsWithTypes(json)

Arguments

  • json: JSON.

Returned value

  • An array of paths. Map(String, String).

Example

CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory
INSERT INTO test FORMAT JSONEachRow {"json" : {"a" : 42}}, {"json" : {"b" : "Hello"}}, {"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}
SELECT json, JSONSharedDataPathsWithTypes(json) FROM test

┌─json─────────────────────────────────┬─JSONSharedDataPathsWithTypes(json)─┐
│ {"a":"42"}                           │ {}                                 │
│ {"b":"Hello"}                        │ {'b':'String'}                     │
│ {"a":["1","2","3"],"c":"2020-01-01"} │ {'c':'Date'}                       │
└──────────────────────────────────────┴────────────────────────────────────┘
Was this page helpful?
Updated