JSON Functions - ClickHouse Documentation

Types of 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:

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

JSONExtract functions

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

Case-Insensitive JSONExtract Functions

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

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

JSONAllPaths

Introduced in: v24.8.0 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

Query

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;

Response

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

JSONAllPathsWithTypes

Introduced in: v24.8.0 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

Query

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;

Response

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

JSONAllValues

Introduced in: v26.4.0 Returns all values from each row in a JSON column as an array of strings. Values are serialized in their text representation and ordered by their path names. Syntax

JSONAllValues(json)

Arguments

json — JSON column. JSON

Returned value Returns an array of all values as strings in the JSON column. Array(String) Examples Usage example

Query

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, JSONAllValues(json) FROM test;

Response

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

JSONArrayLength

Introduced in: v23.2.0 Returns the number of elements in the outermost JSON array. The function returns NULL if input JSON string is invalid. Syntax

JSONArrayLength(json)

Aliases: JSON_ARRAY_LENGTH 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

Query

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

Response

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

JSONDynamicPaths

Introduced in: v24.8.0 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

Query

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;

Response

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

JSONDynamicPathsWithTypes

Introduced in: v24.8.0 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

Query

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;

Response

┌─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.0 Parses JSON and extracts a value with given ClickHouse data type. Syntax

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

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*
return_type — ClickHouse data type to return. String

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

Query

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

Response

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

JSONExtractArrayRaw

Introduced in: v20.1.0 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

Query

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

Response

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

JSONExtractArrayRawCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

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

JSONExtractBool

Introduced in: v20.1.0 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

Query

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

Response

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

JSONExtractBoolCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

JSONExtractCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

array_type

Query

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

Response

[1,2,3]

JSONExtractFloat

Introduced in: v20.1.0 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

Query

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

Response

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

JSONExtractFloatCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

12.34

JSONExtractInt

Introduced in: v20.1.0 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

Query

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

Response

┌──res─┐
│ -100 │
└──────┘

JSONExtractIntCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

nested

Query

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

Response

JSONExtractKeys

Introduced in: v21.11.0 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

Query

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

Response

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

JSONExtractKeysAndValues

Introduced in: v20.1.0 Parses key-value pairs from a JSON where the values are of the given ClickHouse data type. Syntax

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

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*
value_type — ClickHouse data type of the values. String

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

Query

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

Response

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

JSONExtractKeysAndValuesCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

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

JSONExtractKeysAndValuesRaw

Introduced in: v20.4.0 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

Query

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

Response

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

JSONExtractKeysAndValuesRawCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

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

JSONExtractKeysCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

['Name','AGE']

nested

Query

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

Response

['name','AGE']

JSONExtractRaw

Introduced in: v20.1.0 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

Query

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

Response

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

JSONExtractRawCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

{"key":"value"}

JSONExtractString

Introduced in: v20.1.0 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

Query

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

Response

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

JSONExtractStringCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

def

nested

Query

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

Response

John

JSONExtractUInt

Introduced in: v20.1.0 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

Query

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

Response

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

JSONExtractUIntCaseInsensitive

Introduced in: v25.8.0 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

Query

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

Response

JSONHas

Introduced in: v20.1.0 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

Query

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

Response

1
0

JSONKey

Introduced in: v20.1.0 Returns the key of a JSON object field by its index (1-based). If the JSON is passed as a string, it is parsed first. The second argument is a JSON path to navigate into nested objects. The function returns the key name at the specified position. Syntax

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

Arguments

json — JSON string to parse. String
indices_or_keys — Optional list of indices or keys specifying a path to a nested element. Each argument can be either a string (access by key) or an integer (access by index starting from 1). String or Int*

Returned value Returns the key name at the specified position in the JSON object. String Examples Usage example

Query

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

Response

JSONLength

Introduced in: v20.1.0 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

Query

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

Response

1
1

JSONMergePatch

Introduced in: v23.10.0 Returns the merged JSON object string which is formed by merging multiple JSON objects. Syntax

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

Aliases: jsonMergePatch 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

Query

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

Response

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

JSONSharedDataPaths

Introduced in: v24.8.0 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

Query

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;

Response

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

JSONSharedDataPathsWithTypes

Introduced in: v24.8.0 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

Query

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;

Response

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

JSONType

Introduced in: v20.1.0 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

Query

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

Response

1
1
1

JSON_EXISTS

Introduced in: v21.8.0 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

Query

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

Response

┌─JSON_EXISTS(⋯ '$.hello')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯llo.world')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[*]')─┐
│                        1 │
└──────────────────────────┘
┌─JSON_EXISTS(⋯.hello[0]')─┐
│                        1 │
└──────────────────────────┘

JSON_QUERY

Introduced in: v21.8.0 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

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

Response

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

JSON_VALUE

Introduced in: v21.11.0 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

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

Response

world
0
2
ᴺᵁᴸᴸ

dynamicElement

Introduced in: v24.1.0 Extracts a column with specified type from a Dynamic column. This function allows you to extract values of a specific type from a Dynamic column. If a row contains a value of the requested type, it returns that value. If the row contains a different type or NULL, it returns NULL for scalar types or an empty array for array types. Syntax

dynamicElement(dynamic, type_name)

Arguments

dynamic — Dynamic column to extract from. Dynamic
type_name — The name of the variant type to extract (e.g., ‘String’, ‘Int64’, ‘Array(Int64)’).

Returned value Returns values of the specified type from the Dynamic column. Returns NULL for non-matching types (or empty array for array types). Any Examples Extracting different types from Dynamic column

Query

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

Response

┌─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: v24.1.0 Returns the variant type name for each row of a Dynamic column. For rows containing NULL, the function returns ‘None’. For all other rows, it returns the actual data type stored in that row of the Dynamic column (e.g., ‘Int64’, ‘String’, ‘Array(Int64)’). Syntax

dynamicType(dynamic)

Arguments

dynamic — Dynamic column to inspect. Dynamic

Returned value Returns the type name of the value stored in each row, or ‘None’ for NULL values. String Examples Inspecting types in Dynamic column

Query

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

Response

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

isDynamicElementInSharedData

Introduced in: v24.1.0 Returns true for rows in a Dynamic column that are stored in shared variant format rather than as separate subcolumns. When a Dynamic column has a max_types limit, values that exceed this limit are stored in a shared binary format instead of being separated into individual typed subcolumns. This function identifies which rows are stored in this shared format. Syntax

isDynamicElementInSharedData(dynamic)

Arguments

dynamic — Dynamic column to inspect. Dynamic

Returned value Returns true if the value is stored in shared variant format, false if stored as a separate subcolumn or is NULL. Bool Examples Checking storage format in Dynamic column with max_types limit

Query

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;

Response

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

isValidJSON

Introduced in: v20.1.0 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

Query

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

Response

1
0

Using integers to access both JSON arrays and JSON objects

Query

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

Response

simpleJSONExtractBool

Introduced in: v21.4.0 Parses a true/false value from the value of the field named field_name. The result is UInt8. Syntax

simpleJSONExtractBool(json, field_name)

Aliases: visitParamExtractBool 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

Query

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;

Response

simpleJSONExtractFloat

Introduced in: v21.4.0 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)

Aliases: visitParamExtractFloat 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

Query

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;

Response

simpleJSONExtractInt

Introduced in: v21.4.0 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)

Aliases: visitParamExtractInt 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

Query

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;

Response

simpleJSONExtractRaw

Introduced in: v21.4.0 Returns the value of the field named field_name as a String, including separators. Syntax

simpleJSONExtractRaw(json, field_name)

Aliases: visitParamExtractRaw 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

Query

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;

Response

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

simpleJSONExtractString

Introduced in: v21.4.0 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)

Aliases: visitParamExtractString 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

Query

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;

Response

\n\0

☺

simpleJSONExtractUInt

Introduced in: v21.4.0 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)

Aliases: visitParamExtractUInt 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

Query

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;

Response

simpleJSONHas

Introduced in: v21.4.0 Checks whether there is a field named field_name. Syntax

simpleJSONHas(json, field_name)

Aliases: visitParamHas 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

Query

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;

Response

1
0

toJSONString

Introduced in: v21.7.0 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:

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

Query

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

Response

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

Special values

Query

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

Response

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

​Types of JSON functions

​simpleJSON (visitParam) functions

​JSONExtract functions

​Case-Insensitive JSONExtract Functions

​JSONAllPaths

​JSONAllPathsWithTypes

​JSONAllValues

​JSONArrayLength

​JSONDynamicPaths

​JSONDynamicPathsWithTypes

​JSONExtract

​JSONExtractArrayRaw

​JSONExtractArrayRawCaseInsensitive

​JSONExtractBool

​JSONExtractBoolCaseInsensitive

​JSONExtractCaseInsensitive

​JSONExtractFloat

​JSONExtractFloatCaseInsensitive

​JSONExtractInt

​JSONExtractIntCaseInsensitive

​JSONExtractKeys

​JSONExtractKeysAndValues

​JSONExtractKeysAndValuesCaseInsensitive

​JSONExtractKeysAndValuesRaw

​JSONExtractKeysAndValuesRawCaseInsensitive

​JSONExtractKeysCaseInsensitive

​JSONExtractRaw

​JSONExtractRawCaseInsensitive

​JSONExtractString

​JSONExtractStringCaseInsensitive

​JSONExtractUInt

​JSONExtractUIntCaseInsensitive

​JSONHas

​JSONKey

​JSONLength

​JSONMergePatch

​JSONSharedDataPaths

​JSONSharedDataPathsWithTypes

​JSONType

​JSON_EXISTS

​JSON_QUERY

​JSON_VALUE

​dynamicElement

​dynamicType

​isDynamicElementInSharedData

​isValidJSON

​simpleJSONExtractBool

​simpleJSONExtractFloat

​simpleJSONExtractInt

​simpleJSONExtractRaw

​simpleJSONExtractString

​simpleJSONExtractUInt

​simpleJSONHas

​toJSONString

Types of JSON functions

simpleJSON (visitParam) functions

JSONExtract functions

Case-Insensitive JSONExtract Functions

JSONAllPaths

JSONAllPathsWithTypes

JSONAllValues

JSONArrayLength

JSONDynamicPaths

JSONDynamicPathsWithTypes

JSONExtract

JSONExtractArrayRaw

JSONExtractArrayRawCaseInsensitive

JSONExtractBool

JSONExtractBoolCaseInsensitive

JSONExtractCaseInsensitive

JSONExtractFloat

JSONExtractFloatCaseInsensitive

JSONExtractInt

JSONExtractIntCaseInsensitive

JSONExtractKeys

JSONExtractKeysAndValues

JSONExtractKeysAndValuesCaseInsensitive

JSONExtractKeysAndValuesRaw

JSONExtractKeysAndValuesRawCaseInsensitive

JSONExtractKeysCaseInsensitive

JSONExtractRaw

JSONExtractRawCaseInsensitive

JSONExtractString

JSONExtractStringCaseInsensitive

JSONExtractUInt

JSONExtractUIntCaseInsensitive

JSONHas

JSONKey

JSONLength

JSONMergePatch

JSONSharedDataPaths

JSONSharedDataPathsWithTypes

JSONType

JSON_EXISTS

JSON_QUERY

JSON_VALUE

dynamicElement

dynamicType

isDynamicElementInSharedData

isValidJSON

simpleJSONExtractBool

simpleJSONExtractFloat

simpleJSONExtractInt

simpleJSONExtractRaw

simpleJSONExtractString

simpleJSONExtractUInt

simpleJSONHas

toJSONString