--- title: JSON Functions reference meta: description: Functions for working with JSON. headingMaxLevels: 2 --- # JSON functions The following functions are used for working with JSON. 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 doesn't have space characters outside of string literals. ### simpleJSONHas Checks whether there is a field named `field_name`. The result is `UInt8`. ### Syntax ```sql 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: ```sql WITH (SELECT '{"foo":"true","qux":1}') AS json SELECT simpleJSONHas(json, 'foo'), simpleJSONHas(json, 'bar') ``` Result: ```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 ```sql 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: ```sql 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: ```result 0 4 0 3 5 ``` ### 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 ```sql 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: ```sql 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: ```result 0 -4 0 -3 5 ``` ### 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 ```sql 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: ```sql 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: ```result 0 -4000 0 -3.4 5 ``` ### simpleJSONExtractBool Parses a true/false value from the value of the field named `field_name`. The result is `UInt8`. ### Syntax ```sql 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: ```sql 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: ```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 ```sql 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: ```sql 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: ```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 ```sql 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: ```sql 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: ```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 ```sql isValidJSON(json) ``` ### Examples ```sql 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 ```sql 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: ```sql 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: ```sql 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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: ```sql SELECT JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) as x, toTypeName(x) ``` Result: ```result ┌───x─┬─toTypeName(x)─┐ │ 300 │ UInt64 │ └─────┴───────────────┘ ``` ### JSONExtractInt Parses JSON and extracts a value of Int type. ### Syntax ```sql 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: ```sql SELECT JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) as x, toTypeName(x) ``` Result: ```result ┌───x─┬─toTypeName(x)─┐ │ 300 │ Int64 │ └─────┴───────────────┘ ``` ### JSONExtractFloat Parses JSON and extracts a value of Int type. ### Syntax ```sql 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: ```sql SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) as x, toTypeName(x) ``` Result: ```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 ```sql 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: ```sql SELECT JSONExtractBool('{"passed": true}', 'passed') ``` Result: ```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 ```sql 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 ```sql 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` functions. Meaning: `JSONExtract(..., 'String')` returns exactly the same as `JSONExtractString()`, `JSONExtract(..., 'Float64')` returns exactly the same as `JSONExtractFloat()`. ### Syntax ```sql 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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: ```sql 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 ```sql 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 ```sql 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 ```sql 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 ```sql SELECT JSONExtractArrayRaw('{"a": "hello", "b": [-100, 200.0, "hello"]}', 'b') = ['-100', '200.0', '"hello"'] ``` ### JSONExtractKeysAndValuesRaw Extracts raw data from a JSON object. ### Syntax ```sql 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: ```sql SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}') ``` Result: ```text ┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}')─┐ │ [('a','[-100,200]'),('b','{"c":{"d":"hello","f":"world"}}')] │ └──────────────────────────────────────────────────────────────────────────────────────────────┘ ``` Query: ```sql SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b') ``` Result: ```text ┌─JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', 'b')─┐ │ [('c','{"d":"hello","f":"world"}')] │ └───────────────────────────────────────────────────────────────────────────────────────────────────┘ ``` Query: ```sql SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b":{"c": {"d": "hello", "f": "world"}}}', -1, 'c') ``` Result: ```text ┌─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 ```sql JSON_EXISTS(json, path) ``` ### Parameters - `json`: A string with valid JSON. String. - `path`: A string representing the path. String. {% callout type="info" %} Before version 21.11 the order of arguments was wrong, i.e. JSON_EXISTS(path, json) {% /callout %} ### Returned value - Returns `1` if the value exists in the JSON document, otherwise `0`. ### Examples ```sql 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 ```sql JSON_QUERY(json, path) ``` ### Parameters - `json`: A string with valid JSON. String. - `path`: A string representing the path. String. {% callout type="info" %} Before version 21.11 the order of arguments was wrong, i.e. JSON_EXISTS(path, json) {% /callout %} ### 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: ```sql 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: ```text ["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 ```sql JSON_VALUE(json, path) ``` ### Parameters - `json`: A string with valid JSON. String. - `path`: A string representing the path. String. {% callout type="info" %} Before version 21.11 the order of arguments was wrong, i.e. JSON_EXISTS(path, json) {% /callout %} ### Returned value - Returns the extracted value as a JSON scalar if it exists, otherwise an empty string is returned. String. ### Example Query: ```sql 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: ```text 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 ```sql 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: ```sql SELECT toJSONString(map('key1', 1, 'key2', 2)) SELECT toJSONString(tuple(1.25, NULL, NaN, +inf, -inf, [])) SETTINGS output_format_json_quote_denormals = 1 ``` Result: ```text {"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 ```sql 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 ```sql 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 ```sql 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 ```sql SELECT jsonMergePatch('{"a":1}', '{"name": "joey"}', '{"name": "tom"}', '{"name": "zoey"}') AS res ``` ```text ┌─res───────────────────┐ │ {"a":1,"name":"zoey"} │ └───────────────────────┘ ``` {% ### JSONAllPaths Returns the list of all paths stored in each row in JSON column. ### Syntax ```sql JSONAllPaths(json) ``` ### Arguments - `json`: JSON. ### Returned value - An array of paths. Array(String). ### Example ```sql CREATE TABLE test (json JSON(max_dynamic_paths=1)) ENGINE = Memory INSERT INTO test FORMAT JSONEachRow select c1::String as json from values( ('{"json" : {"a" : 42}}'), ('{"json" : {"b" : "Hello"}}'), ('{"json" : {"a" : [1, 2, 3], "c" : "2020-01-01"}}')) SELECT json, JSONAllPaths(json) FROM test ``` ```text ┌─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 ```sql JSONAllPathsWithTypes(json) ``` ### Arguments - `json`: JSON. ### Returned value - An array of paths. Map(String, String). ### Example ```sql 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 ``` ```text ┌─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 ```sql JSONDynamicPaths(json) ``` ### Arguments - `json`: JSON. ### Returned value - An array of paths. Array(String). ### Example ```sql 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 ``` ```text ┌─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 ```sql JSONAllPathsWithTypes(json) ``` ### Arguments - `json`: JSON. ### Returned value - An array of paths. Map(String, String). ### Example ```sql 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 ``` ```text ┌─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 ```sql JSONSharedDataPaths(json) ``` ### Arguments - `json`: JSON. ### Returned value - An array of paths. Array(String). ### Example ```sql 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 ``` ```text ┌─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 ```sql JSONSharedDataPathsWithTypes(json) ``` ### Arguments - `json`: JSON. ### Returned value - An array of paths. Map(String, String). ### Example ```sql 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 ``` ```text ┌─json─────────────────────────────────┬─JSONSharedDataPathsWithTypes(json)─┐ │ {"a":"42"} │ {} │ │ {"b":"Hello"} │ {'b':'String'} │ │ {"a":["1","2","3"],"c":"2020-01-01"} │ {'c':'Date'} │ └──────────────────────────────────────┴────────────────────────────────────┘ ``` %}