Перейти к основному содержимому
Перейти к основному содержимому

Тип данных JSON

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

Если вы хотите использовать тип JSON и для примеров на этой странице, пожалуйста, используйте:

:::

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

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

ПараметрОписаниеЗначение по умолчанию
max_dynamic_pathsНеобязательный параметр, указывающий, сколько путей может храниться отдельно в виде под-колонок в одном блоке данных, который хранится отдельно (например, в одной части данных для таблицы MergeTree).

Если этот лимит превышен, все остальные пути будут храниться вместе в одной структуре.		1024
max_dynamic_typesНеобязательный параметр от 1 до 255, указывающий, сколько различных типов данных может храниться внутри одной колонки пути с типом Dynamic в одном блоке данных, который хранится отдельно (например, в одной части данных для таблицы MergeTree).

Если этот лимит превышен, все новые типы будут преобразованы в тип String.		32
some.path TypeNameНеобязательная подсказка типа для конкретного пути в JSON. Такие пути всегда будут храниться как под-колонки с указанным типом.
SKIP path.to.skipНеобязательная подсказка для конкретного пути, который следует пропустить во время парсинга JSON. Такие пути никогда не будут храниться в колонке JSON. Если указанный путь является вложенным JSON объектом, вся вложенная структура будет пропущена.
SKIP REGEXP 'path_regexp'Необязательная подсказка с регулярным выражением, которое используется для пропуска путей во время парсинга JSON. Все пути, соответствующие этому регулярному выражению, никогда не будут храниться в колонке JSON.

Создание JSON​

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

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

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

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

CAST из String в JSON​

CAST из Tuple в JSON​

CAST из Map в JSON​

CAST из устаревшего Object('json') в JSON​

примечание

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

Например:

вернет:

и не:

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

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

Например:

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

Давайте проверим типы данных возвращаемых под-колонок:

Как мы видим, для a.b тип — UInt32, как мы указали это в объявлении типа JSON, а для всех остальных под-колонок тип — Dynamic.

Также возможно читать под-колонки типа Dynamic, используя специальный синтаксис json.some.path.:TypeName:

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

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

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

примечание

Чтение под-объектов как под-колонок может быть неэффективным, так как это может потребовать почти полного сканирования данных JSON.

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

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

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

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

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

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

Давайте попробуем прочитать под-колонки из колонки JSON:

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

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

Давайте проверим пути и типы внутри нашего Array(JSON):

Давайте прочитаем под-колонки из колонки Array(JSON):

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

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

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

Примеры:

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

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

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

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

Давайте посмотрим, что происходит, когда лимит достигается в нескольких различных сценариях.

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

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

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

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

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

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

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

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

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

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

Существует несколько функций, которые могут помочь исследовать содержимое колонки JSON:

Примеры

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

ИЗМЕНИТЬ ИЗМЕНИТЬ КОЛОНКУ на тип JSON​

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

Пример

Сравнение между значениями типа JSON​

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

Например:

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

Советы по лучшему использованию типа JSON​

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

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

Дальнейшее чтение​