Функции JSON

Существует два набора функций для разбора JSON:

simpleJSON* (visitParam*), которые предназначены для быстрого разбора ограниченного подмножества JSON.
JSONExtract*, которые предназначены для разбора обычного JSON.

Функции simpleJSON (visitParam)

ClickHouse имеет специальные функции для работы с упрощенным JSON. Все эти функции JSON основаны на строгих предположениях о том, что может быть JSON. Они пытаются сделать как можно меньше, чтобы выполнить работу как можно быстрее.

Следующие предположения сделаны:

Имя поля (аргумент функции) должно быть константой.
Имя поля как-то канонически закодировано в JSON. Например: simpleJSONHas('{"abc":"def"}', 'abc') = 1, но simpleJSONHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
Поиск полей осуществляется на любом уровне вложения, без разбора. Если найдено несколько совпадающих полей, используется первое вхождение.
В JSON нет пробелов вне строковых литералов.

simpleJSONHas

Проверяет, существует ли поле с именем field_name. Результат — UInt8.

Синтаксис

simpleJSONHas(json, field_name)

Псевдоним: visitParamHas.

Параметры

json — JSON, в котором выполняется поиск поля. String
field_name — Имя поля для поиска. String literal

Возвращаемое значение

Возвращает 1, если поле существует, 0 в противном случае. UInt8.

Пример

Запрос:

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;

Результат:

1
0

simpleJSONExtractUInt

Парсит UInt64 из значения поля с именем field_name. Если это строковое поле, оно пытается разобрать число из начала строки. Если поле не существует или существует, но не содержит число, возвращает 0.

Синтаксис

simpleJSONExtractUInt(json, field_name)

Псевдоним: visitParamExtractUInt.

Параметры

json — JSON, в котором выполняется поиск поля. String
field_name — Имя поля для поиска. String literal

Возвращаемое значение

Возвращает число, разобраное из поля, если оно существует и содержит число, 0 в противном случае. UInt64.

Пример

Запрос:

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;

Результат:

simpleJSONExtractInt

Парсит Int64 из значения поля с именем field_name. Если это строковое поле, оно пытается разобрать число из начала строки. Если поле не существует или существует, но не содержит число, возвращает 0.

Синтаксис

simpleJSONExtractInt(json, field_name)

Псевдоним: visitParamExtractInt.

Параметры

json — JSON, в котором выполняется поиск поля. String
field_name — Имя поля для поиска. String literal

Возвращаемое значение

Возвращает число, разобраное из поля, если оно существует и содержит число, 0 в противном случае. Int64.

Пример

Запрос:

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;

Результат:

simpleJSONExtractFloat

Парсит Float64 из значения поля с именем field_name. Если это строковое поле, оно пытается разобрать число из начала строки. Если поле не существует или существует, но не содержит число, возвращает 0.

Синтаксис

simpleJSONExtractFloat(json, field_name)

Псевдоним: visitParamExtractFloat.

Параметры

json — JSON, в котором выполняется поиск поля. String
field_name — Имя поля для поиска. String literal

Возвращаемое значение

Возвращает число, разобраное из поля, если оно существует и содержит число, 0 в противном случае. Float64.

Пример

Запрос:

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;

Результат:

simpleJSONExtractBool

Парсит значение true/false из значения поля с именем field_name. Результат — UInt8.

Синтаксис

simpleJSONExtractBool(json, field_name)

Псевдоним: visitParamExtractBool.

Параметры

json — JSON, в котором выполняется поиск поля. String
field_name — Имя поля для поиска. String literal

Возвращаемое значение

Возвращает 1, если значение поля равно true, 0 в противном случае. Это означает, что эта функция вернёт 0 в следующих случаях:

Если поле не существует.
Если поле содержит true как строку, например: {"field":"true"}.
Если поле содержит 1 как числовое значение.

Пример

Запрос:

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;

Результат:

simpleJSONExtractRaw

Возвращает значение поля с именем field_name как String, включая разделители.

Синтаксис

simpleJSONExtractRaw(json, field_name)

Псевдоним: visitParamExtractRaw.

Параметры

json — JSON, в котором выполняется поиск поля. String
field_name — Имя поля для поиска. String literal

Возвращаемое значение

Возвращает значение поля как строку, включая разделители, если поле существует, или пустую строку в противном случае. String

Пример

Запрос:

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;

Результат:


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

simpleJSONExtractString

Парсит String в двойных кавычках из значения поля с именем field_name.

Синтаксис

simpleJSONExtractString(json, field_name)

Псевдоним: visitParamExtractString.

Параметры

json — JSON, в котором выполняется поиск поля. String
field_name — Имя поля для поиска. String literal

Возвращаемое значение

Возвращает неэкранированное значение поля как строку, включая разделители. Пустая строка возвращается, если поле не содержит строку в двойных кавычках, если раскодирование не удалось или если поле не существует. String.

Детали реализации

В настоящее время нет поддержки кодовых точек в формате \uXXXX\uYYYY, которые не являются из основной многоязычной плоскости (они конвертируются в CESU-8 вместо UTF-8).

Пример

Запрос:

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;

Результат:

\n\0

☺

Функции JSONExtract

Следующие функции основаны на simdjson и предназначены для более сложных требований к парсингу JSON.

isValidJSON

Проверяет, что переданная строка является валидным JSON.

Синтаксис

isValidJSON(json)

Примеры

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

JSONHas

Если значение существует в документе JSON, будет возвращено 1. Если значение не существует, будет возвращено 0.

Синтаксис

JSONHas(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает 1, если значение существует в json, иначе 0. UInt8.

Примеры

Запрос:

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 не существует. Вы можете использовать целые числа для доступа как к массивам JSON, так и к объектам JSON. Например:

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 или объекта JSON. Если значение не существует или имеет неверный тип, будет возвращено 0.

Синтаксис

JSONLength(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает длину массива JSON или объекта JSON. Возвращает 0, если значение не существует или имеет неверный тип. UInt64.

Примеры

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

JSONType

Возвращает тип значения JSON. Если значение не существует, будет возвращен Null=0 (не обычный Null, а Null=0 типа Enum8('Null' = 0, 'String' = 34,...).

Синтаксис

JSONType(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает тип значения JSON как строку, в противном случае, если значение не существует, вернёт Null=0. Enum.

Примеры

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 и извлекает значение типа UInt.

Синтаксис

JSONExtractUInt(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает значение типа UInt, если оно существует, иначе возвращает 0. UInt64.

Примеры

Запрос:

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

Результат:

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

JSONExtractInt

Парсит JSON и извлекает значение типа Int.

Синтаксис

JSONExtractInt(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает значение типа Int, если оно существует, иначе возвращает 0. Int64.

Примеры

Запрос:

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

Результат:

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

JSONExtractFloat

Парсит JSON и извлекает значение типа Float.

Синтаксис

JSONExtractFloat(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает значение типа Float, если оно существует, иначе возвращает 0. Float64.

Примеры

Запрос:

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

Результат:

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

JSONExtractBool

Парсит JSON и извлекает булевое значение. Если значение не существует или имеет неправильный тип, будет возвращено 0.

Синтаксис

JSONExtractBool(json[, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает булевое значение, если оно существует, иначе возвращает 0. Bool.

Пример

Запрос:

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

Результат:

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

JSONExtractString

Парсит JSON и извлекает строку. Эта функция похожа на функции visitParamExtractString. Если значение не существует или имеет неправильный тип, будет возвращена пустая строка.

Синтаксис

JSONExtractString(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает неэкранированную строку из json. Если раскодирование не удалось, если значение не существует или имеет неверный тип, то возвращает пустую строку. String.

Примеры

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 и извлекает значение заданного типа данных ClickHouse. Эта функция является обобщенной версией предыдущих функций JSONExtract<type>. Это означает:

JSONExtract(..., 'String') возвращает точно то же самое, что и JSONExtractString(), JSONExtract(..., 'Float64') возвращает точно то же самое, что и JSONExtractFloat().

Синтаксис

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

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.
return_type — Строка, указывающая тип значения для извлечения. String.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает значение, если оно существует указанного типа возвращаемого значения, иначе возвращает 0, Null или пустую строку в зависимости от указанного типа возвращаемого значения. UInt64, Int64, Float64, Bool или String.

Примеры

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'

Обращение к вложенным значениям с помощью передачи нескольких параметров indices_or_keys:

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

Результат:

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

JSONExtractKeysAndValues

Парсит пары ключ-значение из JSON, где значения имеют указанный тип данных ClickHouse.

Синтаксис

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

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.
value_type — Строка, указывающая тип значения для извлечения. String.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает массив разобранных пар ключ-значение. Array(Tuple(value_type)).

Пример

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

JSONExtractKeys

Парсит JSON-строку и извлекает ключи.

Синтаксис

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

Параметры

json — String с валидным JSON.
a, b, c... — Запятая, разделяющая индексы или ключи, которые указывают путь к внутреннему полю в вложенном объекте JSON. Каждый аргумент может быть либо String для получения поля по ключу, либо Integer для получения n-го поля (индексы с 1, отрицательные целые числа считаются с конца). Если не задано, весь JSON рассматривается как объект верхнего уровня. Необязательный параметр.

Возвращаемое значение

Возвращает массив с ключами JSON. Array(String).

Пример

Запрос:

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

Результат:

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

JSONExtractRaw

Возвращает часть JSON как неразобранную строку. Если часть не существует или имеет неверный тип, будет возвращена пустая строка.

Синтаксис

JSONExtractRaw(json [, indices_or_keys]...)

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возврат значения

Возвращает часть JSON в виде неразобранной строки. Если часть не существует или имеет неверный тип, возвращается пустая строка. String.

Пример

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

JSONExtractArrayRaw

Возвращает массив с элементами JSON массива, каждый из которых представлен как неразобранная строка. Если часть не существует или не является массивом, будет возвращен пустой массив.

Синтаксис

JSONExtractArrayRaw(json [, indices_or_keys...])

Параметры

json — JSON-строка для разбора. String.
indices_or_keys — Список из нуля или более аргументов, каждый из которых может быть либо строкой, либо целым числом. String, Int*.

indices_or_keys тип:

String = доступ к объекту по ключу.
Положительное целое число = доступ к n-му члену/ключу с начала.
Отрицательное целое число = доступ к n-му члену/ключу с конца.

Возвращаемое значение

Возвращает массив с элементами JSON массива, каждый из которых представлен как неразобранная строка. В противном случае, если часть не существует или не является массивом, возвращается пустой массив. Array(String).

Пример

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

JSONExtractKeysAndValuesRaw

Извлекает необработанные данные из объекта JSON.

Синтаксис

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

Аргументы

json — String с валидным JSON.
p, a, t, h — Запятая, разделяющая индексы или ключи, которые указывают путь к внутреннему полю в вложенном объекте JSON. Каждый аргумент может быть либо строкой, чтобы получить поле по ключу, либо целым числом, чтобы получить n-й член (индексируемый с 1, отрицательные целые числа считаются с конца). Если не установлено, весь JSON парсится как объект верхнего уровня. Необязательный параметр.

Возвращаемые значения

Массив с кортежами ('key', 'value'). Оба члена кортежа являются строками. Array(Tuple(String, String)).
Пустой массив, если запрашиваемый объект не существует или входной JSON недействителен. Array(Tuple(String, String)).

Примеры

Запрос:

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

Результат:

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

Запрос:

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

Результат:

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

Запрос:

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

Результат:

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

JSON_EXISTS

Синтаксис

JSON_EXISTS(json, path)

Параметры

json — Строка с валидным JSON. String.
path — Строка, представляющая путь. String.

примечание

До версии 21.11 порядок аргументов был неверен, т.е. JSON_EXISTS(path, json)

Возвращаемое значение

Возвращает 1, если значение существует в документе JSON, иначе 0.

Примеры

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

Парсит JSON и извлекает значение как JSON массив или JSON объект. Если значение не существует, будет возвращена пустая строка.

Синтаксис

JSON_QUERY(json, path)

Параметры

json — Строка с валидным JSON. String.
path — Строка, представляющая путь. String.

примечание

До версии 21.11 порядок аргументов был неверен, т.е. JSON_EXISTS(path, json)

Возвращаемое значение

Возвращает извлечённое значение в виде JSON массива или JSON объекта. В противном случае возвращается пустая строка, если значение не существует. String.

Пример

Запрос:

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

Парсит JSON и извлекает значение в виде скаляра JSON. Если значение не существует, по умолчанию будет возвращена пустая строка.

Эта функция контролируется следующими настройками:

при установке SET function_json_value_return_type_allow_nullable = true, будет возвращен NULL. Если значение является сложным типом (например: структура, массив, карта), по умолчанию будет возвращена пустая строка.
при установке SET function_json_value_return_type_allow_complex = true, будет возвращено сложное значение.

Синтаксис

JSON_VALUE(json, path)

Параметры

json — строка с корректным JSON. String.
path — строка, представляющая путь. String.

примечание

Перед версией 21.11 порядок аргументов был неверным, т.е. JSON_EXISTS(path, json)

Возвращаемое значение

Возвращает извлеченное значение в виде скаляра JSON, если оно существует, в противном случае возвращается пустая строка. String.

Пример

Запрос:

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;

Результат:

world
0
2
String

toJSONString

Сериализует значение в его JSON представление. Поддерживаются различные типы данных и вложенные структуры. 64-битные целые числа или больше (такие как UInt64 или Int128) по умолчанию заключаются в кавычки. output_format_json_quote_64bit_integers контролирует это поведение. Специальные значения NaN и inf заменяются на null. Включите настройку output_format_json_quote_denormals, чтобы показать их. При сериализации значения Enum функция выводит его имя.

Синтаксис

toJSONString(value)

Аргументы

value — значение для сериализации. Значение может быть любого типа данных.

Возвращаемое значение

JSON представление значения. String.

Пример

Первый пример показывает сериализацию Map. Второй пример показывает некоторые специальные значения, обернутые в Tuple.

Запрос:

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

Результат:

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

Смотрите также

JSONArrayLength

Возвращает количество элементов в самом внешнем массиве JSON. Функция возвращает NULL, если входная строка JSON недействительна.

Синтаксис

JSONArrayLength(json)

Псевдоним: JSON_ARRAY_LENGTH(json).

Аргументы

json — String с корректным JSON.

Возвращаемое значение

Если json является корректной строкой массива JSON, возвращает количество элементов массива, в противном случае возвращает NULL. Nullable(UInt64).

Пример

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

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

jsonMergePatch

Возвращает объединенную строку JSON-объекта, сформированную путем объединения нескольких JSON-объектов.

Синтаксис

jsonMergePatch(json1, json2, ...)

Аргументы

json — String с корректным JSON.

Возвращаемое значение

Если строки JSON-объектов корректны, возвращает объединенную строку JSON-объекта. String.

Пример

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

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

JSONAllPaths

Возвращает список всех путей, хранящихся в каждой строке в колонке JSON.

Синтаксис

JSONAllPaths(json)

Аргументы

json — JSON.

Возвращаемое значение

Массив путей. Array(String).

Пример

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

Возвращает таблицу всех путей и их типов данных, хранящихся в каждой строке в колонке JSON.

Синтаксис

JSONAllPathsWithTypes(json)

Аргументы

json — JSON.

Возвращаемое значение

Массив путей. Map(String, String).

Пример

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

Возвращает список динамических путей, которые хранятся как отдельные подколонки в колонке JSON.

Синтаксис

JSONDynamicPaths(json)

Аргументы

json — JSON.

Возвращаемое значение

Массив путей. Array(String).

Пример

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

Возвращает таблицу динамических путей, которые хранятся как отдельные подколонки и их типы в каждой строке в колонке JSON.

Синтаксис

JSONAllPathsWithTypes(json)

Аргументы

json — JSON.

Возвращаемое значение

Массив путей. Map(String, String).

Пример

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

Возвращает список путей, которые хранятся в общей структуре данных в колонке JSON.

Синтаксис

JSONSharedDataPaths(json)

Аргументы

json — JSON.

Возвращаемое значение

Массив путей. Array(String).

Пример

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

Возвращает таблицу путей, которые хранятся в общей структуре данных и их типы в каждой строке в колонке JSON.

Синтаксис

JSONSharedDataPathsWithTypes(json)

Аргументы

json — JSON.

Возвращаемое значение

Массив путей. Map(String, String).

Пример

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

Функции simpleJSON (visitParam)​

simpleJSONHas​

simpleJSONExtractUInt​

simpleJSONExtractInt​

simpleJSONExtractFloat​

simpleJSONExtractBool​

simpleJSONExtractRaw​

simpleJSONExtractString​

Функции JSONExtract​

isValidJSON​

JSONHas​

JSONLength​

JSONType​

JSONExtractUInt​

JSONExtractInt​

JSONExtractFloat​

JSONExtractBool​

JSONExtractString​

JSONExtract​

JSONExtractKeysAndValues​

JSONExtractKeys​

JSONExtractRaw​

JSONExtractArrayRaw​

JSONExtractKeysAndValuesRaw​

JSON_EXISTS​

JSON_QUERY​

JSON_VALUE​

toJSONString​

JSONArrayLength​

jsonMergePatch​

JSONAllPaths​

JSONAllPathsWithTypes​

JSONDynamicPaths​

JSONDynamicPathsWithTypes​

JSONSharedDataPaths​

JSONSharedDataPathsWithTypes​

Функции simpleJSON (visitParam)

simpleJSONHas

simpleJSONExtractUInt

simpleJSONExtractInt

simpleJSONExtractFloat

simpleJSONExtractBool

simpleJSONExtractRaw

simpleJSONExtractString

Функции JSONExtract

isValidJSON

JSONHas

JSONLength

JSONType

JSONExtractUInt

JSONExtractInt

JSONExtractFloat

JSONExtractBool

JSONExtractString

JSONExtract

JSONExtractKeysAndValues

JSONExtractKeys

JSONExtractRaw

JSONExtractArrayRaw

JSONExtractKeysAndValuesRaw

JSON_EXISTS

JSON_QUERY

JSON_VALUE

toJSONString

JSONArrayLength

jsonMergePatch

JSONAllPaths

JSONAllPathsWithTypes

JSONDynamicPaths

JSONDynamicPathsWithTypes

JSONSharedDataPaths

JSONSharedDataPathsWithTypes