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

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

Example

Query:

CREATE TABLE jsons
(
    `json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONHas(json, 'foo') FROM jsons;
SELECT simpleJSONHas(json, 'bar') FROM jsons;

Result:

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

Example

Query:

CREATE TABLE jsons
(
    `json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"4e3"}');
INSERT INTO jsons VALUES ('{"foo":3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractUInt(json, 'foo') FROM jsons ORDER BY json;

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

Example

Query:

CREATE TABLE jsons
(
    `json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractInt(json, 'foo') FROM jsons ORDER BY json;

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

Example

Query:

CREATE TABLE jsons
(
    `json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractFloat(json, 'foo') FROM jsons ORDER BY json;

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

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:

CREATE TABLE jsons
(
    `json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":false,"bar":true}');
INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONExtractBool(json, 'bar') FROM jsons ORDER BY json;
SELECT simpleJSONExtractBool(json, 'foo') FROM jsons ORDER BY json;

Result:

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

CREATE TABLE jsons
(
    `json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":{"def":[1,2,3]}}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractRaw(json, 'foo') FROM jsons 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 are not from the basic multilingual plane (they are converted to CESU-8 instead of UTF-8).

Example

Query:

CREATE TABLE jsons
(
    `json` String
)
ENGINE = Memory;

INSERT INTO jsons VALUES ('{"foo":"\\n\\u0000"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263a"}');
INSERT INTO jsons VALUES ('{"foo":"hello}');

SELECT simpleJSONExtractString(json, 'foo') FROM jsons 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 does not 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 does not 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 does not 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 does not 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 does not 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 does not 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 does not 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 does not 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 ClickHouse 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'

Referring to a nested values by passing multiple indices_or_keys parameters:

SELECT JSONExtract('{"a":{"b":"hello","c":{"d":[1,2,3],"e":[1,3,7]}}}','a','c','Map(String, Array(UInt8))') AS val, toTypeName(val), val['d'];

Result:

┌─val───────────────────────┬─toTypeName(val)───────────┬─arrayElement(val, 'd')─┐
│ {'d':[1,2,3],'e':[1,3,7]} │ Map(String, Array(UInt8)) │ [1,2,3]                │
└───────────────────────────┴───────────────────────────┴────────────────────────┘

JSONExtractKeysAndValues

Parses key-value pairs from JSON where the values are of the given ClickHouse 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:

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

JSONExtractRaw

Returns part of the JSON as an unparsed string. If the part does not 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 does not 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]';

Case-Insensitive JSONExtract Functions

The following functions perform ASCII case-insensitive key matching when extracting values from JSON objects. They work identically to their case-sensitive counterparts, except that object keys are matched without regard to case. When multiple keys match with different cases, the first match is returned.

Note

These functions may be less performant than their case-sensitive counterparts, so use the regular JSONExtract functions if possible.

JSONExtractIntCaseInsensitive

Parses JSON and extracts a value of Int type using case-insensitive key matching. This function is similar to JSONExtractInt.

Syntax

JSONExtractIntCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractIntCaseInsensitive('{"Value": 123}', 'value') = 123;
SELECT JSONExtractIntCaseInsensitive('{"VALUE": -456}', 'Value') = -456;

JSONExtractUIntCaseInsensitive

Parses JSON and extracts a value of UInt type using case-insensitive key matching. This function is similar to JSONExtractUInt.

Syntax

JSONExtractUIntCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractUIntCaseInsensitive('{"COUNT": 789}', 'count') = 789;

JSONExtractFloatCaseInsensitive

Parses JSON and extracts a value of Float type using case-insensitive key matching. This function is similar to JSONExtractFloat.

Syntax

JSONExtractFloatCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractFloatCaseInsensitive('{"Price": 12.34}', 'PRICE') = 12.34;

JSONExtractBoolCaseInsensitive

Parses JSON and extracts a boolean value using case-insensitive key matching. This function is similar to JSONExtractBool.

Syntax

JSONExtractBoolCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractBoolCaseInsensitive('{"IsActive": true}', 'isactive') = 1;

JSONExtractStringCaseInsensitive

Parses JSON and extracts a string using case-insensitive key matching. This function is similar to JSONExtractString.

Syntax

JSONExtractStringCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractStringCaseInsensitive('{"ABC": "def"}', 'abc') = 'def';
SELECT JSONExtractStringCaseInsensitive('{"User": {"Name": "John"}}', 'user', 'name') = 'John';

JSONExtractCaseInsensitive

Parses JSON and extracts a value of the given ClickHouse data type using case-insensitive key matching. This function is similar to JSONExtract.

Syntax

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

Example

SELECT JSONExtractCaseInsensitive('{"Number": 123}', 'number', 'Int32') = 123;
SELECT JSONExtractCaseInsensitive('{"List": [1, 2, 3]}', 'list', 'Array(Int32)') = [1, 2, 3];

JSONExtractKeysAndValuesCaseInsensitive

Parses key-value pairs from JSON using case-insensitive key matching. This function is similar to JSONExtractKeysAndValues.

Syntax

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

Example

SELECT JSONExtractKeysAndValuesCaseInsensitive('{"Name": "Alice", "AGE": 30}', 'String')[1] = ('Name', 'Alice');

JSONExtractRawCaseInsensitive

Returns part of the JSON as an unparsed string using case-insensitive key matching. This function is similar to JSONExtractRaw.

Syntax

JSONExtractRawCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractRawCaseInsensitive('{"Object": {"key": "value"}}', 'OBJECT') = '{"key":"value"}';

JSONExtractArrayRawCaseInsensitive

Returns an array with elements of JSON array, each represented as unparsed string, using case-insensitive key matching. This function is similar to JSONExtractArrayRaw.

Syntax

JSONExtractArrayRawCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractArrayRawCaseInsensitive('{"Items": [1, 2, 3]}', 'ITEMS') = ['1', '2', '3'];

JSONExtractKeysAndValuesRawCaseInsensitive

Extracts raw key-value pairs from JSON using case-insensitive key matching. This function is similar to JSONExtractKeysAndValuesRaw.

Syntax

JSONExtractKeysAndValuesRawCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractKeysAndValuesRawCaseInsensitive('{"Name": "Alice", "AGE": 30}')[1] = ('Name', '"Alice"');

JSONExtractKeysCaseInsensitive

Parses a JSON string and extracts the keys using case-insensitive key matching to navigate to nested objects. This function is similar to JSONExtractKeys.

Syntax

JSONExtractKeysCaseInsensitive(json [, indices_or_keys]...)

Example

SELECT JSONExtractKeysCaseInsensitive('{"Name": "Alice", "AGE": 30}') = ['Name', 'AGE'];
SELECT JSONExtractKeysCaseInsensitive('{"User": {"name": "John", "AGE": 25}}', 'user') = ['name', 'AGE'];

JSONExtractArrayRaw

Returns an array with elements of JSON array, each represented as unparsed string. If the part does not 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 does not exist or is not 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 does not 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 does not 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.

Note

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.

Note

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.

Note

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",[]]

See Also

• output_format_json_quote_64bit_integers
• output_format_json_quote_denormals

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'}                       │
└──────────────────────────────────────┴────────────────────────────────────┘

JSONAllPaths

Introduced in: v24.8

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

Syntax

JSONAllPaths(json)

Arguments

• json — JSON column. JSON

Returned value

Returns an array of all paths in the JSON column. Array(String)

Examples

Usage 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

Introduced in: v24.8

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

Syntax

JSONAllPathsWithTypes(json)

Arguments

• json — JSON column. JSON

Returned value

Returns a map of all paths and their data types in the JSON column. Map(String, String)

Examples

Usage 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'} │
└──────────────────────────────────────┴───────────────────────────────────────────┘

JSONArrayLength

Introduced in: v23.2

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

Syntax

JSONArrayLength(json)

Arguments

• json — String with valid JSON. String

Returned value

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

Examples

Usage example

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

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

JSONDynamicPaths

Introduced in: v24.8

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

Syntax

JSONDynamicPaths(json)

Arguments

• json — JSON column. JSON

Returned value

Returns an array of dynamic paths in the JSON column. Array(String)

Examples

Usage 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

Introduced in: v24.8

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

Syntax

JSONDynamicPathsWithTypes(json)

Arguments

• json — JSON column. JSON

Returned value

Returns a map of dynamic paths and their data types in the JSON column. Map(String, String)

Examples

Usage 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))'}  │
└──────────────────────────────────────┴─────────────────────────────────┘

JSONExtract

Introduced in: v19.14

Parses JSON and extracts a value with given ClickHouse data type.

Syntax

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

Arguments

• json — JSON string to parse. String
• return_type — ClickHouse data type to return. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returned value

Returns a value of specified ClickHouse data type if possible, otherwise returns the default value for that type.

Examples

Usage example

SELECT JSONExtract('{"a": "hello", "b": [-100, 200.0, 300]}', 'Tuple(String, Array(Float64))') AS res;

┌─res──────────────────────────────┐
│ ('hello',[-100,200,300])         │
└──────────────────────────────────┘

JSONExtractArrayRaw

Introduced in: v20.1

Returns an array with elements of JSON array, each represented as unparsed string.

Syntax

JSONExtractArrayRaw(json[, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

Returns an array of strings with JSON array elements. If the part is not an array or does not exist, an empty array will be returned. Array(String)

Examples

Usage example

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

┌─res──────────────────────────┐
│ ['-100','200.0','"hello"']   │
└──────────────────────────────┘

JSONExtractArrayRawCaseInsensitive

Introduced in: v25.8

Returns an array with elements of JSON array, each represented as unparsed string, using case-insensitive key matching. This function is similar to JSONExtractArrayRaw.

Syntax

JSONExtractArrayRawCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the array. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns an array of raw JSON strings. Array(String)

Examples

basic

SELECT JSONExtractArrayRawCaseInsensitive('{"Items": [1, 2, 3]}', 'ITEMS')

['1','2','3']

JSONExtractBool

Introduced in: v20.1

Parses JSON and extracts a value of Bool type.

Syntax

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

Arguments

• 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 or (U)Int*

Returned value

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

Examples

Usage example

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

┌─res─┐
│   1 │
└─────┘

JSONExtractBoolCaseInsensitive

Introduced in: v25.8

Parses JSON and extracts a boolean value using case-insensitive key matching. This function is similar to JSONExtractBool.

Syntax

JSONExtractBoolCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns the extracted boolean value (1 for true, 0 for false), 0 if not found. UInt8

Examples

basic

SELECT JSONExtractBoolCaseInsensitive('{"IsActive": true}', 'isactive')

1

JSONExtractCaseInsensitive

Introduced in: v25.8

Parses JSON and extracts a value of the given ClickHouse data type using case-insensitive key matching. This function is similar to JSONExtract.

Syntax

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

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*
• return_type — The ClickHouse data type to extract String

Returned value

Returns the extracted value in the specified data type. Any

Examples

int_type

SELECT JSONExtractCaseInsensitive('{"Number": 123}', 'number', 'Int32')

123

array_type

SELECT JSONExtractCaseInsensitive('{"List": [1, 2, 3]}', 'list', 'Array(Int32)')

[1,2,3]

JSONExtractFloat

Introduced in: v20.1

Parses JSON and extracts a value of Float type.

Syntax

JSONExtractFloat(json[, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

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

Examples

Usage example

SELECT JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) AS res;

┌─res─┐
│ 200 │
└─────┘

JSONExtractFloatCaseInsensitive

Introduced in: v25.8

Parses JSON and extracts a value of Float type using case-insensitive key matching. This function is similar to JSONExtractFloat.

Syntax

JSONExtractFloatCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns the extracted Float value, 0 if not found or cannot be converted. Float64

Examples

basic

SELECT JSONExtractFloatCaseInsensitive('{"Price": 12.34}', 'PRICE')

12.34

JSONExtractInt

Introduced in: v20.1

Parses JSON and extracts a value of Int type.

Syntax

JSONExtractInt(json[, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

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

Examples

Usage example

SELECT JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1) AS res;

┌─res─┐
│ 200 │
└─────┘

JSONExtractIntCaseInsensitive

Introduced in: v25.8

Parses JSON and extracts a value of Int type using case-insensitive key matching. This function is similar to JSONExtractInt.

Syntax

JSONExtractIntCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns the extracted Int value, 0 if not found or cannot be converted. Int64

Examples

basic

SELECT JSONExtractIntCaseInsensitive('{"Value": 123}', 'value')

123

nested

SELECT JSONExtractIntCaseInsensitive('{"DATA": {"COUNT": 42}}', 'data', 'Count')

42

JSONExtractKeys

Introduced in: v21.11

Parses a JSON string and extracts the keys.

Syntax

JSONExtractKeys(json[, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

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

Examples

Usage example

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

┌─res─────────┐
│ ['a','b']   │
└─────────────┘

JSONExtractKeysAndValues

Introduced in: v20.1

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

Syntax

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

Arguments

• json — JSON string to parse. String
• value_type — ClickHouse data type of the values. String
• indices_or_keys — A list of zero or more arguments each of which can be either string or integer. String or (U)Int*

Returned value

Returns an array of tuples with the parsed key-value pairs. Array(Tuple(String, value_type))

Examples

Usage example

SELECT JSONExtractKeysAndValues('{"x": {"a": 5, "b": 7, "c": 11}}', 'Int8', 'x') AS res;

┌─res────────────────────┐
│ [('a',5),('b',7),('c',11)] │
└────────────────────────┘

JSONExtractKeysAndValuesCaseInsensitive

Introduced in: v25.8

Parses key-value pairs from JSON using case-insensitive key matching. This function is similar to JSONExtractKeysAndValues.

Syntax

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

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the object. Keys use case-insensitive matching String or (U)Int*
• value_type — The ClickHouse data type of the values String

Returned value

Returns an array of tuples containing key-value pairs. Array(Tuple(String, T))

Examples

basic

SELECT JSONExtractKeysAndValuesCaseInsensitive('{"Name": "Alice", "AGE": 30}', 'String')

[('Name','Alice'),('AGE','30')]

JSONExtractKeysAndValuesRaw

Introduced in: v20.4

Returns an array of tuples with keys and values from a JSON object. All values are represented as unparsed strings.

Syntax

JSONExtractKeysAndValuesRaw(json[, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

Returns an array of tuples with parsed key-value pairs where values are unparsed strings. Array(Tuple(String, String))

Examples

Usage example

SELECT JSONExtractKeysAndValuesRaw('{"a": [-100, 200.0], "b": "hello"}') AS res;

┌─res──────────────────────────────────┐
│ [('a','[-100,200.0]'),('b','"hello"')] │
└──────────────────────────────────────┘

JSONExtractKeysAndValuesRawCaseInsensitive

Introduced in: v25.8

Extracts raw key-value pairs from JSON using case-insensitive key matching. This function is similar to JSONExtractKeysAndValuesRaw.

Syntax

JSONExtractKeysAndValuesRawCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the object. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns an array of tuples containing key-value pairs as raw strings. Array(Tuple(String, String))

Examples

basic

SELECT JSONExtractKeysAndValuesRawCaseInsensitive('{"Name": "Alice", "AGE": 30}')

[('Name','"Alice"'),('AGE','30')]

JSONExtractKeysCaseInsensitive

Introduced in: v25.8

Parses a JSON string and extracts the keys using case-insensitive key matching to navigate to nested objects. This function is similar to JSONExtractKeys.

Syntax

JSONExtractKeysCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the object. Keys use case-insensitive matching String or (U)Int*

Returned value

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

Examples

basic

SELECT JSONExtractKeysCaseInsensitive('{"Name": "Alice", "AGE": 30}')

['Name','AGE']

nested

SELECT JSONExtractKeysCaseInsensitive('{"User": {"name": "John", "AGE": 25}}', 'user')

['name','AGE']

JSONExtractRaw

Introduced in: v20.1

Returns a part of JSON as unparsed string.

Syntax

JSONExtractRaw(json[, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

Returns the part of JSON as an unparsed string. If the part does not exist or has a wrong type, an empty string will be returned. String

Examples

Usage example

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

┌─res──────────────┐
│ [-100,200.0,300] │
└──────────────────┘

JSONExtractRawCaseInsensitive

Introduced in: v25.8

Returns part of the JSON as an unparsed string using case-insensitive key matching. This function is similar to JSONExtractRaw.

Syntax

JSONExtractRawCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns the raw JSON string of the extracted element. String

Examples

object

SELECT JSONExtractRawCaseInsensitive('{"Object": {"key": "value"}}', 'OBJECT')

{"key":"value"}

JSONExtractString

Introduced in: v20.1

Parses JSON and extracts a value of String type.

Syntax

JSONExtractString(json[, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

Returns a String value if it exists, otherwise returns an empty string. String

Examples

Usage example

SELECT JSONExtractString('{"a": "hello", "b": [-100, 200.0, 300]}', 'a') AS res;

┌─res───┐
│ hello │
└───────┘

JSONExtractStringCaseInsensitive

Introduced in: v25.8

Parses JSON and extracts a string using case-insensitive key matching. This function is similar to JSONExtractString.

Syntax

JSONExtractStringCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns the extracted string value, empty string if not found. String

Examples

basic

SELECT JSONExtractStringCaseInsensitive('{"ABC": "def"}', 'abc')

def

nested

SELECT JSONExtractStringCaseInsensitive('{"User": {"Name": "John"}}', 'user', 'name')

John

JSONExtractUInt

Introduced in: v20.1

Parses JSON and extracts a value of UInt type.

Syntax

JSONExtractUInt(json [, indices_or_keys, ...])

Arguments

• 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 or (U)Int*

Returned value

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

Examples

Usage example

SELECT JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) AS res;

┌─res─┐
│ 300 │
└─────┘

JSONExtractUIntCaseInsensitive

Introduced in: v25.8

Parses JSON and extracts a value of UInt type using case-insensitive key matching. This function is similar to JSONExtractUInt.

Syntax

JSONExtractUIntCaseInsensitive(json [, indices_or_keys]...)

Arguments

• json — JSON string to parse String
• indices_or_keys — Optional. Indices or keys to navigate to the field. Keys use case-insensitive matching String or (U)Int*

Returned value

Returns the extracted UInt value, 0 if not found or cannot be converted. UInt64

Examples

basic

SELECT JSONExtractUIntCaseInsensitive('{"COUNT": 789}', 'count')

789

JSONHas

Introduced in: v20.1

Checks for the existence of the provided value(s) in the JSON document.

Syntax

JSONHas(json[ ,indices_or_keys, ...])

Arguments

• json — JSON string to parse String
• [ ,indices_or_keys, ...] — A list of zero or more arguments. String or (U)Int*

Returned value

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

Examples

Usage example

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

1
0

JSONLength

Introduced in: v20.1

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

Syntax

JSONLength(json [, indices_or_keys, ...])

Arguments

• json — JSON string to parse String
• [, indices_or_keys, ...] — Optional. A list of zero or more arguments. String or (U)Int8/16/32/64

Returned value

Returns the length of the JSON array or JSON object, otherwise returns 0 if the value does not exist or has the wrong type. UInt64

Examples

Usage example

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

1
1

JSONMergePatch

Introduced in: v23.10

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

Syntax

jsonMergePatch(json1[, json2, ...])

Arguments

• json1[, json2, ...] — One or more strings with valid JSON. String

Returned value

Returns the merged JSON object string, if the JSON object strings are valid. String

Examples

Usage example

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

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

JSONSharedDataPaths

Introduced in: v24.8

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

Syntax

JSONSharedDataPaths(json)

Arguments

• json — JSON column. JSON

Returned value

Returns an array of paths stored in shared data structure in the JSON column. Array(String)

Examples

Usage 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

Introduced in: v24.8

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

Syntax

JSONSharedDataPathsWithTypes(json)

Arguments

• json — JSON column. JSON

Returned value

Returns a map of paths stored in shared data structure and their data types in the JSON column. Map(String, String)

Examples

Usage 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'}                        │
└──────────────────────────────────────┴─────────────────────────────────────┘

JSONType

Introduced in: v20.1

Return the type of a JSON value. If the value does not exist, Null=0 will be returned.

Syntax

JSONType(json[, indices_or_keys, ...])

Arguments

• json — JSON string to parse String
• json[, indices_or_keys, ...] — A list of zero or more arguments, each of which can be either string or integer. String or (U)Int8/16/32/64

Returned value

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

Examples

Usage example

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

1
1
1

JSON_EXISTS

Introduced in: v21.8

If the value exists in the JSON document, 1 will be returned. If the value does not exist, 0 will be returned.

Syntax

JSON_EXISTS(json, path)

Arguments

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

Returned value

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

Examples

Usage example

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_EXISTS(⋯ '$.hello')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯llo.world')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[*]')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[0]')─┐
│                        1 │
└──────────────────────────┘

JSON_QUERY

Introduced in: v21.8

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)

Arguments

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

Returned value

Returns the extracted JSON array or JSON object as a string, or an empty string if the value does not exist. String

Examples

Usage example

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'));

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

JSON_VALUE

Introduced in: v21.11

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)

Arguments

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

Returned value

Returns the extracted JSON scalar as a string, or an empty string if the value does not exist. String

Examples

Usage example

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 JSON_VALUE('{"hello":"world"}', '$.b') settings function_json_value_return_type_allow_nullable=true;

world
0
2
ᴺᵁᴸᴸ

dynamicElement

Introduced in: v

Extracts a column with specified type from a Dynamic column.

Syntax

dynamicElement(dynamic, type_name)

Arguments

• dynamic — Dynamic column - type_name — The name of the variant type to extract

Returned value

Examples

Example

CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, dynamicType(d), dynamicElement(d, 'String'), dynamicElement(d, 'Int64'), dynamicElement(d, 'Array(Int64)'), dynamicElement(d, 'Date'), dynamicElement(d, 'Array(String)') FROM test;

┌─d─────────────┬─dynamicType(d)─┬─dynamicElement(d, 'String')─┬─dynamicElement(d, 'Int64')─┬─dynamicElement(d, 'Array(Int64)')─┬─dynamicElement(d, 'Date')─┬─dynamicElement(d, 'Array(String)')─┐
│ ᴺᵁᴸᴸ          │ None           │ ᴺᵁᴸᴸ                        │                       ᴺᵁᴸᴸ │ []                                │                      ᴺᵁᴸᴸ │ []                                 │
│ 42            │ Int64          │ ᴺᵁᴸᴸ                        │                         42 │ []                                │                      ᴺᵁᴸᴸ │ []                                 │
│ Hello, World! │ String         │ Hello, World!               │                       ᴺᵁᴸᴸ │ []                                │                      ᴺᵁᴸᴸ │ []                                 │
│ [1,2,3]       │ Array(Int64)   │ ᴺᵁᴸᴸ                        │                       ᴺᵁᴸᴸ │ [1,2,3]                           │                      ᴺᵁᴸᴸ │ []                                 │
└───────────────┴────────────────┴─────────────────────────────┴────────────────────────────┴───────────────────────────────────┴───────────────────────────┴────────────────────────────────────┘

dynamicType

Introduced in: v

Returns the variant type name for each row of Dynamic column. If row contains NULL, it returns 'None' for it.

Syntax

dynamicType(dynamic)

Arguments

• dynamic — Dynamic column

Returned value

Examples

Example

CREATE TABLE test (d Dynamic) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, dynamicType(d) FROM test;

┌─d─────────────┬─dynamicType(d)─┐
│ ᴺᵁᴸᴸ          │ None           │
│ 42            │ Int64          │
│ Hello, World! │ String         │
│ [1,2,3]       │ Array(Int64)   │
└───────────────┴────────────────┘

isDynamicElementInSharedData

Introduced in: v

Returns true for rows in Dynamic column that are not separated into subcolumns and stored inside shared variant in binary form.

Syntax

isDynamicElementInSharedData(dynamic)

Arguments

• dynamic — Dynamic column

Returned value

Examples

Example

CREATE TABLE test (d Dynamic(max_types=2)) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT d, isDynamicElementInSharedData(d) FROM test;

┌─d─────────────┬─isDynamicElementInSharedData(d)─┐
│ ᴺᵁᴸᴸ          │ false              │
│ 42            │ false              │
│ Hello, World! │ true               │
│ [1,2,3]       │ true               │
└───────────────┴────────────────────┘

isValidJSON

Introduced in: v20.1

Checks that the string passed is valid JSON.

Syntax

isValidJSON(json)

Arguments

• json — JSON string to validate String

Returned value

Returns 1 if the string is valid JSON, otherwise 0. UInt8

Examples

Usage example

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

1
0

Using integers to access both JSON arrays and JSON objects

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

0
1
1
1
1
1
0

simpleJSONExtractBool

Introduced in: v21.4

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

Syntax

simpleJSONExtractBool(json, field_name)

Arguments

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

Returned value

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

Examples

Usage example

CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":false,"bar":true}');
INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONExtractBool(json, 'bar') FROM jsons ORDER BY json;
SELECT simpleJSONExtractBool(json, 'foo') FROM jsons ORDER BY json;

0
1
0
0

simpleJSONExtractFloat

Introduced in: v21.4

Parses Float64 from the value of the field named field_name. If field_name 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)

Arguments

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

Returned value

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

Examples

Usage example

CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractFloat(json, 'foo') FROM jsons ORDER BY json;

0
-4000
0
-3.4
5

simpleJSONExtractInt

Introduced in: v21.4

Parses Int64 from the value of the field named field_name. If field_name 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)

Arguments

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

Returned value

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

Examples

Usage example

CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractInt(json, 'foo') FROM jsons ORDER BY json;

0
-4
0
-3
5

simpleJSONExtractRaw

Introduced in: v21.4

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

Syntax

simpleJSONExtractRaw(json, field_name)

Arguments

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

Returned value

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

Examples

Usage example

CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"-4e3"}');
INSERT INTO jsons VALUES ('{"foo":-3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":{"def":[1,2,3]}}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractRaw(json, 'foo') FROM jsons ORDER BY json;

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

simpleJSONExtractString

Introduced in: v21.4

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

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

Syntax

simpleJSONExtractString(json, field_name)

Arguments

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

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

Examples

Usage example

CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"\\n\\u0000"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263"}');
INSERT INTO jsons VALUES ('{"foo":"\\u263a"}');
INSERT INTO jsons VALUES ('{"foo":"hello}');

SELECT simpleJSONExtractString(json, 'foo') FROM jsons ORDER BY json;

\n\0

☺

simpleJSONExtractUInt

Introduced in: v21.4

Parses UInt64 from the value of the field named field_name. If field_name 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)

Arguments

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

Returned value

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

Examples

Usage example

CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"4e3"}');
INSERT INTO jsons VALUES ('{"foo":3.4}');
INSERT INTO jsons VALUES ('{"foo":5}');
INSERT INTO jsons VALUES ('{"foo":"not1number"}');
INSERT INTO jsons VALUES ('{"baz":2}');

SELECT simpleJSONExtractUInt(json, 'foo') FROM jsons ORDER BY json;

0
4
0
3
5

simpleJSONHas

Introduced in: v21.4

Checks whether there is a field named field_name.

Syntax

simpleJSONHas(json, field_name)

Arguments

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

Returned value

Returns 1 if the field exists, 0 otherwise UInt8

Examples

Usage example

CREATE TABLE jsons
(
    `json` String
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO jsons VALUES ('{"foo":"true","qux":1}');

SELECT simpleJSONHas(json, 'foo') FROM jsons;
SELECT simpleJSONHas(json, 'bar') FROM jsons;

1
0

toJSONString

Introduced in: v21.7

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.

See also:

• output_format_json_quote_64bit_integers
• output_format_json_quote_denormals

Syntax

toJSONString(value)

Arguments

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

Returned value

Returns the JSON representation of the value. String

Examples

Map serialization

SELECT toJSONString(map('key1', 1, 'key2', 2));

┌─toJSONString(map('key1', 1, 'key2', 2))─┐
│ {"key1":1,"key2":2}                     │
└─────────────────────────────────────────┘

Special values

SELECT toJSONString(tuple(1.25, NULL, NaN, +inf, -inf, [])) SETTINGS output_format_json_quote_denormals = 1;

┌─toJSONString(tuple(1.25, NULL, NaN, plus(inf), minus(inf), []))─┐
│ [1.25,null,"nan","inf","-inf",[]]                               │
└─────────────────────────────────────────────────────────────────┘

variantElement

Introduced in: v

Extracts a column with specified type from a Variant column.

Syntax

variantElement(variant, type_name, [, default_value])

Arguments

• variant — Variant column - type_name — The name of the variant type to extract - default_value — The default value that will be used if variant doesn't have variant with specified type. Can be any type. Optional

Returned value

Examples

Example

CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT v, variantElement(v, 'String'), variantElement(v, 'UInt64'), variantElement(v, 'Array(UInt64)') FROM test;

┌─v─────────────┬─variantElement(v, 'String')─┬─variantElement(v, 'UInt64')─┬─variantElement(v, 'Array(UInt64)')─┐
│ ᴺᵁᴸᴸ          │ ᴺᵁᴸᴸ                        │                        ᴺᵁᴸᴸ │ []                                 │
│ 42            │ ᴺᵁᴸᴸ                        │                          42 │ []                                 │
│ Hello, World! │ Hello, World!               │                        ᴺᵁᴸᴸ │ []                                 │
│ [1,2,3]       │ ᴺᵁᴸᴸ                        │                        ᴺᵁᴸᴸ │ [1,2,3]                            │
└───────────────┴─────────────────────────────┴─────────────────────────────┴────────────────────────────────────┘

variantType

Introduced in: v

Returns the variant type name for each row of Variant column. If row contains NULL, it returns 'None' for it.

Syntax

variantType(variant)

Arguments

• variant — Variant column

Returned value

Examples

Example

CREATE TABLE test (v Variant(UInt64, String, Array(UInt64))) ENGINE = Memory;
INSERT INTO test VALUES (NULL), (42), ('Hello, World!'), ([1, 2, 3]);
SELECT variantType(v) FROM test;

┌─variantType(v)─┐
│ None           │
│ UInt64         │
│ String         │
│ Array(UInt64)  │
└────────────────┘