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

Тип данных 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) и вставляются в динамический столбец для пути. Чтобы прочитать массив объектов, вы можете извлечь его из динамического столбца как под-колонку:

Как вы могли заметить, параметры 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 нельзя сравнивать с помощью функций less/greater, но можно сравнивать с помощью функции equal.

Два JSON-объекта считаются равными, если у них одинаковый набор путей, и каждый из этих путей имеет одинаковый тип и значение в обоих объектах.

Например:

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

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

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

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