Тип данных JSON

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

Ознакомьтесь с нашим руководством по лучшим практикам работы с 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 рассматриваются как непрозрачные двоичные объекты, которые только сохраняются и извлекаются целиком без анализа на уровне полей
• Вам не нужно выполнять запросы или фильтрацию по отдельным полям JSON в базе данных
• JSON используется только как формат передачи/хранения и не анализируется в ClickHouse

Совет

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

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

Создание JSON

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

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

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;

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

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;

┌─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

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

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

CAST из типа Tuple в JSON

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;

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

Приведение типа из Map к JSON

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

┌─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, то подстолбец для этого пути всегда будет иметь тип Dynamic.

Например:

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;

┌─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"]}                  │
└─────────────────────────────────────────────────────────────┘

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

┌─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:

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

┌─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]                 │ ᴺᵁᴸᴸ                    │
└───────────────────────────┴───────────────────────────┴─────────────────────────┴─────────────────────────┘

Если запрошенный путь отсутствует в данных, он будет заполнен значениями NULL:

SELECT json.non.existing.path FROM test;

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

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

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

┌─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:

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

┌─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 нельзя привести к запрошенному типу:

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

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

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

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)

Примечание

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

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

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

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;

┌─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"]}                                               │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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

┌─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 для сериализации общих данных чтение подстолбцов из общих данных значительно оптимизировано.

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

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

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

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

В следующем примере сравниваются все три типа подстолбцов для пути a:

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;

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

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

┌─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 чтение подстолбцов из общей структуры данных значительно оптимизировано.

Определение типов для путей

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

• input_format_try_infer_dates
• input_format_try_infer_datetimes
• schema_inference_make_columns_nullable
• input_format_json_try_infer_numbers_from_strings
• input_format_json_infer_incomplete_types_as_strings
• input_format_json_read_numbers_as_strings
• input_format_json_read_bools_as_strings
• input_format_json_read_bools_as_numbers
• input_format_json_read_arrays_as_strings
• input_format_json_infer_array_of_dynamic_from_array_of_different_types

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

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;

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

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;

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

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

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

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

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

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

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

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;

┌─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"}}]}}                                                │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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

┌─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:

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

┌─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), используя специальный синтаксис:

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

┌─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):

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

┌─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):

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

┌─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:

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

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

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

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

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

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

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

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

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

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

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

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

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

Как видите, исходный JSON {"a.b" : 42} теперь имеет вид {"a" : {"b" : 42}}.

Это ограничение также приводит к ошибке при разборе корректных JSON-объектов, подобных этому:

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

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 и разэкранированы обратно при форматировании.

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

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

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

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

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

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──────────────────────────────────┬─json.a%2Eb─┬─json.a.b─────┐
│ {"a.b":"42","a":{"b":"Hello World!"}} │ 42         │ Hello World! │
└───────────────────────────────────────┴────────────┴──────────────┘

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

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;

┌─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), необходимо экранировать точки в подсказке:

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

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

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

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

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

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

Примеры:

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"}}}
')

┌─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:

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"}}')

┌─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:

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"}}
')

┌─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 старается сохранить пути, содержащие наибольшее количество ненулевых значений, и перенести самые редкие пути в общую структуру данных. Однако конкретное поведение зависит от реализации.

Рассмотрим пример такого слияния. Сначала создадим таблицу со столбцом JSON, установим лимит динамических путей равным 3, а затем вставим значения с 5 различными путями:

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, содержащим один путь:

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

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

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

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

┌─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 используются три разных варианта сериализации общей структуры данных: map, map_with_buckets и advanced.

Версия сериализации управляется настройками MergeTree object_shared_data_serialization_version и object_shared_data_serialization_version_for_zero_level_parts (часть нулевого уровня — это часть, создаваемая при вставке данных в таблицу; во время слияний части получают более высокий уровень).

Примечание: изменение формата сериализации общей структуры данных поддерживается только для v3 версии сериализации объектов

Map

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

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

Map with buckets

В версии сериализации 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

В версии сериализации advanced общие данные сериализуются в специальной структуре данных, которая максимально повышает производительность чтения подстолбцов путей за счёт хранения дополнительной информации, позволяющей читать только данные запрошенных путей. Эта сериализация также поддерживает бакеты, поэтому каждый бакет содержит только подмножество путей.

Эта сериализация довольно неэффективна для записи данных (поэтому не рекомендуется использовать её для частей нулевого уровня), чтение всего столбца JSON немного менее эффективно по сравнению с сериализацией map, но она очень эффективна для чтения подстолбцов путей.

Примечание: из-за хранения дополнительной информации внутри структуры данных размер занимаемого дискового пространства при этой сериализации больше по сравнению с сериализациями map и map_with_buckets.

Более подробный обзор новых сериализаций общей структуры данных и деталей реализации см. в публикации в блоге.

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

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

• JSONAllPaths
• JSONAllPathsWithTypes
• JSONDynamicPaths
• JSONDynamicPathsWithTypes
• JSONSharedDataPaths
• JSONSharedDataPathsWithTypes
• distinctDynamicTypes
• distinctJSONPaths and distinctJSONPathsAndTypes

Примеры

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

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

┌─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))─────────────────────────┘

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

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

Пример

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;

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

Lazy Type Hints (экспериментально)

Примечание

Эта функция является экспериментальной и требует включения настройки allow_experimental_json_lazy_type_hints.

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

Lazy type hints позволяют добавлять подсказки типов как операцию, затрагивающую только метаданные, без перезаписи существующих данных:

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

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

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

SET allow_experimental_json_lazy_type_hints = 1;

Пример

-- 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 experimental setting
SET allow_experimental_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;

┌─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;

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

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

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

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

Ограничения

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

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

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

Например:

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;

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

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

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

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

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

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

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

Есть два способа указать подстолбец JSON в выражении индекса:

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

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

Пример

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 ограничивает сканирование соответствующими гранулами:

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

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

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

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

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

Пример

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, чтобы убедиться, что индекс пропуска данных используется. Если путь существует только в одной части, индекс пропускает другую часть:

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

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

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

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

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

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

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

...
    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 отсутствует в грануле, подстолбец вычисляется как:

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

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

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

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

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

• Как мы разработали новый мощный тип данных JSON для ClickHouse
• Испытание «миллиард JSON‑документов»: ClickHouse против MongoDB, Elasticsearch и других