Тип данных JSON - ClickHouse Documentation

Ищете руководство?

Ознакомьтесь с нашим руководством по лучшим практикам работы с JSON: в нём вы найдёте примеры, расширенные возможности и рекомендации по использованию типа JSON.

Тип JSON хранит документы JavaScript Object Notation (JSON) в одном столбце.

В ClickHouse Open-Source тип данных JSON считается готовым к использованию в продакшн, начиная с версии 25.3. В предыдущих версиях использовать этот тип в продакшн не рекомендуется.

Чтобы объявить столбец типа JSON, можно использовать следующий синтаксис:

<column_name> JSON
(
    max_dynamic_paths=N,
    max_dynamic_types=M,
    some.path TypeName,
    SKIP path.to.skip,
    SKIP REGEXP 'paths_regexp'
)

Где параметры в приведённом выше синтаксисе определены следующим образом:

Параметр	Описание	Значение по умолчанию
`max_dynamic_paths`	Необязательный параметр, указывающий, сколько путей может храниться отдельно в виде подстолбцов в пределах одного отдельно хранимого блока данных (например, в пределах одной части данных в таблице семейства MergeTree). Если этот предел превышен, все остальные пути будут храниться вместе в единой структуре, называемой общие данные. Также есть способы изменить лимит динамических путей без изменения этого параметра.	`1024`
`max_dynamic_types`	Необязательный параметр в диапазоне от `1` до `255`, указывающий, сколько различных типов данных может храниться отдельно внутри одного столбца пути с типом `Dynamic` в пределах одного отдельно хранимого блока данных (например, в пределах одной части данных в таблице семейства MergeTree). Если этот предел превышен, все новые типы будут храниться вместе в единой структуре с названием `shared variant`.	`32`
`some.path TypeName`	Необязательная подсказка типа для конкретного пути в JSON. Такие пути всегда будут храниться как подстолбцы с указанным типом.
`SKIP path.to.skip`	Необязательная подсказка для конкретного пути, который нужно пропускать при разборе JSON. Такие пути никогда не будут сохраняться в JSON-столбце. Если указанный путь представляет собой вложенный объект JSON, будет пропущен весь вложенный объект.
`SKIP REGEXP 'path_regexp'`	Необязательная подсказка с регулярным выражением, используемым для пропуска путей при разборе JSON. Все пути, соответствующие этому регулярному выражению, никогда не будут сохраняться в JSON-столбце.

Когда использовать тип `JSON`

Тип JSON предназначен для запросов, фильтрации и агрегации по отдельным полям в объектах JSON с динамической или непредсказуемой структурой. Для этого объекты JSON разбиваются на отдельные подстолбцы, что значительно уменьшает объём читаемых данных и ускоряет запросы по выбранным полям по сравнению с такими альтернативами, как Map или разбор строк. Однако у этого подхода есть важные недостатки:

Более медленные INSERT - Разбиение JSON на подстолбцы, определение типов и управление гибкими структурами хранения делают вставку медленнее по сравнению с хранением JSON в виде простого столбца String.
Медленнее при чтении объектов целиком - Если вам нужно получать JSON-документы целиком, а не отдельные поля, тип JSON работает медленнее, чем чтение из столбца String. Дополнительные затраты на восстановление объектов из отдельных подстолбцов не дают преимуществ, если вы не выполняете запрос по отдельным полям.
Дополнительные накладные расходы на хранение - Поддержка отдельных подстолбцов создаёт дополнительные структурные накладные расходы по сравнению с хранением JSON как одного строкового значения.

Используйте тип `JSON`, когда:

У ваших данных динамическая или непредсказуемая структура, а ключи различаются от документа к документу
Типы полей или схемы меняются со временем либо различаются между записями
Вам нужно выполнять запросы, фильтровать или агрегировать данные по определённым путям внутри объектов JSON, структуру которых невозможно заранее предсказать
Ваш сценарий предполагает работу с полуструктурированными данными, такими как журнал, события или пользовательский контент с непоследовательными схемами

Используйте столбец `String` (или структурированные типы), когда:

Структура ваших данных известна и стабильна — в этом случае лучше использовать обычные столбцы, типы Tuple, Array, Dynamic или Variant
Документы JSON рассматриваются как непрозрачные blob-объекты, которые только хранятся и извлекаются целиком, без анализа на уровне полей
Вам не нужно выполнять запросы или фильтровать данные по отдельным полям JSON в базе данных
JSON — это просто формат передачи/хранения, а не формат, который анализируется в ClickHouse

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

Создание `JSON`

В этом разделе мы рассмотрим различные способы создания JSON.

Использование `JSON` в определении столбца таблицы

Query (Example 1)

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response (Example 1)

┌─json────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"]}          │
│ {"f":"Hello, World!"}                       │
│ {"a":{"b":"43","e":"10"},"c":["4","5","6"]} │
└─────────────────────────────────────────────┘

Query (Example 2)

CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42}, "c" : [1, 2, 3]}'), ('{"f" : "Hello, World!"}'), ('{"a" : {"b" : 43, "e" : 10}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response (Example 2)

┌─json──────────────────────────────┐
│ {"a":{"b":42},"c":["1","2","3"]}  │
│ {"a":{"b":0},"f":"Hello, World!"} │
│ {"a":{"b":43},"c":["4","5","6"]}  │
└───────────────────────────────────┘

Использование CAST с `::JSON`

Различные типы можно преобразовывать с помощью специального синтаксиса ::JSON.

CAST из `String` в `JSON`

Query

SELECT '{"a" : {"b" : 42},"c" : [1, 2, 3], "d" : "Hello, World!"}'::JSON AS json;

Response

┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘

CAST из `Tuple` в `JSON`

Query

SET enable_named_columns_in_function_tuple = 1;
SELECT (tuple(42 AS b) AS a, [1, 2, 3] AS c, 'Hello, World!' AS d)::JSON AS json;

Response

┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘

CAST из `Map` в `JSON`

Query

SET use_variant_as_common_type=1;
SELECT map('a', map('b', 42), 'c', [1,2,3], 'd', 'Hello, World!')::JSON AS json;

Response

┌─json───────────────────────────────────────────────────┐
│ {"a":{"b":"42"},"c":["1","2","3"],"d":"Hello, World!"} │
└────────────────────────────────────────────────────────┘

JSON-пути хранятся в развёрнутом виде. Это означает, что, когда объект JSON формируется из пути вида a.b.c, невозможно определить, следует ли строить объект как { "a.b.c" : ... } или как { "a": { "b": { "c": ... } } }. В нашей реализации всегда предполагается второй вариант.Например:

Запрос

SELECT CAST('{"a.b.c" : 42}', 'JSON') AS json

вернёт:

Ответ

   ┌─json───────────────────┐
1. │ {"a":{"b":{"c":"42"}}} │
   └────────────────────────┘

а не:

   ┌─json───────────┐
1. │ {"a.b.c":"42"} │
   └────────────────┘

Чтение JSON-путей как подстолбцов

Тип JSON поддерживает чтение каждого JSON-пути как отдельного подстолбца. Если тип запрошенного пути не указан в объявлении типа JSON, то подстолбец этого пути всегда будет иметь тип Dynamic. Например:

Query

CREATE TABLE test (json JSON(a.b UInt32, SKIP a.e)) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : 42, "g" : 42.42}, "c" : [1, 2, 3], "d" : "2020-01-01"}'), ('{"f" : "Hello, World!", "d" : "2020-01-02"}'), ('{"a" : {"b" : 43, "e" : 10, "g" : 43.43}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response

┌─json────────────────────────────────────────────────────────┐
│ {"a":{"b":42,"g":42.42},"c":["1","2","3"],"d":"2020-01-01"} │
│ {"a":{"b":0},"d":"2020-01-02","f":"Hello, World!"}          │
│ {"a":{"b":43,"g":43.43},"c":["4","5","6"]}                  │
└─────────────────────────────────────────────────────────────┘

Query (Reading JSON paths as sub-columns)

SELECT json.a.b, json.a.g, json.c, json.d FROM test;

Response (Reading JSON paths as sub-columns)

┌─json.a.b─┬─json.a.g─┬─json.c──┬─json.d─────┐
│       42 │ 42.42    │ [1,2,3] │ 2020-01-01 │
│        0 │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ    │ 2020-01-02 │
│       43 │ 43.43    │ [4,5,6] │ ᴺᵁᴸᴸ       │
└──────────┴──────────┴─────────┴────────────┘

Вы также можете использовать функцию getSubcolumn, чтобы получать подстолбцы из типа JSON:

Query

SELECT getSubcolumn(json, 'a.b'), getSubcolumn(json, 'a.g'), getSubcolumn(json, 'c'), getSubcolumn(json, 'd') FROM test;

Response

┌─getSubcolumn(json, 'a.b')─┬─getSubcolumn(json, 'a.g')─┬─getSubcolumn(json, 'c')─┬─getSubcolumn(json, 'd')─┐
│                        42 │ 42.42                     │ [1,2,3]                 │ 2020-01-01              │
│                         0 │ ᴺᵁᴸᴸ                      │ ᴺᵁᴸᴸ                    │ 2020-01-02              │
│                        43 │ 43.43                     │ [4,5,6]                 │ ᴺᵁᴸᴸ                    │
└───────────────────────────┴───────────────────────────┴─────────────────────────┴─────────────────────────┘

Для доступа к JSON-путям также можно использовать синтаксис с квадратными скобками json['key']. Вложенный доступ поддерживается через цепочку обращений:

Query

SELECT json['a']['b'], json['c'], json['d'] FROM test;

Response

┌─arrayElement(arrayElement(json, 'a'), 'b')─┬─arrayElement(json, 'c')─┬─arrayElement(json, 'd')─┐
│ 42                                         │ [1,2,3]                 │ 2020-01-01              │
│ 0                                          │ ᴺᵁᴸᴸ                    │ 2020-01-02              │
│ 43                                         │ [4,5,6]                 │ ᴺᵁᴸᴸ                    │
└────────────────────────────────────────────┴─────────────────────────┴─────────────────────────┘

Синтаксис с квадратными скобками работает и для Nullable(JSON), возвращая то же значение и тот же тип, что и эквивалентный точечный синтаксис, и следуя тем же правилам nullability, что и json.key: путь, который может представлять NULL (Dynamic либо типизированный путь, который можно обернуть в Nullable), для строки со значением NULL даёт NULL, тогда как типизированный путь, не допускающий NULL, например Array или Map, сохраняет там своё значение по умолчанию. Цепочка обращений через квадратные скобки сворачивается в один JSON-путь, если включена настройка optimize_functions_to_subcolumns, поэтому json['a']['b'] читает путь a.b точно так же, как и json.a.b: в строке, где a содержит скаляр, а не объект, пути a.b нет, и результатом будет NULL. Без этой оптимизации внешнее обращение применяется к значению Dynamic выражения json['a'], и для такой строки действует настройка dynamic_throw_on_type_mismatch. Если запрошенный путь не был найден в данных, он будет заполнен значениями NULL:

Query

SELECT json.non.existing.path FROM test;

Response

┌─json.non.existing.path─┐
│ ᴺᵁᴸᴸ                   │
│ ᴺᵁᴸᴸ                   │
│ ᴺᵁᴸᴸ                   │
└────────────────────────┘

Проверим типы данных возвращаемых подстолбцов:

Query

SELECT toTypeName(json.a.b), toTypeName(json.a.g), toTypeName(json.c), toTypeName(json.d) FROM test;

Response

┌─toTypeName(json.a.b)─┬─toTypeName(json.a.g)─┬─toTypeName(json.c)─┬─toTypeName(json.d)─┐
│ UInt32               │ Dynamic              │ Dynamic            │ Dynamic            │
│ UInt32               │ Dynamic              │ Dynamic            │ Dynamic            │
│ UInt32               │ Dynamic              │ Dynamic            │ Dynamic            │
└──────────────────────┴──────────────────────┴────────────────────┴────────────────────┘

Как видно, для a.b используется тип UInt32, как и было указано в объявлении типа JSON, а для всех остальных подстолбцов используется тип Dynamic. Подстолбцы типа Dynamic также можно читать, используя специальный синтаксис json.some.path.:TypeName:

Query

SELECT
    json.a.g.:Float64,
    dynamicType(json.a.g),
    json.d.:Date,
    dynamicType(json.d)
FROM test

Response

┌─json.a.g.:`Float64`─┬─dynamicType(json.a.g)─┬─json.d.:`Date`─┬─dynamicType(json.d)─┐
│               42.42 │ Float64               │     2020-01-01 │ Date                │
│                ᴺᵁᴸᴸ │ None                  │     2020-01-02 │ Date                │
│               43.43 │ Float64               │           ᴺᵁᴸᴸ │ None                │
└─────────────────────┴───────────────────────┴────────────────┴─────────────────────┘

Подстолбцы Dynamic можно привести к любому типу данных. В этом случае будет сгенерировано исключение, если внутренний тип в Dynamic нельзя привести к запрошенному типу:

Query

SELECT json.a.g::UInt64 AS uint
FROM test;

Response

┌─uint─┐
│   42 │
│    0 │
│   43 │
└──────┘

Query

SELECT json.a.g::UUID AS float
FROM test;

Response

Received exception from server:
Code: 48. DB::Exception: Received from localhost:9000. DB::Exception:
Conversion between numeric types and UUID is not supported.
Probably the passed UUID is unquoted:
while executing 'FUNCTION CAST(__table1.json.a.g :: 2, 'UUID'_String :: 1) -> CAST(__table1.json.a.g, 'UUID'_String) UUID : 0'.
(NOT_IMPLEMENTED)

Чтобы эффективно читать подстолбцы из компактных частей MergeTree, убедитесь, что включена настройка MergeTree write_marks_for_substreams_in_compact_parts.

Чтение вложенных объектов JSON как подстолбцов

Тип JSON позволяет читать вложенные объекты как подстолбцы типа JSON с помощью специального синтаксиса json.^some.path:

Query

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : {"b" : {"c" : 42, "g" : 42.42}}, "c" : [1, 2, 3], "d" : {"e" : {"f" : {"g" : "Hello, World", "h" : [1, 2, 3]}}}}'), ('{"f" : "Hello, World!", "d" : {"e" : {"f" : {"h" : [4, 5, 6]}}}}'), ('{"a" : {"b" : {"c" : 43, "e" : 10, "g" : 43.43}}, "c" : [4, 5, 6]}');
SELECT json FROM test;

Response

┌─json──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":"42","g":42.42}},"c":["1","2","3"],"d":{"e":{"f":{"g":"Hello, World","h":["1","2","3"]}}}} │
│ {"d":{"e":{"f":{"h":["4","5","6"]}}},"f":"Hello, World!"}                                                 │
│ {"a":{"b":{"c":"43","e":"10","g":43.43}},"c":["4","5","6"]}                                               │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Query

SELECT json.^a.b, json.^d.e.f FROM test;

Response

┌─json.^`a`.b───────────────────┬─json.^`d`.e.f──────────────────────────┐
│ {"c":"42","g":42.42}          │ {"g":"Hello, World","h":["1","2","3"]} │
│ {}                            │ {"h":["4","5","6"]}                    │
│ {"c":"43","e":"10","g":43.43} │ {}                                     │
└───────────────────────────────┴────────────────────────────────────────┘

Когда пути хранятся в базовом (map) общих данных, чтение подстолбцов вложенных объектов может быть неэффективным, так как требует сканирования всей общей структуры данных. При сериализации общих данных map_with_buckets, advanced или advanced_chunked чтение подстолбцов из общих данных значительно оптимизировано.

Чтение комбинированных подстолбцов JSON

Тип JSON поддерживает чтение пути в виде комбинированного подстолбца с использованием специального синтаксиса json.@some.path. Комбинированный подстолбец для заданного пути возвращает:

Литеральное значение, хранящееся по этому пути, как Dynamic, если по этому пути есть литеральное значение.
Подобъект JSON по этому пути как Dynamic, если по этому пути нет литерального значения, но есть вложенные подпути.
NULL, если для этого пути не существует ни литерального значения, ни каких-либо подпутей.

Это полезно, когда в разных строках по одному и тому же пути может находиться либо скалярное значение, либо вложенный объект, и удобнее, чем отдельно выполнять запрос к литеральному подстолбцу (json.a) и подстолбцу подобъекта (json.^a). В следующем примере сравниваются все три типа подстолбцов для пути a:

Query

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES ('{"a" : 42, "b" : {"c" : 1, "d" : "Hello"}}'), ('{"a" : {"x": 1, "y": 2}, "b" : {"c" : 1}}'), ('{"c" : "World"}');
SELECT json FROM test;

Response

┌─json────────────────────────────┐
│ {"a":42,"b":{"c":1,"d":"Hello"}}│
│ {"a":{"x":1,"y":2},"b":{"c":1}}│
│ {"c":"World"}                   │
└─────────────────────────────────┘

Query

SELECT
    json.a,
    dynamicType(json.a),
    json.^a,
    toTypeName(json.^a),
    json.@a,
    dynamicType(json.@a)
FROM test;

Response

┌─json.a─┬─dynamicType(json.a)─┬─json.^a───────┬─toTypeName(json.^a)─┬─json.@a───────┬─dynamicType(json.@a)─┐
│ 42     │ Int64               │ {}            │ JSON                │ 42            │ Int64                │
│ NULL   │ None                │ {"x":1,"y":2} │ JSON                │ {"x":1,"y":2} │ JSON                 │
│ NULL   │ None                │ {}            │ JSON                │ NULL          │ None                 │
└────────┴─────────────────────┴───────────────┴─────────────────────┴───────────────┴──────────────────────┘

Строка 1: a содержит литерал 42. json.a возвращает его как Dynamic(Int64), json.^a возвращает пустой подобъект {} (у a нет вложенных ключей), а json.@a возвращает литерал 42.
Строка 2: a содержит вложенный объект. json.a возвращает NULL (по этому пути нет литерального значения), json.^a возвращает подобъект как JSON, а json.@a также возвращает подобъект как Dynamic(JSON).
Строка 3: a полностью отсутствует. И json.a, и json.@a возвращают NULL, а json.^a возвращает пустой объект {}.

Когда пути хранятся в базовых (map) общих данных, чтение комбинированных подстолбцов может быть неэффективным, поскольку требует сканирования всех общих данных. При сериализации общих данных map_with_buckets, advanced или advanced_chunked чтение подстолбцов из общих данных значительно оптимизировано.

Вывод типов для путей

При разборе JSON ClickHouse пытается определить наиболее подходящий тип данных для каждого JSON-пути. Это работает так же, как автоматическое определение схемы, и управляется теми же настройками:

Рассмотрим несколько примеров:

Query

SELECT JSONAllPathsWithTypes('{"a" : "2020-01-01", "b" : "2020-01-01 10:00:00"}'::JSON) AS paths_with_types settings input_format_try_infer_dates=1, input_format_try_infer_datetimes=1;

Response

┌─paths_with_types─────────────────┐
│ {'a':'Date','b':'DateTime64(9)'} │
└──────────────────────────────────┘

Query

SELECT JSONAllPathsWithTypes('{"a" : "2020-01-01", "b" : "2020-01-01 10:00:00"}'::JSON) AS paths_with_types settings input_format_try_infer_dates=0, input_format_try_infer_datetimes=0;

Response

┌─paths_with_types────────────┐
│ {'a':'String','b':'String'} │
└─────────────────────────────┘

Query

SELECT JSONAllPathsWithTypes('{"a" : [1, 2, 3]}'::JSON) AS paths_with_types settings schema_inference_make_columns_nullable=1;

Response

┌─paths_with_types───────────────┐
│ {'a':'Array(Nullable(Int64))'} │
└────────────────────────────────┘

Query

SELECT JSONAllPathsWithTypes('{"a" : [1, 2, 3]}'::JSON) AS paths_with_types settings schema_inference_make_columns_nullable=0;

Response

┌─paths_with_types─────┐
│ {'a':'Array(Int64)'} │
└──────────────────────┘

Обработка массивов объектов JSON

JSON-пути, содержащие массив объектов, разбираются как тип Array(JSON) и записываются в столбец Dynamic для этого пути. Чтобы прочитать массив объектов, его можно извлечь из столбца Dynamic как подстолбец:

Query

CREATE TABLE test (json JSON) ENGINE = Memory;
INSERT INTO test VALUES
('{"a" : {"b" : [{"c" : 42, "d" : "Hello", "f" : [[{"g" : 42.42}]], "k" : {"j" : 1000}}, {"c" : 43}, {"e" : [1, 2, 3], "d" : "My", "f" : [[{"g" : 43.43, "h" : "2020-01-01"}]],  "k" : {"j" : 2000}}]}}'),
('{"a" : {"b" : [1, 2, 3]}}'),
('{"a" : {"b" : [{"c" : 44, "f" : [[{"h" : "2020-01-02"}]]}, {"e" : [4, 5, 6], "d" : "World", "f" : [[{"g" : 44.44}]],  "k" : {"j" : 3000}}]}}');
SELECT json FROM test;

Response

┌─json────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"a":{"b":[{"c":"42","d":"Hello","f":[[{"g":42.42}]],"k":{"j":"1000"}},{"c":"43"},{"d":"My","e":["1","2","3"],"f":[[{"g":43.43,"h":"2020-01-01"}]],"k":{"j":"2000"}}]}} │
│ {"a":{"b":["1","2","3"]}}                                                                                                                                               │
│ {"a":{"b":[{"c":"44","f":[[{"h":"2020-01-02"}]]},{"d":"World","e":["4","5","6"],"f":[[{"g":44.44}]],"k":{"j":"3000"}}]}}                                                │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Query

SELECT json.a.b, dynamicType(json.a.b) FROM test;

Response

┌─json.a.b──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─dynamicType(json.a.b)────────────────────────────────────┐
│ ['{"c":"42","d":"Hello","f":[[{"g":42.42}]],"k":{"j":"1000"}}','{"c":"43"}','{"d":"My","e":["1","2","3"],"f":[[{"g":43.43,"h":"2020-01-01"}]],"k":{"j":"2000"}}'] │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │
│ [1,2,3]                                                                                                                                                           │ Array(Nullable(Int64))                                   │
│ ['{"c":"44","f":[[{"h":"2020-01-02"}]]}','{"d":"World","e":["4","5","6"],"f":[[{"g":44.44}]],"k":{"j":"3000"}}']                                                  │ Array(JSON(max_dynamic_types=16, max_dynamic_paths=256)) │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────┘

Как вы, возможно, заметили, параметры max_dynamic_types/max_dynamic_paths для вложенного типа JSON были уменьшены по сравнению со значениями по умолчанию. Это необходимо, чтобы число подстолбцов не росло бесконтрольно во вложенных массивах объектов JSON. Давайте попробуем прочитать подстолбцы из вложенного столбца JSON:

Query

SELECT json.a.b.:`Array(JSON)`.c, json.a.b.:`Array(JSON)`.f, json.a.b.:`Array(JSON)`.d FROM test;

Response

┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐
│ [42,43,NULL]              │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My']       │
│ []                        │ []                                                          │ []                        │
│ [44,NULL]                 │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]]                │ [NULL,'World']            │
└───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘

Можно не указывать имена подстолбцов Array(JSON), используя специальный синтаксис:

Query

SELECT json.a.b[].c, json.a.b[].f, json.a.b[].d FROM test;

Response

┌─json.a.b.:`Array(JSON)`.c─┬─json.a.b.:`Array(JSON)`.f───────────────────────────────────┬─json.a.b.:`Array(JSON)`.d─┐
│ [42,43,NULL]              │ [[['{"g":42.42}']],NULL,[['{"g":43.43,"h":"2020-01-01"}']]] │ ['Hello',NULL,'My']       │
│ []                        │ []                                                          │ []                        │
│ [44,NULL]                 │ [[['{"h":"2020-01-02"}']],[['{"g":44.44}']]]                │ [NULL,'World']            │
└───────────────────────────┴─────────────────────────────────────────────────────────────┴───────────────────────────┘

Количество [] после пути указывает на уровень массива. Например, json.path[][] будет преобразован в json.path.:Array(Array(JSON)) Давайте проверим пути и типы внутри нашего Array(JSON):

Query

SELECT DISTINCT arrayJoin(JSONAllPathsWithTypes(arrayJoin(json.a.b[]))) FROM test;

Response

┌─arrayJoin(JSONAllPathsWithTypes(arrayJoin(json.a.b.:`Array(JSON)`)))──┐
│ ('c','Int64')                                                         │
│ ('d','String')                                                        │
│ ('f','Array(Array(JSON(max_dynamic_types=8, max_dynamic_paths=64)))') │
│ ('k.j','Int64')                                                       │
│ ('e','Array(Nullable(Int64))')                                        │
└───────────────────────────────────────────────────────────────────────┘

Прочитаем подстолбцы из столбца Array(JSON):

Query

SELECT json.a.b[].c.:Int64, json.a.b[].f[][].g.:Float64, json.a.b[].f[][].h.:Date FROM test;

Response

┌─json.a.b.:`Array(JSON)`.c.:`Int64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.g.:`Float64`─┬─json.a.b.:`Array(JSON)`.f.:`Array(Array(JSON))`.h.:`Date`─┐
│ [42,43,NULL]                       │ [[[42.42]],[],[[43.43]]]                                     │ [[[NULL]],[],[['2020-01-01']]]                            │
│ []                                 │ []                                                           │ []                                                        │
│ [44,NULL]                          │ [[[NULL]],[[44.44]]]                                         │ [[['2020-01-02']],[[NULL]]]                               │
└────────────────────────────────────┴──────────────────────────────────────────────────────────────┴───────────────────────────────────────────────────────────┘

Из вложенного столбца JSON также можно читать подстолбцы подобъектов:

Query

SELECT json.a.b[].^k FROM test

Response

┌─json.a.b.:`Array(JSON)`.^`k`─────────┐
│ ['{"j":"1000"}','{}','{"j":"2000"}'] │
│ []                                   │
│ ['{}','{"j":"3000"}']                │
└──────────────────────────────────────┘

Обработка ключей JSON со значением NULL

В нашей реализации JSON null и отсутствие значения считаются эквивалентными:

Query

SELECT '{}'::JSON AS json1, '{"a" : null}'::JSON AS json2, json1 = json2

Response

┌─json1─┬─json2─┬─equals(json1, json2)─┐
│ {}    │ {}    │                    1 │
└───────┴───────┴──────────────────────┘

Это означает, что невозможно определить, содержали ли исходные данные JSON путь со значением NULL или он вообще отсутствовал.

Обработка ключей JSON с точками

Внутри JSON-столбца все пути и значения хранятся в уплощённом виде. Это означает, что по умолчанию эти 2 объекта считаются одинаковыми:

{"a" : {"b" : 42}}
{"a.b" : 42}

Оба они будут внутренне храниться как пара: путь a.b и значение 42. При форматировании JSON мы всегда формируем вложенные объекты на основе частей пути, разделённых точкой:

Query

SELECT '{"a" : {"b" : 42}}'::JSON AS json1, '{"a.b" : 42}'::JSON AS json2, JSONAllPaths(json1), JSONAllPaths(json2);

Response

┌─json1────────────┬─json2────────────┬─JSONAllPaths(json1)─┬─JSONAllPaths(json2)─┐
│ {"a":{"b":"42"}} │ {"a":{"b":"42"}} │ ['a.b']             │ ['a.b']             │
└──────────────────┴──────────────────┴─────────────────────┴─────────────────────┘

Как видите, исходный JSON {"a.b" : 42} теперь имеет вид {"a" : {"b" : 42}}. Это ограничение также приводит к ошибке при разборе корректных объектов JSON, таких как этот:

Query

SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json;

Response

Code: 117. DB::Exception: Cannot insert data into JSON column: Duplicate path found during parsing JSON object: a.b. You can enable setting type_json_skip_duplicated_paths to skip duplicated paths during insert: In scope SELECT CAST('{"a.b" : 42, "a" : {"b" : "Hello, World"}}', 'JSON') AS json. (INCORRECT_DATA)

Если вы хотите сохранить ключи с точками и не представлять их как вложенные объекты, можно включить настройку json_type_escape_dots_in_keys (доступна начиная с версии 25.8). В этом случае при парсинге все точки в ключах JSON будут экранироваться как %2E, а при formatting преобразовываться обратно.

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a" : {"b" : 42}}'::JSON AS json1, '{"a.b" : 42}'::JSON AS json2, JSONAllPaths(json1), JSONAllPaths(json2);

Response

┌─json1────────────┬─json2────────┬─JSONAllPaths(json1)─┬─JSONAllPaths(json2)─┐
│ {"a":{"b":"42"}} │ {"a.b":"42"} │ ['a.b']             │ ['a%2Eb']           │
└──────────────────┴──────────────┴─────────────────────┴─────────────────────┘

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, JSONAllPaths(json);

Response

┌─json──────────────────────────────────┬─JSONAllPaths(json)─┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ ['a%2Eb','a.b']    │
└───────────────────────────────────────┴────────────────────┘

Чтобы прочитать ключ с экранированной точкой как подстолбец, нужно использовать экранированную точку в имени подстолбца:

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, json.`a%2Eb`, json.a.b;

Response

┌─json──────────────────────────────────┬─json.a%2Eb─┬─json.a.b─────┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ 42         │ Hello World! │
└───────────────────────────────────────┴────────────┴──────────────┘

Примечание: из-за ограничений парсера идентификаторов и анализатора подстолбец json.`a.b` эквивалентен подстолбцу json.a.b и не сможет прочитать путь с экранированной точкой:

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON AS json, json.`a%2Eb`, json.`a.b`, json.a.b;

Response

┌─json──────────────────────────────────┬─json.a%2Eb─┬─json.a.b─────┬─json.a.b─────┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ 42         │ Hello World! │ Hello World! │
└───────────────────────────────────────┴────────────┴──────────────┴──────────────┘

Также, если вы хотите указать подсказку для JSON-пути, содержащего ключи с точками (или использовать её в разделах SKIP/SKIP REGEX), в подсказке необходимо экранировать точки:

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON(`a%2Eb` UInt8) as json, json.`a%2Eb`, toTypeName(json.`a%2Eb`);

Response

┌─json────────────────────────────────┬─json.a%2Eb─┬─toTypeName(json.a%2Eb)─┐
│ {"a.b":42,"a":{"b":"Hello World!"}} │         42 │ UInt8                  │
└─────────────────────────────────────┴────────────┴────────────────────────┘

Query

SET json_type_escape_dots_in_keys=1;
SELECT '{"a.b" : 42, "a" : {"b" : "Hello World!"}}'::JSON(SKIP `a%2Eb`) as json, json.`a%2Eb`;

Response

┌─json───────────────────────┬─json.a%2Eb─┐
│ {"a":{"b":"Hello World!"}} │ ᴺᵁᴸᴸ       │
└────────────────────────────┴────────────┘

Чтение типа JSON из данных

Все текстовые форматы (JSONEachRow, TSV, CSV, CustomSeparated, Values и т. д.) поддерживают чтение данных типа JSON. Примеры:

Query

SELECT json FROM format(JSONEachRow, 'json JSON(a.b.c UInt32, SKIP a.b.d, SKIP d.e, SKIP REGEXP \'b.*\')', '
{"json" : {"a" : {"b" : {"c" : 1, "d" : [0, 1]}}, "b" : "2020-01-01", "c" : 42, "d" : {"e" : {"f" : ["s1", "s2"]}, "i" : [1, 2, 3]}}}
{"json" : {"a" : {"b" : {"c" : 2, "d" : [2, 3]}}, "b" : [1, 2, 3], "c" : null, "d" : {"e" : {"g" : 43}, "i" : [4, 5, 6]}}}
{"json" : {"a" : {"b" : {"c" : 3, "d" : [4, 5]}}, "b" : {"c" : 10}, "e" : "Hello, World!"}}
{"json" : {"a" : {"b" : {"c" : 4, "d" : [6, 7]}}, "c" : 43}}
{"json" : {"a" : {"b" : {"c" : 5, "d" : [8, 9]}}, "b" : {"c" : 11, "j" : [1, 2, 3]}, "d" : {"e" : {"f" : ["s3", "s4"], "g" : 44}, "h" : "2020-02-02 10:00:00"}}}
')

Response

┌─json──────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":1}},"c":"42","d":{"i":["1","2","3"]}}          │
│ {"a":{"b":{"c":2}},"d":{"i":["4","5","6"]}}                   │
│ {"a":{"b":{"c":3}},"e":"Hello, World!"}                       │
│ {"a":{"b":{"c":4}},"c":"43"}                                  │
│ {"a":{"b":{"c":5}},"d":{"h":"2020-02-02 10:00:00.000000000"}} │
└───────────────────────────────────────────────────────────────┘

Для текстовых форматов, таких как CSV/TSV/и т. д., JSON разбирается из строки, содержащей объект JSON:

Query

SELECT json FROM format(TSV, 'json JSON(a.b.c UInt32, SKIP a.b.d, SKIP REGEXP \'b.*\')',
'{"a" : {"b" : {"c" : 1, "d" : [0, 1]}}, "b" : "2020-01-01", "c" : 42, "d" : {"e" : {"f" : ["s1", "s2"]}, "i" : [1, 2, 3]}}
{"a" : {"b" : {"c" : 2, "d" : [2, 3]}}, "b" : [1, 2, 3], "c" : null, "d" : {"e" : {"g" : 43}, "i" : [4, 5, 6]}}
{"a" : {"b" : {"c" : 3, "d" : [4, 5]}}, "b" : {"c" : 10}, "e" : "Hello, World!"}
{"a" : {"b" : {"c" : 4, "d" : [6, 7]}}, "c" : 43}
{"a" : {"b" : {"c" : 5, "d" : [8, 9]}}, "b" : {"c" : 11, "j" : [1, 2, 3]}, "d" : {"e" : {"f" : ["s3", "s4"], "g" : 44}, "h" : "2020-02-02 10:00:00"}}')

Response

┌─json──────────────────────────────────────────────────────────┐
│ {"a":{"b":{"c":1}},"c":"42","d":{"i":["1","2","3"]}}          │
│ {"a":{"b":{"c":2}},"d":{"i":["4","5","6"]}}                   │
│ {"a":{"b":{"c":3}},"e":"Hello, World!"}                       │
│ {"a":{"b":{"c":4}},"c":"43"}                                  │
│ {"a":{"b":{"c":5}},"d":{"h":"2020-02-02 10:00:00.000000000"}} │
└───────────────────────────────────────────────────────────────┘

Достижение предела динамических путей внутри JSON

Тип данных JSON может хранить только ограниченное количество путей во внутреннем представлении в виде отдельных подстолбцов. По умолчанию этот предел равен 1024, но его можно изменить в объявлении типа с помощью параметра max_dynamic_paths. Когда предел достигнут, все новые пути, вставляемые в столбец JSON, будут храниться в единой общей структуре данных. Такие пути по-прежнему можно читать как подстолбцы, но это может быть менее эффективно (см. раздел об общей структуре данных). Этот предел нужен, чтобы избежать появления огромного количества разных подстолбцов, из-за которых таблица может стать непригодной для использования. Давайте посмотрим, что происходит, когда этот предел достигается, в нескольких разных сценариях.

Достижение лимита при парсинге данных

При парсинге объектов JSON из данных, когда для текущего блока данных достигается лимит, все новые пути будут сохраняться в общей структуре данных. Можно использовать следующие две функции интроспекции: JSONDynamicPaths, JSONSharedDataPaths:

Query

SELECT json, JSONDynamicPaths(json), JSONSharedDataPaths(json) FROM format(JSONEachRow, 'json JSON(max_dynamic_paths=3)', '
{"json" : {"a" : {"b" : 42}, "c" : [1, 2, 3]}}
{"json" : {"a" : {"b" : 43}, "d" : "2020-01-01"}}
{"json" : {"a" : {"b" : 44}, "c" : [4, 5, 6]}}
{"json" : {"a" : {"b" : 43}, "d" : "2020-01-02", "e" : "Hello", "f" : {"g" : 42.42}}}
{"json" : {"a" : {"b" : 43}, "c" : [7, 8, 9], "f" : {"g" : 43.43}, "h" : "World"}}
')

Response

┌─json───────────────────────────────────────────────────────────┬─JSONDynamicPaths(json)─┬─JSONSharedDataPaths(json)─┐
│ {"a":{"b":"42"},"c":["1","2","3"]}                             │ ['a.b','c','d']        │ []                        │
│ {"a":{"b":"43"},"d":"2020-01-01"}                              │ ['a.b','c','d']        │ []                        │
│ {"a":{"b":"44"},"c":["4","5","6"]}                             │ ['a.b','c','d']        │ []                        │
│ {"a":{"b":"43"},"d":"2020-01-02","e":"Hello","f":{"g":42.42}}  │ ['a.b','c','d']        │ ['e','f.g']               │
│ {"a":{"b":"43"},"c":["7","8","9"],"f":{"g":43.43},"h":"World"} │ ['a.b','c','d']        │ ['f.g','h']               │
└────────────────────────────────────────────────────────────────┴────────────────────────┴───────────────────────────┘

Как видно, после вставки путей e и f.g лимит был достигнут, и они были вставлены в общую структуру данных.

При слиянии частей данных в движках таблиц MergeTree

При слиянии нескольких частей данных в таблице MergeTree столбец JSON в результирующей части данных может достичь лимита динамических путей и не сможет хранить все пути из исходных частей в виде подстолбцов. В этом случае ClickHouse определяет, какие пути останутся подстолбцами после слияния, а какие будут храниться в общей структуре данных. В большинстве случаев ClickHouse старается сохранить пути, содержащие наибольшее количество не NULL значений, а самые редкие пути переместить в общую структуру данных. Однако это зависит от реализации. Рассмотрим пример такого слияния. Сначала создадим таблицу со столбцом JSON, установим лимит динамических путей равным 3, а затем вставим значения с 5 различными путями:

Query

CREATE TABLE test (id UInt64, json JSON(max_dynamic_paths=3)) ENGINE=MergeTree ORDER BY id;
SYSTEM STOP MERGES test;
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as a) FROM numbers(5);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as b) FROM numbers(4);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as c) FROM numbers(3);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as d) FROM numbers(2);
INSERT INTO test SELECT number, formatRow('JSONEachRow', number as e) FROM numbers(1);

Каждая вставка создаст отдельную часть данных, в которой столбец JSON будет содержать только один путь:

Query

SELECT
    count(),
    groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
    groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
    _part
FROM test
GROUP BY _part
ORDER BY _part ASC

Response

┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│       5 │ ['a']         │ []                │ all_1_1_0 │
│       4 │ ['b']         │ []                │ all_2_2_0 │
│       3 │ ['c']         │ []                │ all_3_3_0 │
│       2 │ ['d']         │ []                │ all_4_4_0 │
│       1 │ ['e']         │ []                │ all_5_5_0 │
└─────────┴───────────────┴───────────────────┴───────────┘

Теперь объединим все части в одну и посмотрим, что получится:

Query

SELECT
    count(),
    groupArrayArrayDistinct(JSONDynamicPaths(json)) AS dynamic_paths,
    groupArrayArrayDistinct(JSONSharedDataPaths(json)) AS shared_data_paths,
    _part
FROM test
GROUP BY _part
ORDER BY _part ASC

Response

┌─count()─┬─dynamic_paths─┬─shared_data_paths─┬─_part─────┐
│      15 │ ['a','b','c'] │ ['d','e']         │ all_1_5_2 │
└─────────┴───────────────┴───────────────────┴───────────┘

Как видим, ClickHouse сохранил наиболее часто встречающиеся пути a, b и c, а пути d и e переместил в общую структуру данных.

Общая структура данных

Как описано в предыдущем разделе, при достижении ограничения max_dynamic_paths все новые пути сохраняются в одной общей структуре данных. В этом разделе мы подробнее рассмотрим общую структуру данных и то, как из неё читаются подстолбцы путей. Подробные сведения о функциях, используемых для анализа содержимого JSON-столбца, см. в разделе “функции интроспекции”.

Общая структура данных в памяти

В памяти общая структура данных — это просто подстолбец типа Map(String, String), который хранит соответствие между JSON-путём в плоском виде и значением, закодированным в бинарном виде. Чтобы извлечь из него подстолбец для пути, мы просто проходим по всем строкам в этом столбце Map и пытаемся найти запрошенный путь и его значения.

Общая структура данных в частях MergeTree

В таблицах MergeTree данные хранятся в частях данных, в которых всё записывается на диск (локальный или удалённый). При этом данные на диске могут храниться иначе, чем в памяти. Сейчас в частях данных MergeTree используются 4 разных варианта сериализации общей структуры данных: map, map_with_buckets, advanced и advanced_chunked. Версия сериализации определяется настройками MergeTree object_shared_data_serialization_version и object_shared_data_serialization_version_for_zero_level_parts (часть нулевого уровня — это часть, создаваемая при вставке данных в таблицу; при слиянии части получают более высокий уровень). Примечание: изменение сериализации общей структуры данных поддерживается только для v3 object serialization version

Map

В версии сериализации map общие данные сериализуются в виде одного столбца типа Map(String, String), так же, как они хранятся в памяти. Чтобы прочитать подстолбец по пути из этого типа сериализации, ClickHouse считывает весь столбец Map и извлекает нужный путь в памяти. Эта сериализация эффективна для записи данных и чтения всего JSON-столбца, но неэффективна для чтения подстолбцов по путям.

Map с бакетами

В версии сериализации map_with_buckets общие данные сериализуются как N столбцов («бакетов») типа Map(String, String). Каждый такой бакет содержит только подмножество путей. Чтобы прочитать подстолбец по пути из этого типа сериализации, ClickHouse считывает весь столбец Map из одного бакета и уже в памяти извлекает запрошенный путь. Эта сериализация менее эффективна для записи данных и чтения всего JSON-столбца, но более эффективна для чтения подстолбцов по путям, поскольку считывает данные только из нужных бакетов. Количество бакетов N задаётся настройками MergeTree object_shared_data_buckets_for_compact_part (по умолчанию 8) и object_shared_data_buckets_for_wide_part (по умолчанию 32). Максимально допустимое значение для обеих настроек — 256.

Продвинутая

В версии сериализации advanced общие данные сериализуются в специальную структуру данных, которая обеспечивает максимальную производительность чтения подстолбцов по путям за счёт хранения дополнительной информации, позволяющей читать только данные запрошенных путей. Эта сериализация также поддерживает бакеты, поэтому каждый бакет содержит только подмножество путей. Эта сериализация довольно неэффективна для записи данных (поэтому её не рекомендуется использовать для частей нулевого уровня), чтение всего JSON-столбца немного менее эффективно по сравнению с сериализацией map, но для чтения подстолбцов по путям она очень эффективна. Примечание: из-за хранения дополнительной информации внутри структуры данных объём данных на диске при использовании этой сериализации больше по сравнению с сериализациями map и map_with_buckets. Более подробный обзор новых сериализаций общих данных и подробности реализации см. в записи блога.

Продвинутая фрагментированная

Сериализация advanced_chunked аналогична advanced, но поддерживает разделение строк на более мелкие фрагменты при сериализации. Это снижает пиковое потребление памяти при слиянии JSON-столбцов со множеством уникальных путей, поскольку одновременно требуется материализовать данные лишь одного фрагмента, а не всего диапазона строк. Размер фрагмента задаётся настройкой MergeTree object_shared_data_target_chunk_rows (по умолчанию 8192). Это не жёсткое ограничение: если последний фрагмент будет меньше половины целевого размера, он объединяется с предыдущим фрагментом, поэтому фактический размер фрагментов находится в диапазоне от target/2 до 1.5 * target.

Управление количеством динамических путей внутри JSON в частях данных MergeTree

Основной способ задать ограничение на динамические пути в JSON — использовать параметр max_dynamic_paths в объявлении типа JSON. Однако изменение max_dynamic_paths для существующих столбцов требует выполнения ALTER TABLE <table> MODIFY COLUMN <column> JSON(max_dynamic_paths=K), что запустит фоновую мутацию, переписывающую все существующие части. Такая мутация может быть очень ресурсоемкой и может влиять на производительность сервера до ее завершения. Чтобы избежать этого, можно использовать следующие 3 настройки, которые позволяют изменить ограничение на динамические пути в таблицах семейства MergeTree для новых частей данных:

merge_max_dynamic_subcolumns_in_wide_part - настройка MergeTree, которая ограничивает количество динамических подстолбцов для каждого JSON-столбца при слиянии в часть данных Wide.
merge_max_dynamic_subcolumns_in_compact_part - настройка MergeTree, которая ограничивает количество динамических подстолбцов для каждого JSON-столбца при слиянии в часть данных Compact.
max_dynamic_subcolumns_in_json_type_parsing - настройка сеанса, которая ограничивает количество динамических подстолбцов для каждого JSON-столбца при разборе JSON-данных в JSON-столбец.

Примечание: ограничение на динамические пути не может превышать значение, указанное в параметре max_dynamic_paths, даже если значения описанных настроек выше.

Функции интроспекции

Есть несколько функций, которые помогают исследовать содержимое JSON-столбца:

Примеры Давайте исследуем содержимое датасета GH Archive за 2020-01-01:

Query

SELECT arrayJoin(distinctJSONPaths(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', NOSIGN, JSONAsObject)

Response

┌─arrayJoin(distinctJSONPaths(json))─────────────────────────┐
│ actor.avatar_url                                           │
│ actor.display_login                                        │
│ actor.gravatar_id                                          │
│ actor.id                                                   │
│ actor.login                                                │
│ actor.url                                                  │
│ created_at                                                 │
│ id                                                         │
│ org.avatar_url                                             │
│ org.gravatar_id                                            │
│ org.id                                                     │
│ org.login                                                  │
│ org.url                                                    │
│ payload.action                                             │
│ payload.before                                             │
│ payload.comment._links.html.href                           │
│ payload.comment._links.pull_request.href                   │
│ payload.comment._links.self.href                           │
│ payload.comment.author_association                         │
│ payload.comment.body                                       │
│ payload.comment.commit_id                                  │
│ payload.comment.created_at                                 │
│ payload.comment.diff_hunk                                  │
│ payload.comment.html_url                                   │
│ payload.comment.id                                         │
│ payload.comment.in_reply_to_id                             │
│ payload.comment.issue_url                                  │
│ payload.comment.line                                       │
│ payload.comment.node_id                                    │
│ payload.comment.original_commit_id                         │
│ payload.comment.original_position                          │
│ payload.comment.path                                       │
│ payload.comment.position                                   │
│ payload.comment.pull_request_review_id                     │
...
│ payload.release.node_id                                    │
│ payload.release.prerelease                                 │
│ payload.release.published_at                               │
│ payload.release.tag_name                                   │
│ payload.release.tarball_url                                │
│ payload.release.target_commitish                           │
│ payload.release.upload_url                                 │
│ payload.release.url                                        │
│ payload.release.zipball_url                                │
│ payload.size                                               │
│ public                                                     │
│ repo.id                                                    │
│ repo.name                                                  │
│ repo.url                                                   │
│ type                                                       │
└─arrayJoin(distinctJSONPaths(json))─────────────────────────┘

Query

SELECT arrayJoin(distinctJSONPathsAndTypes(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', NOSIGN, JSONAsObject)
SETTINGS date_time_input_format = 'best_effort'

Response

┌─arrayJoin(distinctJSONPathsAndTypes(json))──────────────────┐
│ ('actor.avatar_url',['String'])                             │
│ ('actor.display_login',['String'])                          │
│ ('actor.gravatar_id',['String'])                            │
│ ('actor.id',['Int64'])                                      │
│ ('actor.login',['String'])                                  │
│ ('actor.url',['String'])                                    │
│ ('created_at',['DateTime'])                                 │
│ ('id',['String'])                                           │
│ ('org.avatar_url',['String'])                               │
│ ('org.gravatar_id',['String'])                              │
│ ('org.id',['Int64'])                                        │
│ ('org.login',['String'])                                    │
│ ('org.url',['String'])                                      │
│ ('payload.action',['String'])                               │
│ ('payload.before',['String'])                               │
│ ('payload.comment._links.html.href',['String'])             │
│ ('payload.comment._links.pull_request.href',['String'])     │
│ ('payload.comment._links.self.href',['String'])             │
│ ('payload.comment.author_association',['String'])           │
│ ('payload.comment.body',['String'])                         │
│ ('payload.comment.commit_id',['String'])                    │
│ ('payload.comment.created_at',['DateTime'])                 │
│ ('payload.comment.diff_hunk',['String'])                    │
│ ('payload.comment.html_url',['String'])                     │
│ ('payload.comment.id',['Int64'])                            │
│ ('payload.comment.in_reply_to_id',['Int64'])                │
│ ('payload.comment.issue_url',['String'])                    │
│ ('payload.comment.line',['Int64'])                          │
│ ('payload.comment.node_id',['String'])                      │
│ ('payload.comment.original_commit_id',['String'])           │
│ ('payload.comment.original_position',['Int64'])             │
│ ('payload.comment.path',['String'])                         │
│ ('payload.comment.position',['Int64'])                      │
│ ('payload.comment.pull_request_review_id',['Int64'])        │
...
│ ('payload.release.node_id',['String'])                      │
│ ('payload.release.prerelease',['Bool'])                     │
│ ('payload.release.published_at',['DateTime'])               │
│ ('payload.release.tag_name',['String'])                     │
│ ('payload.release.tarball_url',['String'])                  │
│ ('payload.release.target_commitish',['String'])             │
│ ('payload.release.upload_url',['String'])                   │
│ ('payload.release.url',['String'])                          │
│ ('payload.release.zipball_url',['String'])                  │
│ ('payload.size',['Int64'])                                  │
│ ('public',['Bool'])                                         │
│ ('repo.id',['Int64'])                                       │
│ ('repo.name',['String'])                                    │
│ ('repo.url',['String'])                                     │
│ ('type',['String'])                                         │
└─arrayJoin(distinctJSONPathsAndTypes(json))──────────────────┘

ALTER MODIFY COLUMN в тип JSON

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

Query

CREATE TABLE test (json String) ENGINE=MergeTree ORDER BY tuple();
INSERT INTO test VALUES ('{"a" : 42}'), ('{"a" : 43, "b" : "Hello"}'), ('{"a" : 44, "b" : [1, 2, 3]}'), ('{"c" : "2020-01-01"}');
ALTER TABLE test MODIFY COLUMN json JSON;
SELECT json, json.a, json.b, json.c FROM test;

Response

┌─json─────────────────────────┬─json.a─┬─json.b──┬─json.c─────┐
│ {"a":"42"}                   │ 42     │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ       │
│ {"a":"43","b":"Hello"}       │ 43     │ Hello   │ ᴺᵁᴸᴸ       │
│ {"a":"44","b":["1","2","3"]} │ 44     │ [1,2,3] │ ᴺᵁᴸᴸ       │
│ {"c":"2020-01-01"}           │ ᴺᵁᴸᴸ   │ ᴺᵁᴸᴸ    │ 2020-01-01 │
└──────────────────────────────┴────────┴─────────┴────────────┘

Ленивые подсказки типов (бета)

Эта возможность находится в стадии бета, и для нее необходимо включить настройку enable_json_lazy_type_hints.

Когда вы добавляете или изменяете подсказки типов в JSON-столбце с помощью ALTER TABLE ... MODIFY COLUMN, ClickHouse обычно переписывает все части данных, чтобы материализовать новые подсказки типов. Для таблиц с большими объемами исторических данных (сотни терабайт) это может быть чрезвычайно затратно. Ленивые подсказки типов позволяют добавлять подсказки типов как операцию, затрагивающую только метаданные, без переписывания существующих данных:

Старые части: подсказки типов применяются во время выполнения запроса через приведение из Dynamic к указанному типу
Новые части: подсказки типов материализуются во время операций INSERT
Слияния: подсказки типов материализуются при слиянии частей

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

Включение ленивых подсказок типов

SET enable_json_lazy_type_hints = 1;

Пример

Query

-- Create a table and insert data
CREATE TABLE test_lazy (json JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO test_lazy VALUES ('{"user_id": "123", "score": "95.5"}');

-- Enable lazy type hints
SET enable_json_lazy_type_hints = 1;

-- Add type hints - this completes instantly without mutation
ALTER TABLE test_lazy MODIFY COLUMN json JSON(user_id UInt64, score Float64);

-- Query the data - type hints are applied at read time
SELECT json.user_id, toTypeName(json.user_id), json.score, toTypeName(json.score) FROM test_lazy;

Response

┌─json.user_id─┬─toTypeName(json.user_id)─┬─json.score─┬─toTypeName(json.score)─┐
│          123 │ UInt64                   │       95.5 │ Float64                │
└──────────────┴──────────────────────────┴────────────┴────────────────────────┘

Проверка отсутствия мутации

Вы можете убедиться, что ALTER завершился без мутации, проверив таблицу system.mutations:

SELECT * FROM system.mutations WHERE table = 'test_lazy' AND NOT is_done;

При включенных ленивых подсказках типов этот запрос не возвращает ни одной строки, что подтверждает, что операция затронула только метаданные. Это относится к описанным выше изменениям подсказок типов; сведения о случаях, которые вместо этого отклоняются, см. в разделе Ограничения.

Материализация подсказок типов

Чтобы материализовать подсказки типов в уже существующих данных, можно:

Дождаться фоновых слияний: ClickHouse автоматически материализует подсказки типов при слиянии частей
Принудительно запустить слияние: используйте OPTIMIZE TABLE test_lazy FINAL, чтобы сразу слить все части
Переписать части: используйте ALTER TABLE test_lazy REWRITE PARTS, чтобы переписать части с новыми метаданными

Ограничения

Преобразование типов при выполнении запроса может приводить к существенным накладным расходам по производительности по сравнению с заранее материализованными типами, особенно для крупных объектов JSON
Эта возможность работает только при изменении typed_paths (подсказок типов); другие параметры JSON, такие как max_dynamic_paths, SKIP или SKIP REGEXP, по-прежнему требуют мутаций

Изменение подсказки типа (или удаление типизированного пути) не является операцией только с метаданными и отклоняется, если затронутый подстолбец используется в структуре с позиционным хранением:
- в первичном ключе/ключе сортировки или ключе партиционирования — изменение запрещено, поскольку первичный индекс / значения партиции на диске нельзя перестроить с помощью ALTER, изменяющего только метаданные (как и для любого другого ключевого столбца);
- в явном индексе пропуска данных — сначала удалите индекс или отключите enable_json_lazy_type_hints, чтобы выполнить изменение как полную мутацию, перестраивающую индекс.
- в проекции, ключ сортировки (ORDER BY) которой считывает подстолбец — сначала удалите проекцию, поскольку ALTER, изменяющий только метаданные, не может перестроить первичный индекс проекции.
Добавление подсказок для путей, не используемых ни в одной такой структуре, или изменения, при которых тип используемых подстолбцов на диске остаётся неизменным (например, добавление несвязанного типизированного пути), по-прежнему являются операциями только с метаданными.

Сравнение значений типа JSON

Объекты JSON сравниваются аналогично значениям типа Map. Например:

Query

CREATE TABLE test (json1 JSON, json2 JSON) ENGINE=Memory;
INSERT INTO test FORMAT JSONEachRow
{"json1" : {}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : [1, 2, 3]}}
{"json1" : {"a" : 42}, "json2" : {"a" : "Hello"}}
{"json1" : {"a" : 42}, "json2" : {"b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 42, "b" : 42}}
{"json1" : {"a" : 42}, "json2" : {"a" : 41, "b" : 42}}

SELECT json1, json2, json1 < json2, json1 = json2, json1 > json2 FROM test;

Response

┌─json1──────┬─json2───────────────┬─less(json1, json2)─┬─equals(json1, json2)─┬─greater(json1, json2)─┐
│ {}         │ {}                  │                  0 │                    1 │                     0 │
│ {"a":"42"} │ {}                  │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"41"}          │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"42"}          │                  0 │                    1 │                     0 │
│ {"a":"42"} │ {"a":["1","2","3"]} │                  0 │                    0 │                     1 │
│ {"a":"42"} │ {"a":"Hello"}       │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"b":"42"}          │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"a":"42","b":"42"} │                  1 │                    0 │                     0 │
│ {"a":"42"} │ {"a":"41","b":"42"} │                  0 │                    0 │                     1 │
└────────────┴─────────────────────┴────────────────────┴──────────────────────┴───────────────────────┘

Примечание: если 2 пути содержат значения разных типов данных, они сравниваются в соответствии с правилом сравнения для типа данных Variant.

Индексы пропуска данных для JSON

Индексы пропуска данных можно использовать с JSON-столбцами тремя способами:

Индексы для конкретных подстолбцов — создайте стандартный индекс пропуска данных для известного JSON-пути, как и для обычного столбца. В этом случае индексируются значения по этому пути.
Индексы на основе путей с JSONAllPaths — индексируйте набор путей, присутствующих в каждой грануле, чтобы пропускать гранулы, в которых не может содержаться запрашиваемый путь.
Индексы на основе значений с JSONAllValues — индексируйте все значения по всем JSON-путям с помощью текстового индекса, чтобы ускорить полнотекстовый поиск по любому подстолбцу JSON с помощью одного индекса.

Индексы для отдельных подстолбцов

Вы можете создать индекс пропуска данных для любого подстолбца JSON, используя тот же синтаксис, что и для обычных столбцов. Поддерживается любой тип индекса (minmax, set, bloom_filter, tokenbf_v1, ngrambf_v1 и т. д.). Есть два способа указать подстолбец JSON в выражении индекса:

Типизированный путь, объявленный в подсказке типа JSON, — прямой доступ по имени: json.a.
Динамический путь с явным приведением типа — используйте синтаксис приведения ::: json.b::String.

Также можно использовать выражения, объединяющие несколько подстолбцов, например json.a || json.b::String.

Пример

Query

CREATE TABLE sensor_data
(
    data JSON(sensor_id UInt32),
    INDEX idx_sensor data.sensor_id TYPE minmax GRANULARITY 1,
    INDEX idx_location data.location::String TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;

INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);

Индекс minmax на типизированном подстолбце data.sensor_id сужает область сканирования до подходящих гранул:

Query

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id < 2;

Response

...
    Indexes:
      Skip
        Name: idx_sensor
        Description: minmax GRANULARITY 1
        Parts: 1/2
        Granules: 2/8

Индекс bloom_filter для приведённого подстолбца data.location::String также работает:

Query

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';

Response

...
    Indexes:
      Skip
        Name: idx_location
        Description: bloom_filter GRANULARITY 1
        Parts: 1/2
        Granules: 1/8

Индексы по путям с JSONAllPaths

Индексы пропуска данных также можно создавать для JSON-столбцов с помощью функции JSONAllPaths. Это работает так же, как создание индексов пропуска данных для столбцов Map через mapKeys: индекс хранит набор JSON-путей, присутствующих в каждой грануле, и использует его, чтобы пропускать гранулы, которые не могут содержать запрашиваемый путь.

Поддерживаемые типы индексов

JSONAllPaths можно использовать со следующими типами индексов пропуска данных:

bloom_filter — поддерживает equals, in и IS NOT NULL.
tokenbf_v1 — поддерживает equals и IS NOT NULL.
ngrambf_v1 — поддерживает equals и IS NOT NULL.
text (обратный индекс) — поддерживает equals, in и IS NOT NULL.

Пример

Query

CREATE TABLE events
(
    data JSON,
    INDEX idx JSONAllPaths(data) TYPE bloom_filter GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');

Вы можете использовать EXPLAIN indexes = 1, чтобы проверить, что индекс пропуска данных действительно используется. Если путь существует только в одной части данных, индекс пропускает другую часть:

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';

Response

...
    Indexes:
      Skip
        Name: idx
        Description: bloom_filter GRANULARITY 1
        Parts: 1/2
        Granules: 1/2

Если путь отсутствует во всех частях, все части и гранулы пропускаются:

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;

Response

...
    Indexes:
      Skip
        Name: idx
        Description: bloom_filter GRANULARITY 1
        Parts: 0/2
        Granules: 0/2

IS NOT NULL также использует индекс — он пропускает гранулы, в которых путь отсутствует (так как в этом случае значение было бы NULL):

Query

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;

Response

...
    Indexes:
      Skip
        Name: idx
        Description: bloom_filter GRANULARITY 1
        Parts: 1/2
        Granules: 1/2

Как это работает

Выражение JSONAllPaths(json_column) возвращает Array(String), содержащий все пути, присутствующие в значении JSON. Индекс пропуска данных хранит строки этих путей в своей структуре данных (фильтр Блума или обратный индекс). Когда в запросе используется фильтрация по json.some.path, индекс проверяет для каждой гранулы, есть ли в нём строка "some.path", и пропускает гранулы, где она отсутствует. С индексом сопоставляется только обычный доступ по пути, при необходимости с подсказкой типа или приведением типов (json.a.b, json.a.b.:Int64, json.a.b::String). Фильтры по подстолбцу подобъекта (json.^a) и по комбинированному подстолбцу literal+sub-object (json.@`a) не используют индекс: эти подстолбцы не равны NULL, если существует какой-либо вложенный путь a, поэтому наличие самого пути a в JSONAllPaths не является эквивалентным условием.

Безопасность при отсутствии путей

Если путь JSON отсутствует в грануле, подстолбец принимает значение:

NULL для типа Dynamic (например, json.path) и подстолбцов типа Nullable (например, json.path.:Int64) — сравнения с NULL всегда возвращают false, поэтому пропуск безопасен.
Значение по умолчанию этого типа для выражений CAST без Nullable (например, json.path::Int64 даёт 0, если путь отсутствует) — пропуск безопасен, только если сравниваемое значение отличается от значения по умолчанию. Индекс автоматически учитывает это различие.

Полнотекстовый поиск с JSONAllValues

Текстовые индексы можно использовать для ускорения полнотекстового поиска по JSON-столбцам с помощью функции JSONAllValues. JSONAllValues возвращает все значения из JSON-столбца в виде Array(String), который можно проиндексировать текстовым индексом. Один индекс на JSONAllValues(json_column) охватывает все JSON-пути, позволяя выполнять полнотекстовый поиск по любому подстолбцу без создания отдельных индексов для каждого пути. Подробнее и примеры см. в разделе Индексы на основе значений с JSONAllValues в документации по текстовым индексам.

Советы по более эффективному использованию типа JSON

Прежде чем создавать JSON-столбец и загружать в него данные, обратите внимание на следующие рекомендации:

Изучите свои данные и укажите как можно больше подсказок для путей с типами. Это сделает хранение и чтение данных гораздо эффективнее.
Продумайте, какие пути вам понадобятся, а какие — никогда. Укажите пути, которые вам не нужны, в разделе SKIP, а при необходимости — и в разделе SKIP REGEXP. Это повысит эффективность хранения.
Не задавайте параметру max_dynamic_paths слишком большие значения, так как это может снизить эффективность хранения и чтения. Хотя это сильно зависит от параметров системы, таких как память, CPU и т. д., в качестве общего практического правила не стоит устанавливать max_dynamic_paths выше 10 000 для хранения в локальной файловой системе и 1024 — для хранения в удалённой файловой системе.

Дополнительные материалы

Последнее изменение 26 сентября 2026 г.

Ищете руководство?

​Когда использовать тип JSON

​Используйте тип JSON, когда:

​Используйте столбец String (или структурированные типы), когда:

​Создание JSON

​Использование JSON в определении столбца таблицы

​Использование CAST с ::JSON

​CAST из String в JSON

​CAST из Tuple в JSON

​CAST из Map в JSON

​Чтение JSON-путей как подстолбцов

​Чтение вложенных объектов JSON как подстолбцов

​Чтение комбинированных подстолбцов JSON

​Вывод типов для путей

​Обработка массивов объектов JSON

​Обработка ключей JSON со значением NULL

​Обработка ключей JSON с точками

​Чтение типа JSON из данных

​Достижение предела динамических путей внутри JSON

​Достижение лимита при парсинге данных

​При слиянии частей данных в движках таблиц MergeTree

​Общая структура данных

​Общая структура данных в памяти

​Общая структура данных в частях MergeTree

​Map

​Map с бакетами

​Продвинутая

​Продвинутая фрагментированная

​Управление количеством динамических путей внутри JSON в частях данных MergeTree

​Функции интроспекции

​ALTER MODIFY COLUMN в тип JSON

​Ленивые подсказки типов (бета)

​Включение ленивых подсказок типов

​Пример

​Проверка отсутствия мутации

​Материализация подсказок типов

​Ограничения

​Сравнение значений типа JSON

​Индексы пропуска данных для JSON

​Индексы для отдельных подстолбцов

​Пример

​Индексы по путям с JSONAllPaths

​Поддерживаемые типы индексов

​Пример

​Как это работает

​Безопасность при отсутствии путей

​Полнотекстовый поиск с JSONAllValues

​Советы по более эффективному использованию типа JSON

​Дополнительные материалы

Когда использовать тип `JSON`

Используйте тип `JSON`, когда:

Используйте столбец `String` (или структурированные типы), когда:

Создание `JSON`

Использование `JSON` в определении столбца таблицы

Использование CAST с `::JSON`

CAST из `String` в `JSON`

CAST из `Tuple` в `JSON`

CAST из `Map` в `JSON`

Чтение JSON-путей как подстолбцов

Чтение вложенных объектов JSON как подстолбцов

Чтение комбинированных подстолбцов JSON

Вывод типов для путей

Обработка массивов объектов JSON

Обработка ключей JSON со значением NULL

Обработка ключей JSON с точками

Чтение типа JSON из данных

Достижение предела динамических путей внутри JSON

Достижение лимита при парсинге данных

При слиянии частей данных в движках таблиц MergeTree

Общая структура данных

Общая структура данных в памяти

Общая структура данных в частях MergeTree

Map

Map с бакетами

Продвинутая

Продвинутая фрагментированная

Управление количеством динамических путей внутри JSON в частях данных MergeTree

Функции интроспекции

ALTER MODIFY COLUMN в тип JSON

Ленивые подсказки типов (бета)

Включение ленивых подсказок типов

Пример

Проверка отсутствия мутации

Материализация подсказок типов

Ограничения

Сравнение значений типа JSON

Индексы пропуска данных для JSON

Индексы для отдельных подстолбцов

Пример

Индексы по путям с JSONAllPaths

Поддерживаемые типы индексов

Пример

Как это работает

Безопасность при отсутствии путей

Полнотекстовый поиск с JSONAllValues

Советы по более эффективному использованию типа JSON

Дополнительные материалы