Тип данных JSON

Документация по типу данных JSON в ClickHouse со встроенной поддержкой работы с 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 рассматриваются как непрозрачные 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]                 │ ᴺᵁᴸᴸ                    │
└───────────────────────────┴───────────────────────────┴─────────────────────────┴─────────────────────────┘

Если в данных не найден запрошенный путь, он будет заполнен значениями 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 type, а для всех остальных подстолбцов используется тип 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} │ {}                                     │
└───────────────────────────────┴────────────────────────────────────────┘

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

​

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

Тип JSON поддерживает чтение пути в виде комбинированного подстолбца с использованием специального синтаксиса [e-mail]. Комбинированный подстолбец для заданного пути возвращает:

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

​

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

При разборе 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

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

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

Версия сериализации определяется настройками 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.

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

​

Управление количеством динамических путей внутри 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-столбца:

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

Примеры

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

Query

SELECT arrayJoin(distinctJSONPaths(json))
FROM s3('s3://clickhouse-public-datasets/gharchive/original/2020-01-01-*.json.gz', 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', 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 │
└──────────────────────────────┴────────┴─────────┴────────────┘

​

Ленивые подсказки типов (экспериментальная возможность)

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

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

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

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

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

​

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

SET allow_experimental_json_lazy_type_hints = 1;

​

Пример

Query

-- Создать таблицу и вставить данные
CREATE TABLE test_lazy (json JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO test_lazy VALUES ('{"user_id": "123", "score": "95.5"}');

-- Включить экспериментальную настройку
SET allow_experimental_json_lazy_type_hints = 1;

-- Добавить подсказки типов — выполняется мгновенно без мутации
ALTER TABLE test_lazy MODIFY COLUMN json JSON(user_id UInt64, score Float64);

-- Запросить данные — подсказки типов применяются во время чтения
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;

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

​

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

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

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.

Например:

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-столбцами тремя способами:

1. Индексы для конкретных подстолбцов — создайте стандартный индекс пропуска данных для известного JSON-пути, как и для обычного столбца. В этом случае индексируются значения по этому пути.
2. Индексы на основе путей с JSONAllPaths — индексируйте набор путей, присутствующих в каждой грануле, чтобы пропускать гранулы, в которых не может содержаться запрашиваемый путь.
3. Индексы на основе значений с 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 отсутствует в грануле, подстолбец принимает значение:

• 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 — для хранения в удалённой файловой системе.

​

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

• Как мы создали новый мощный тип данных JSON для ClickHouse
• JSON-челлендж на миллиард документов: ClickHouse против MongoDB, Elasticsearch и других