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

Тип данных JSON

Beta feature. Learn more.

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

примечание

Эта функция находится на стадии бета-тестирования и еще не готова к использованию в продакшене. Если вам нужно работать с документами 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:

ALTER MODIFY COLUMN для типа JSON​

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

Пример

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

Значения колонки JSON не могут быть сравнены с помощью функций less/greater, но могут быть сравнены с помощью функции equal.

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

Например:

Советы для более эффективного использования типа JSON​

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

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

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