We make the following assumptions about Json format:

1. Field names (function parameters) must be constants.
2. Field names must use standard encoding. For example: visitParamHas('{"abc":"def"}', 'abc') = 1, but visitParamHas('{"\u0061\u0062\u0063":"def"}', 'abc') = 0
3. The function can search for fields in multi-level nested structures without restriction. If multiple matching fields exist, the first matching field is returned.
4. Except for string text, JSON does not contain whitespace characters.

visitParamHas(param, name) #

Checks whether a field with the name «name» exists.

visitParamExtractUInt(param, name) #

Parses the value of the field named «name» as UInt64. If this is a string field, the function will try to parse a number from the beginning of the string. If the field does not exist, or no number can be parsed from it, 0 is returned.

visitParamExtractInt(param, name) #

Same as visitParamExtractUInt, but returns Int64.

visitParamExtractFloat(param, name) #

Same as visitParamExtractUInt, but returns Float64.

visitParamExtractBool(param, name) #

Parses true/false values. The result is of UInt8 type.

visitParamExtractRaw(param, name) #

Returns the value of the field, including whitespace characters.
Example:

visitParamExtractRaw('{"abc":"\n\u0000"}', 'abc') = '"\n\u0000"' 
visitParamExtractRaw('{"abc":{"def":[1,2,3]}}', 'abc') = '{"def":[1,2,3]}'

visitParamExtractString(param, name) #

Parses the string using double quotes. This value is not escaped. If escaping fails, it will return an empty string.
Example:

visitParamExtractString('{"abc":"\n\u0000"}', 'abc') = '\n\0' 
visitParamExtractString('{"abc":"\u263a"}', 'abc') = '☺' 
visitParamExtractString('{"abc":"\u263"}', 'abc') = '' 
visitParamExtractString('{"abc":"hello}', 'abc') = ''

Currently, \uXXXX\uYYYY character encodings are not supported. These encodings are not in the basic multilingual plane (they are converted to CESU-8 instead of UTF-8).
The following functions are based on simdjson and are designed for more complex JSON parsing requirements. However, assumption 2 above still applies.

JSONHas(json[, indices_or_keys]…) #

If the value exists in JSON, 1 is returned.
If the value does not exist, 0 is returned.
Example:

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

indices_or_keys can be a list of zero or more parameters, each parameter can be a string or integer.

• String: Access JSON object members by member name.
• Positive integer: Access the nth member/member name from the beginning.
• Negative integer: Access the nth member/member name from the end.

You can use integers to access 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(json[, indices_or_keys]…) #

Returns the length of a JSON array or JSON object.
If the value does not exist or the type is incorrect, 0 is returned.
Example:

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

JSONType(json[, indices_or_keys]…) returns the type of the JSON value. #

If the value does not exist, Null is returned.
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'

JSONExtractUInt(json[, indices_or_keys]…) #

JSONExtractInt(json[, indices_or_keys]…) #

JSONExtractFloat(json[, indices_or_keys]…) #

JSONExtractBool(json[, indices_or_keys]…) #

Parses JSON and extracts values. These functions are similar to the visitParam* functions.
If the value does not exist or the type is incorrect, it returns 0.
Example:

select JSONExtractInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 1) = -100 
select JSONExtractFloat('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', 2) = 200.0 
select JSONExtractUInt('{"a": "hello", "b": [-100, 200.0, 300]}', 'b', -1) = 300

JSONExtractString(json[, indices_or_keys]…) #

Parse JSON and extract a string. This function is similar to the visitParamExtractString function.
If the value does not exist or the type is incorrect, it returns an empty string.
The value is not escaped. If unescaping fails, it returns an empty string.
Example:

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(json[, indices_or_keys…], Return_type) #

Parse JSON and extract the value of the given ClickHouse data type.
This is a variant of the previous JSONExtract<type> function. This means JSONExtract(…, 'String') returns exactly the same result as JSONExtractString() returns. JSONExtract(…, 'Float64') returns exactly the same result as JSONExtractFloat() returns.
Example:

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": [-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(json[, indices_or_keys…], Value_type) #

Parse key-value pairs from JSON, where the value is of the given ClickHouse data type.
Example:

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

JSONExtractRaw(json[, indices_or_keys]…) #

Returns a part of the JSON.
If the part does not exist or the type is incorrect, it returns an empty string.
Example:

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

JSON_EXISTS #

Checks whether the JSON path exists. 1 means it exists, 0 means it does not exist.
Syntax

JSON_EXISTS(json, path)

Param

• json — valid JSON. String/Object.
• path — JSON subelement path. String

Return value

• 1 means the path exists in the JSON, otherwise 0.

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_QUERY #

Parses the JSON object or JSON array pointed to by the path. If the path does not exist, it returns an empty string.
Syntax

JSON_QUERY(json, path)

Param

• json — a valid JSON. String/Object
• path — JSON path in string form. String.

Return value

• Returns the JSON object or JSON array in string form. If the path does not exist, it returns an empty 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 the scalar value of the JSON object pointed to by the JSON path. If it does not exist, it returns an empty string.

• function_json_value_return_type_allow_nullable = true, if the JSON path points to a complex type (such as struct, array, or map), it returns NULL.
• function_json_value_return_type_allow_complex = true, if the JSON path points to a complex type, it returns the value in string form.

Syntax

JSON_VALUE(json, path)

Param

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

Return value

• If the path exists, returns the scalar value in string form; otherwise, returns an empty 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 the parameter type to a JSON-formatted string.
Syntax

toJSONString(value)

Param

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

Return value

• JSON representation of the value. String.

Example
Query:

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

Result:

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

JSONArrayLength #

Returns the number of outermost elements in the JSON array.
Syntax

JSONArrayLength(json)

Alias: JSON_ARRAY_LENGTH(json).
Param

• json — valid JSON. String/Object

Return value

• Returns the number of outermost elements in the JSON array. If the JSON is invalid, returns NULL.

Example

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

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

get_json_object #

Returns
Syntax

get_json_object(json, path)

Param

• json — valid JSON. String/Object. If the JSON content contains escape characters, it will be considered invalid JSON, such as '{"test_key":"\ntest_value"}'
• path — subpath in JSON, in the form '$.key'

Return value

• If the JSON is of nullable type, returns Nullable(String); otherwise, returns String.

Example

select get_json_object('{"key":"value"}', '$.key'); --value 
select get_json_object(null, '$.key'); --NULL 
select get_json_object('{"key":"\nvalue"}', '$.key'); -- 
select get_json_object('{"key":"\nvalue"}', '$.key'); --\nvalue