Обработка других форматов JSON

Ранее приведенные примеры загрузки данных JSON предполагают использование JSONEachRow (NDJSON). Этот формат читает ключи в каждой строке JSON как колонки. Например:

Хотя это обычно наиболее часто используемый формат для JSON, пользователи могут столкнуться с другими форматами или понадобится прочитать JSON как один объект.

Мы предоставляем примеры чтения и загрузки JSON в других распространенных форматах ниже.

Чтение JSON как объекта

Наши предыдущие примеры показывают, как JSONEachRow читает JSON, разделенный по строкам, где каждая строка читается как отдельный объект, сопоставленный строке таблицы, а каждый ключ – колонке. Это идеально подходит для случаев, когда структура JSON предсказуема с одиночными типами для каждой колонки.

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

Используйте JSONEachRow для вставок построчно, а JSONAsObject при хранении гибких или динамичных данных JSON.

Сравните приведенный выше пример с следующим запросом, который читает те же данные как JSON объект на каждую строку:

Формат JSONAsObject также может быть полезен для вставки строк в таблицу с использованием одной колонки типа JSON, например.

Формат JSONAsObject может также быть полезен для чтения JSON, разделенного по строкам, в случаях, когда структура объектов непоследовательна. Например, если ключ изменяется по типу в разных строках (он может иногда быть строкой, но в других случаях объектом). В таких случаях ClickHouse не может вывести стабильную схему с помощью JSONEachRow, и JSONAsObject позволяет загружать данные без строгого контроля типов, сохраняя каждую строку JSON целиком в одной колонке. Например, обратите внимание, как JSONEachRow выдаёт ошибку на следующем примере:

Напротив, JSONAsObject может быть использован в этом случае, поскольку тип JSON поддерживает несколько типов для одного и того же подстолбца.

Массив JSON объектов

Одна из самых популярных форм данных JSON - это наличие списка JSON объектов в массиве JSON, например, в этом примере:

Давайте создадим таблицу для этого типа данных:

Чтобы импортировать список JSON объектов, мы можем использовать формат JSONEachRow (вставляя данные из файла list.json):

Мы использовали конструкцию FROM INFILE для загрузки данных из локального файла, и можем увидеть, что импорт прошел успешно:

Ключи объектов JSON

В некоторых случаях список JSON объектов может быть закодирован как свойства объектов вместо элементов массива (см. objects.json для примера):

ClickHouse может загружать данные из такого типа данных, используя формат JSONObjectEachRow:

Указание значений ключей родительского объекта

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

Теперь мы можем проверить, какие данные будут загружены из оригинального JSON файла, используя функцию file():

Обратите внимание, как колонка id была правильно заполнена значениями ключей.

JSON Массивы

Иногда, ради экономии места, JSON файлы закодированы в массивах вместо объектов. В этом случае мы сталкиваемся со списком JSON массивов:

В этом случае ClickHouse загрузит эти данные и назначит каждое значение соответствующей колонке на основе его порядка в массиве. Мы используем формат JSONCompactEachRow для этого:

Импорт отдельных колонок из JSON массивов

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

ClickHouse использует формат JSONColumns для парсинга данных, отформатированных таким образом:

Также поддерживается более компактный формат, когда мы имеем дело с массивом колонок вместо объекта с помощью формата JSONCompactColumns:

Сохранение JSON объектов вместо их парсинга

В некоторых случаях вы можете захотеть сохранить JSON объекты в одном столбце String (или JSON) вместо их парсинга. Это может быть полезно при работе с списком JSON объектов разных структур. Взгляните на этот файл например, на котором у нас есть несколько разных JSON объектов внутри родительского списка:

Мы хотим сохранить оригинальные JSON объекты в следующую таблицу:

Теперь мы можем загрузить данные из файла в эту таблицу, используя формат JSONAsString, чтобы сохранить JSON объекты вместо их парсинга:

И мы можем использовать функции JSON для запроса сохраненных объектов:

Обратите внимание, что JSONAsString прекрасно работает в случаях, когда у нас есть файлы с форматированием JSON объект на строку (обычно используемые с форматом JSONEachRow).

Схема для вложенных объектов

В случаях, когда мы имеем дело с вложенными JSON объектами, мы можем дополнительно определить явную схему и использовать сложные типы (Array, Object Data Type или Tuple), чтобы загружать данные:

Доступ к вложенным JSON объектам

Мы можем ссылаться на вложенные ключи JSON, включив следующую настройку:

Это позволяет нам ссылаться на ключи вложенных JSON объектов, используя нотацию с точками (не забудьте обернуть их символами обратной кавычки для работы):

Таким образом, мы можем развертывать вложенные JSON объекты или использовать некоторые вложенные значения, чтобы сохранить их как отдельные колонки.

Пропуск неизвестных колонок

По умолчанию ClickHouse будет игнорировать неизвестные колонки при импорте данных JSON. Давайте попробуем импортировать оригинальный файл в таблицу без колонки month:

Мы все еще можем вставить оригинальные данные JSON с 3 колонками в эту таблицу:

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

ClickHouse выдаст исключения в случаях несоответствия структуры JSON и структуры таблицы.

BSON

ClickHouse позволяет экспортировать и импортировать данные из закодированных файлов BSON. Этот формат используется некоторыми СУБД, например, базой данных MongoDB.

Чтобы импортировать данные BSON, мы используем формат BSONEachRow. Давайте импортируем данные из этого BSON файла:

Мы также можем экспортировать в файлы BSON, используя тот же формат:

После этого мы получим наши данные экспортированными в файл out.bson.