Тип данных JSON
Ищете руководство?
Посмотрите наш путеводитель по лучшим практикам работы с JSON для примеров, расширенных функций и соображений по использованию типа JSON.
Читать далее
Тип JSON
хранит документы в формате JavaScript Object Notation (JSON) в одной колонке.
Если вы хотите использовать тип JSON
и для примеров на этой странице, пожалуйста, используйте:
Однако, если вы используете ClickHouse Cloud, вам необходимо сначала связаться с поддержкой, чтобы включить использование типа JSON
.
В ClickHouse Open-Source тип данных JSON отмечен как готовый к производству в версии 25.3. Не рекомендуется использовать этот тип в производственной среде в предыдущих версиях.
Чтобы объявить колонку типа 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.
Это работает аналогично автоматическому выводу схемы из входных данных,
и контролируется теми же настройками:
- 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
Рассмотрим несколько примеров:
Обработка массивов объектов 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:
JSONAllPaths
JSONAllPathsWithTypes
JSONDynamicPaths
JSONDynamicPathsWithTypes
JSONSharedDataPaths
JSONSharedDataPathsWithTypes
distinctDynamicTypes
distinctJSONPaths and distinctJSONPathsAndTypes
Примеры
Давайте исследуем содержимое набора данных GH Archive за дату 2020-01-01
:
ИЗМЕНИТЬ ИЗМЕНИТЬ КОЛОНКУ на тип JSON
Можно изменить существующую таблицу и изменить тип колонки на новый тип JSON
. В данный момент поддерживается только изменение типа с String
.
Пример
Сравнение значений типа JSON
JSON-объекты сравниваются аналогично ассоциативным массивам (Maps).
Например:
Примечание: когда 2 пути содержат значения разных типов данных, они сравниваются в соответствии с правилом сравнения типа Variant
.
Советы по лучшему использованию типа JSON
Перед созданием колонки JSON
и загрузкой данных в нее, учтите следующие советы:
- Исследуйте ваши данные и указывайте как можно больше подсказок путей с типами. Это сделает хранение и чтение гораздо более эффективными.
- Подумайте о том, какие пути вам понадобятся и какие пути вам никогда не понадобятся. Укажите пути, которые вам не нужны, в разделе
SKIP
, а в разделеSKIP REGEXP
, если необходимо. Это улучшит хранение. - Не устанавливайте параметр
max_dynamic_paths
на слишком большие значения, так как это может сделать хранение и чтение менее эффективными. Хотя это зависит от параметров системы, таких как память, CPU и т.д., общим правилом будет не устанавливатьmax_dynamic_paths
> 10 000.