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:

The field name (function argument) must be a constant.
The field name is somehow canonically encoded in JSON. For example: simpleJSONHas('{"abc":"def"}', 'abc') = 1, but simpleJSONHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
Fields are searched for on any nesting level, indiscriminately. If there are multiple matching fields, the first occurrence is used.
The JSON doesn't 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.

Example

Query:

WITH (SELECT '{"foo":"true","qux":1}') AS json
SELECT simpleJSONHas(json, 'foo'), simpleJSONHas(json, 'bar')

Result:

-----------------------------------------------------------
| simpleJSONHas(json, 'foo') | simpleJSONHas(json, 'bar') |
-----------------------------------------------------------
|                          1 |                          0 |
-----------------------------------------------------------

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 doesn't exist, or it exists but doesn't 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.

Example

Query:

SELECT simpleJSONExtractUInt(json, 'foo') 
FROM (
    select c1::String as json from values(
        ('{"foo":"4e3"}'),
        ('{"foo":3.4}'),
        ('{"foo":5}'),
        ('{"foo":"not1number"}'),
        ('{"baz":2}'))
)
ORDER BY json

Result:

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 doesn't exist, or it exists but doesn't 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.

Example

Query:

SELECT simpleJSONExtractInt(json, 'foo') 
FROM (
    select c1::String as json from values(
        ('{"foo":"-4e3"}'),
        ('{"foo":-3.4}'),
        ('{"foo":5}'),
        ('{"foo":"not1number"}'),
        ('{"baz":2}'))
)
ORDER BY json

Result:

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 doesn't exist, or it exists but doesn't 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.

Example

Query:

SELECT simpleJSONExtractFloat(json, 'foo') 
FROM (
    select c1::String as json from values(
        ('{"foo":"-4e3"}'),
        ('{"foo":-3.4}'),
        ('{"foo":5}'),
        ('{"foo":"not1number"}'),
        ('{"baz":2}'))
)
ORDER BY json

Result:

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.

Example

Query:

SELECT simpleJSONExtractBool(json, 'bar') AS barbool, simpleJSONExtractBool(json, 'foo') AS foobool 
FROM
(
    select c1::String as json from values(
        ('{"foo":false,"bar":true}'),
        ('{"foo":"true","qux":1}')
    )
)
ORDER BY json

Result:

  barbool   foobool  
    UInt8     UInt8  
─────────────────────
        0         0  
─────────────────────
        1         0

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

Example

Query:

SELECT simpleJSONExtractRaw(json, 'foo')
FROM (
    select c1::String as json from values(
        ('{"foo":"-4e3"}'),
        ('{"foo":-3.4}'),
        ('{"foo":5}'),
        ('{"foo":{"def":[1,2,3]}}'),
        ('{"baz":2}')
    )
)
ORDER BY json

Result:


"-4e3"
-3.4
5
{"def":[1,2,3]}

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 aren't from the basic multilingual plane (they are converted to CESU-8 instead of UTF-8).

Example

Query:

SELECT simpleJSONExtractString(json, 'foo')
FROM (
    select c1::String as json from values(
      ('{"foo":"\\n\\u0000"}'),
      ('{"foo":"\\u263"}'),
      ('{"foo":"\\u263a"}'),
      ('{"foo":"hello}')
    )
)
ORDER BY json

Result:

\n\0

☺

JSONExtract functions¶

The following functions are based on simdjson, and designed for more complex JSON parsing requirements.

isValidJSON¶

Checks that passed string is valid JSON.

Syntax

isValidJSON(json)

Examples

SELECT isValidJSON('{"a": "hello", "b": [-100, 200.0, 300]}') = 1
SELECT isValidJSON('not a json') = 0

JSONHas¶

If the value exists in the JSON document, 1 will be returned. If the value doesn't exist, 0 will be returned.

Syntax

JSONHas(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns 1 if the value exists in json, otherwise 0. UInt8.

Examples

Query:

SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 1
SELECT JSONHas('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4) = 0

The minimum index of the element is 1. Thus the element 0 doesn't exist. You may use integers to access both JSON arrays and JSON objects. For example:

SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', 1) = 'a'
SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', 2) = 'b'
SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', -1) = 'b'
SELECT JSONExtractKey('{"a": "hello", "b": [-100, 200.0, 300]}', -2) = 'a'
SELECT JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 1) = 'hello'

JSONLength¶

Return the length of a JSON array or a JSON object. If the value doesn't exist or has the wrong type, 0 will be returned.

Syntax

JSONLength(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns the length of the JSON array or JSON object. Returns 0 if the value doesn't exist or has the wrong type. UInt64.

Examples

SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 3
SELECT JSONLength('{"a": "hello", "b": [-100, 200.0, 300]}') = 2

JSONType¶

Return the type of a JSON value. If the value doesn't exist, Null=0 will be returned (not usual Null, but Null=0 of Enum8('Null' = 0, 'String' = 34,...). .

Syntax

JSONType(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns the type of a JSON value as a string, otherwise if the value doesn't exists it returns Null=0. Enum.

Examples

SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}') = 'Object'
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') = 'String'
SELECT JSONType('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = 'Array'

JSONExtractUInt¶

Parses JSON and extracts a value of UInt type.

Syntax

JSONExtractUInt(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns a UInt value if it exists, otherwise it returns 0. UInt64.

Examples

Query:

SELECT JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) as x, toTypeName(x)

Result:

┌───x─┬─toTypeName(x)─┐
│ 300 │ UInt64        │
└─────┴───────────────┘

JSONExtractInt¶

Parses JSON and extracts a value of Int type.

Syntax

JSONExtractInt(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns an Int value if it exists, otherwise it returns 0. Int64.

Examples

Query:

SELECT JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) as x, toTypeName(x)

Result:

┌───x─┬─toTypeName(x)─┐
│ 300 │ Int64         │
└─────┴───────────────┘

JSONExtractFloat¶

Parses JSON and extracts a value of Int type.

Syntax

JSONExtractFloat(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns an Float value if it exists, otherwise it returns 0. Float64.

Examples

Query:

SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) as x, toTypeName(x)

Result:

┌───x─┬─toTypeName(x)─┐
│ 200 │ Float64       │
└─────┴───────────────┘

JSONExtractBool¶

Parses JSON and extracts a boolean value. If the value doesn't exist or has a wrong type, 0 will be returned.

Syntax

JSONExtractBool(json\[, indices_or_keys\]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns a Boolean value if it exists, otherwise it returns 0. Bool.

Example

Query:

SELECT JSONExtractBool('{"passed": true}', 'passed')

Result:

┌─JSONExtractBool('{"passed": true}', 'passed')─┐
│                                             1 │
└───────────────────────────────────────────────┘

JSONExtractString¶

Parses JSON and extracts a string. This function is similar to visitParamExtractString functions. If the value doesn't exist or has a wrong type, an empty string will be returned.

Syntax

JSONExtractString(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns an unescaped string from json. If unescaping failed, if the value doesn't exist or if it has a wrong type then it returns an empty string. String.

Examples

SELECT JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') = 'hello'
SELECT JSONExtractString('{"abc":"\\n\\u0000"}', 'abc') = '\n\0'
SELECT JSONExtractString('{"abc":"\\u263a"}', 'abc') = '☺'
SELECT JSONExtractString('{"abc":"\\u263"}', 'abc') = ''
SELECT JSONExtractString('{"abc":"hello}', 'abc') = ''

JSONExtract¶

Parses JSON and extracts a value of the given data type. This function is a generalized version of the previous JSONExtract<type> functions. Meaning:

JSONExtract(..., 'String') returns exactly the same as JSONExtractString(), JSONExtract(..., 'Float64') returns exactly the same as JSONExtractFloat().

Syntax

JSONExtract(json [, indices_or_keys...], return_type)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.
return_type: A string specifying the type of the value to extract. String.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns a value if it exists of the specified return type, otherwise it returns 0, Null, or an empty-string depending on the specified return type. UInt64, Int64, Float64, Bool or String.

Examples

SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(String, Array(Float64))') = ('hello',[-100,200,300])
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(b Array(Float64), a String)') = ([-100,200,300],'hello')
SELECT JSONExtract('{"a": "hello", "b": "world"}', 'Map(String, String)') = map('a',  'hello', 'b', 'world')
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 'Array(Nullable(Int8))') = [-100, NULL, NULL]
SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 4, 'Nullable(Int64)') = NULL
SELECT JSONExtract('{"passed": true}', 'passed', 'UInt8') = 1
SELECT JSONExtract('{"day": "Thursday"}', 'day', 'Enum8(\'Sunday\' = 0, \'Monday\' = 1, \'Tuesday\' = 2, \'Wednesday\' = 3, \'Thursday\' = 4, \'Friday\' = 5, \'Saturday\' = 6)') = 'Thursday'
SELECT JSONExtract('{"day": 5}', 'day', 'Enum8(\'Sunday\' = 0, \'Monday\' = 1, \'Tuesday\' = 2, \'Wednesday\' = 3, \'Thursday\' = 4, \'Friday\' = 5, \'Saturday\' = 6)') = 'Friday'

JSONExtractKeysAndValues¶

Parses key-value pairs from JSON where the values are of the given data type.

Syntax

JSONExtractKeysAndValues(json [, indices_or_keys...], value_type)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.
value_type: A string specifying the type of the value to extract. String.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns an array of parsed key-value pairs. Array(Tuple(value_type)).

Example

SELECT JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'x', 'Int8') = [('a',5),('b',7),('c',11)]

JSONExtractKeys¶

Parses a JSON string and extracts the keys.

Syntax

JSONExtractKeys(json[, a, b, c...])

Parameters

json: String with valid JSON.
a, b, c...: Comma-separated indices or keys that specify the path to the inner field in a nested JSON object. Each argument can be either a String to get the field by the key or an Integer to get the N-th field (indexed from 1, negative integers count from the end). If not set, the whole JSON is parsed as the top-level object. Optional parameter.

Returned value

Returns an array with the keys of the JSON. Array(String).

Example

Query:

SELECT JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}')

Result:

text
┌─JSONExtractKeys('{"a": "hello", "b": [-100, 200.0, 300]}')─┐
│ ['a','b']                                                  │
└────────────────────────────────────────────────────────────┘

JSONExtractRaw¶

Returns part of the JSON as an unparsed string. If the part doesn't exist or has the wrong type, an empty string will be returned.

Syntax

JSONExtractRaw(json [, indices_or_keys]...)

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns part of the JSON as an unparsed string. If the part doesn't exist or has the wrong type, an empty string is returned. String.

Example

SELECT JSONExtractRaw('{"a": "hello", "b": [-100, 200.0, 300]}', 'b') = '[-100, 200.0, 300]'

JSONExtractArrayRaw¶

Returns an array with elements of JSON array, each represented as unparsed string. If the part doesn't exist or isn’t an array, then an empty array will be returned.

Syntax

JSONExtractArrayRaw(json [, indices_or_keys...])

Parameters

json: JSON string to parse. String.
indices_or_keys: A list of zero or more arguments, each of which can be either string or integer. String, Int*.

indices_or_keys type:

String = access object member by key.
Positive integer = access the n-th member/key from the beginning.
Negative integer = access the n-th member/key from the end.

Returned value

Returns an array with elements of JSON array, each represented as unparsed string. Otherwise, an empty array is returned if the part doesn't exist or isn't an array. Array(String).

Example

SELECT JSONExtractArrayRaw('{"a": "hello", "b": [-100, 200.0, "hello"]}', 'b') = ['-100', '200.0', '"hello"']

JSONExtractKeysAndValuesRaw¶

Extracts raw data from a JSON object.

Syntax

JSONExtractKeysAndValuesRaw(json[, p, a, t, h])

Arguments

json: String with valid JSON.
p, a, t, h: Comma-separated indices or keys that specify the path to the inner field in a nested JSON object. Each argument can be either a string to get the field by the key or an integer to get the N-th field (indexed from 1, negative integers count from the end). If not set, the whole JSON is parsed as the top-level object. Optional parameter.

Returned values

Array with ('key', 'value') tuples. Both tuple members are strings. Array(Tuple(String, String).
Empty array if the requested object doesn't exist, or input JSON is invalid. Array(Tuple(String, String).

Examples

Query:

SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}')

Result:

┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}')─┐
│ [('a','[-100,200]'),('b','{"c":{"d":"hello","f":"world"}}')]                                 │
└──────────────────────────────────────────────────────────────────────────────────────────────┘

Query:

SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b')

Result:

┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b')─┐
│ [('c','{"d":"hello","f":"world"}')]                                                               │
└───────────────────────────────────────────────────────────────────────────────────────────────────┘

Query:

SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', -1, 'c')

Result:

┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', -1, 'c')─┐
│ [('d','"hello"'),('f','"world"')]                                                                     │
└───────────────────────────────────────────────────────────────────────────────────────────────────────┘

JSON_EXISTS¶

If the value exists in the JSON document, 1 will be returned. If the value doesn't exist, 0 will be returned.

Syntax

JSON_EXISTS(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 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 doesn't 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 doesn't 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 doesn't 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"} │
└───────────────────────┘

Data Types

Table engines

Functions

JSON functions¶

simpleJSON (visitParam) functions¶

simpleJSONHas¶

simpleJSONExtractUInt¶

simpleJSONExtractInt¶

simpleJSONExtractFloat¶

simpleJSONExtractBool¶

simpleJSONExtractRaw¶

simpleJSONExtractString¶

JSONExtract functions¶

isValidJSON¶

JSONHas¶

JSONLength¶

JSONType¶

JSONExtractUInt¶

JSONExtractInt¶

JSONExtractFloat¶

JSONExtractBool¶

JSONExtractString¶

JSONExtract¶

JSONExtractKeysAndValues¶

JSONExtractKeys¶

JSONExtractRaw¶

JSONExtractArrayRaw¶

JSONExtractKeysAndValuesRaw¶

JSON_EXISTS¶

JSON_QUERY¶

JSON_VALUE¶

toJSONString¶

JSONArrayLength¶

jsonMergePatch¶