Тип данных JSON

Елена Дворядкина

Для работы с данными JSON в ADQM можно:

  • использовать тип данных JSON (доступен с версии 22.3);

  • явно определить столбцы таблицы, сопоставляя их с полями JSON;

  • сохранить объекты JSON как строки и использовать специальные функции для извлечения значений.

Данная статья описывает примеры использования этих подходов с помощью тестового набора данных о людях (persons), который включает поля разных типов (name, age, hire_date, address) и может быть по-разному структурирован в формате JSON.

Тип данных JSON

Начиная с версии 22.3, ADQM поддерживает тип данных Object(JSON) (сокращенное название типа — JSON), который позволяет автоматически создавать столбцы таблицы подходящих типов на основе входящих данных в формате JSON. То есть схема таблицы может динамически изменяться в зависимости от изменения/обновления данных.

ВАЖНО
В настоящее время тип данных JSON находится в стадии разработки, но его можно использовать в экспериментальном режиме. Для этого необходимо перед созданием таблицы включить опцию: SET allow_experimental_object_type = 1.

Пример

Создайте файл persons.json с данными в формате JSON в папке /var/lib/clickhouse/user_files:

[
  {"name":"john", "age":42, "hire_date":"2021-01-01", "address": {"country":"USA", "city":"New York"}},
  {"name":"mary", "age":25, "hire_date":"2022-02-02", "address": {"country":"UK", "city":"London"}},
  {"name":"andrew", "age":33, "hire_date":"2023-03-03", "address": {"country":"Germany", "city":"Berlin"}}
]

При импорте данных в ADQM используйте формат JSONAsObject, чтобы указать, что каждый объект JSON будет хранится как отдельная строка таблицы в столбце типа JSON.

Например, в следующем запросе указывается, что объекты JSON из файла persons.json запишутся в столбец person типа JSON (если столбец не указать явно, будет использоваться столбец с именем по умолчанию — json):

SELECT * FROM file('persons.json', 'JSONAsObject', 'person JSON');

Результат выполнения запроса:

┌─person──────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"address.city":"New York","address.country":"USA","age":42,"hire_date":"2021-01-01","name":"john"}     │
│ {"address.city":"London","address.country":"UK","age":25,"hire_date":"2022-02-02","name":"mary"}        │
│ {"address.city":"Berlin","address.country":"Germany","age":33,"hire_date":"2023-03-03","name":"andrew"} │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Чтобы эффективно оперировать этими данными, рекомендуется создать таблицу MergeTree и сохранить в нее данные через запрос INSERT INTO.

Выполните последовательно следующие команды:

SET allow_experimental_object_type = 1;
CREATE TABLE json_table (person JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO json_table SELECT * FROM file('persons.json', 'JSONAsObject', 'person JSON');

Выполните запрос DESCRIBE, чтобы убедиться, что таблица содержит один столбец типа JSON:

DESCRIBE json_table;
┌─name───┬─type───────────┬
│ person │ Object('json') │
└────────┴────────────────┴

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

DESCRIBE json_table SETTINGS describe_extend_object_types=1;
┌─name───┬─type───────────────────────────────────────────────────────────────────────────────────────┬
│ person │ Tuple(address Tuple(city String, country String), age Int8, hire_date String, name String) │
└────────┴────────────────────────────────────────────────────────────────────────────────────────────┴
ПРИМЕЧАНИЕ

Обратите внимание, динамически создаваемые столбцы не могут быть использованы в качестве первичных ключей или ключей сортировки, так как ADQM не знает об этих столбцах на этапе создания таблицы. Поэтому в запросе CREATE TABLE используется выражение ORDER BY tuple(), то есть таблица не имеет первичного ключа. В разделе Добавление первичных ключей описано, как обойти это ограничение.

Выборка данных из динамических столбцов

К динамическим столбцам таблицы можно обращаться в запросах. Например:

SELECT person.name, person.address.country FROM json_table;
┌─person.name─┬─person.address.country─┐
│ john        │ USA                    │
│ mary        │ UK                     │
│ andrew      │ Germany                │
└─────────────┴────────────────────────┘

При выборке данных из динамических столбцов их можно использовать для сортировки и агрегирования данных.

Например, следующий запрос выводит список строк, отсортированный по именам людей:

SELECT person.name, person.address.city FROM json_table ORDER BY person.name;
┌─person.name─┬─person.address.city─┐
│ andrew      │ Berlin              │
│ john        │ New York            │
│ mary        │ London              │
└─────────────┴─────────────────────┘

Изменение данных

Если в исходном файле изменяются поля объектов JSON (например, добавляются новые поля или меняется тип существующих), ADQM учтет это при вставке данных и автоматически настроит столбцы таблицы в соответствии с новой структурой объектов JSON.

Добавление нового поля

  1. Добавьте в исходный файл persons.json столбец id:

    [
  {"id":1, "name":"john", "age":42, "hire_date":"2021-01-01", "address": {"country":"USA", "city":"New York"}},
  {"id":2, "name":"mary", "age":25, "hire_date":"2022-02-02", "address": {"country":"UK", "city":"London"}},
  {"id":3, "name":"andrew", "age":33, "hire_date":"2023-03-03", "address": {"country":"Germany", "city":"Berlin"}}
]

  2. Вставьте данные в таблицу:

    INSERT INTO json_table SELECT * FROM file('persons.json', 'JSONAsObject', 'person JSON');

  3. Убедитесь, что новый столбец id типа Int8 добавлен в схему таблицы:

    DESCRIBE json_table SETTINGS describe_extend_object_types=1;
    ┌─name───┬─type────────────────────────────────────────────────────────────────────────────────────────────────┬
│ person │ Tuple(address Tuple(city String, country String), age Int8, hire_date String, id Int8, name String) │
└────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────┴

Изменения типа поля

  1. Измените в исходном файле JSON значения поля id с числовых на строковые, например:

    [
  {"id":"user_1", "name":"john", "age":42, "hire_date":"2021-01-01", "address": {"country":"USA", "city":"New York"}},
  {"id":"user_2", "name":"mary", "age":25, "hire_date":"2022-02-02", "address": {"country":"UK", "city":"London"}},
  {"id":"user_3", "name":"andrew", "age":33, "hire_date":"2023-03-03", "address": {"country":"Germany", "city":"Berlin"}}
]

  2. Вставьте данные в таблицу:

    INSERT INTO json_table SELECT * FROM file('persons.json', 'JSONAsObject', 'person JSON');

  3. Убедитесь, что тип столбца id автоматически изменился на String:

    DESCRIBE json_table SETTINGS describe_extend_object_types=1;
    ┌─name───┬─type──────────────────────────────────────────────────────────────────────────────────────────────────┬
│ person │ Tuple(address Tuple(city String, country String), age Int8, hire_date String, id String, name String) │
└────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────┴

Неструктурированные данные

Если данные JSON неструктурированы (например, объекты JSON имеют разные наборы полей), при вставке данных ADQM учтет все поля и заполнит поля, для которых не определены значения, значениями по умолчанию для соответствующих типов данных (например, 0 для чисел, пустые строки для строк).

Пример

В исходном файле persons.json измените структуру первого объекта JSON — удалите поле address.city и добавьте новое поле position:

[
  {"name":"john", "age":42, "hire_date":"2021-01-01", "address": {"country":"USA"}, "position":"manager"},
  {"name":"mary", "age":25, "hire_date":"2022-02-02", "address": {"country":"UK", "city":"London"}},
  {"name":"andrew", "age":33, "hire_date":"2023-03-03", "address": {"country":"Germany", "city":"Berlin"}}
]

Создайте новую таблицу и вставьте в нее данные из файла:

SET allow_experimental_object_type = 1;
CREATE TABLE unstruct_json_table (person JSON) ENGINE = MergeTree ORDER BY tuple();
INSERT INTO unstruct_json_table SELECT * FROM file('persons.json', 'JSONAsObject', 'person JSON');

Проверьте значения столбцов таблицы:

SELECT person.name, person.position, person.address.city FROM unstruct_json_table;
┌─person.name─┬─person.position─┬─person.address.city─┐
│ john        │ manager         │                     │
│ mary        │                 │ London              │
│ andrew      │                 │ Berlin              │
└─────────────┴─────────────────┴─────────────────────┘

Значения по умолчанию в столбцах таблицы учитываются при вычислении агрегатных функций. Например, следующий запрос с функцией groupArray возвращает массив значений столбца person.address.city, где первое значение — пустая строка, так как первый объект JSON не содержит поле city:

SELECT groupArray(person.address.city) FROM  unstruct_json_table;
┌─groupArray(person.address.city)─┐
│ ['','London','Berlin']          │
└─────────────────────────────────┘

Добавление первичных ключей

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

Пример

В исходном файле данных persons.json добавьте поле person для хранения объектов JSON:

[
  {"person":{"address":{"city":"New York","country":"USA"},"age":42,"hire_date":"2021-01-01","name":"john"}},
  {"person":{"address":{"city":"London","country":"UK"},"age":25,"hire_date":"2022-02-02","name":"mary"}},
  {"person":{"address":{"city":"Berlin","country":"Germany"},"age":33,"hire_date":"2023-03-03","name":"andrew"}}
]

Создайте таблицу со столбцами:

  • name — строковые значения JSON-поля name, которые извлекаются с помощью функции JSONExtractString (этот столбец используется как ключ сортировки);

  • person — объекты JSON.

SET allow_experimental_object_type = 1;
CREATE TABLE sort_key_json_table (name String DEFAULT JSONExtractString(toJSONString(person), 'name'), person JSON)
ENGINE = MergeTree
ORDER BY name;

Вставьте данные из файла persons.json:

INSERT INTO sort_key_json_table (name, person) SELECT
    JSONExtractString(toJSONString(person), 'person.name'),
    person
FROM file('persons.json', 'JSONAsObject', 'person JSON');

В результирующей таблице строки отсортированы по столбцу name:

SELECT * FROM sort_key_json_table;
┌─name───┬─person────────────────────────────────────────────┐
│ andrew │ ((('Berlin','Germany'),33,'2023-03-03','andrew')) │
│ john   │ ((('New York','USA'),42,'2021-01-01','john'))     │
│ mary   │ ((('London','UK'),25,'2022-02-02','mary'))        │
└────────┴───────────────────────────────────────────────────┘

Сопоставление столбцов таблицы с полями JSON

ADQM может работать с данными JSON без использования типа данных Object(JSON), что актуально для всех версий ADQM (в том числе более ранних, чем 22.3). Для этого рекомендуется создать таблицу с явно определенными столбцами, сопоставляя их со всеми полями JSON. В этом случае необходимо заранее знать структуру данных JSON и типы полей, а также следить за тем, чтобы схема таблицы оставалась актуальной, если добавляются/изменяются поля в источнике данных JSON. Для вложенных структур JSON можно использовать типы данных Tuple, Map и Nested при определении столбцов таблицы.

Такой подход является наиболее оптимальным, если данные JSON имеют известную фиксированную структуру, так как может обеспечить лучшую производительность, а также позволяет управлять кодеками сжатия и гибко настраивать первичные ключи и ключи сортировки.

Пример

Для примера подготовьте файл /var/lib/clickhouse/user_files/persons.json с данными JSON без вложенных структур:

[
  {"name":"john", "age":42, "hire_date":"2021-01-01"},
  {"name":"mary", "age":25, "hire_date":"2022-02-02"},
  {"name":"andrew", "age":33, "hire_date":"2023-03-03"}
]

Создайте таблицу со столбцами соответствующих типов для каждого поля JSON:

CREATE TABLE struct_json_table (name String, age Int32, hire_date Date) ENGINE = MergeTree ORDER BY name;

Вставьте в таблицу данные из файла, используя табличную функцию file:

INSERT INTO struct_json_table SELECT * FROM file('persons.json', 'JSONEachRow');

В результате в столбцы таблицы запишутся данные из соответствующих JSON-полей:

SELECT * FROM struct_json_table;
┌─name───┬─age─┬──hire_date─┐
│ andrew │  33 │ 2023-03-03 │
│ john   │  42 │ 2021-01-01 │
│ mary   │  25 │ 2022-02-02 │
└────────┴─────┴────────────┘

В этом примере для импорта данных JSON используется один из поддерживаемых форматов — JSONEachRow. В разделе Форматы импорта данных JSON приведены и описаны другие форматы, предоставляемые ADQM.

Примеры создания столбцов типа Nested, Tuple и Map для работы с вложенными структурами JSON приведены в разделе Handle as Structured Data документации ClickHouse.

Функции для работы с JSON

ADQM позволяет сохранять объекты JSON как строки и предоставляет специальные функции для парсинга JSON-based строк и извлечения значений полей.

Например, следующий запрос интерпретирует объекты JSON из указанного файла как строки (используется формат JSONAsString), извлекает значения полей name и age и записывает их в соответствующие столбцы:

SELECT
    JSONExtractString(json, 'name') AS name,
    JSONExtractUInt(json, 'age') AS age
FROM (SELECT * FROM file('persons.json', JSONAsString) AS json);

Результат:

┌─name───┬─age──┐
│ john   │   42 │
│ mary   │   25 │
│ andrew │   33 │
└────────┴──────┘

Полный список поддерживаемых функций представлен в статье документации ClickHouse json-functions.

Импорт данных JSON

ADQM предоставляет различные форматы и настройки для импорта данных JSON. Чтобы выбрать подходящие опции, необходимо определить, как входные данные структурированы.

Форматы импорта данных JSON

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

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

CREATE TABLE import_json_table(name String, age Int32, hire_date Date) ENGINE = MergeTree ORDER BY hire_date;

В приведенных примерах запросы из столбца Импорт данных вставляют данные в таблицу import_json_table из локального файла persons.json (расположен в папке, из которой запущен clickhouse-client), содержимое которого описано в столбце Входные данные JSON.

Форматы импорта JSON
Входные данные JSON Импорт данных

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

[
  {"name":"john", "age":42, "hire_date":"2021-01-01"},
  {"name":"mary", "age":25, "hire_date":"2022-02-02"},
  {"name":"andrew", "age":33, "hire_date":"2023-03-03"}
]

Используйте формат JSONEachRow:

INSERT INTO import_json_table
FROM INFILE 'persons.json' FORMAT JSONEachRow;

NDJSON (newline-delimited JSON):

{"name":"john","age":42,"hire_date":"2021-01-01"}
{"name":"mary","age":25,"hire_date":"2022-02-02"}
{"name":"andrew","age":33,"hire_date":"2023-03-03"}

Используйте формат JSONEachRow:

INSERT INTO import_json_table
FROM INFILE 'persons.json' FORMAT JSONEachRow;

Массивы JSON:

["john", 42, "2021-01-01"],
["mary", 25, "2022-02-02"],
["andrew", 33, "2023-03-03"]

Используйте формат JSONCompactEachRow:

INSERT INTO import_json_table
FROM INFILE 'persons.json' FORMAT JSONCompactEachRow;

Один объект JSON, который содержит поля с массивами значений:

{
  "name": ["john", "mary", "andrew"],
  "age": ["42", "25", "33"],
  "hire_date": ["2021-01-01", "2022-02-02", "2023-03-03"]
}

Используйте формат JSONColumns:

INSERT INTO import_json_table
FROM INFILE 'persons.json' FORMAT JSONColumns;

Массив c массивами JSON:

[
  ["john", "mary", "andrew"],
  ["42", "25", "33"],
  ["2021-01-01", "2022-02-02", "2023-03-03"]
]

Используйте формат JSONCompactColumns:

INSERT INTO import_json_table
FROM INFILE 'persons.json' FORMAT JSONCompactColumns;

В результате каждого из перечисленных выше примеров данные импортируются в таблицу как:

┌─name───┬─age─┬──hire_date─┐
│ john   │  42 │ 2021-01-01 │
│ mary   │  25 │ 2022-02-02 │
│ andrew │  33 │ 2023-03-03 │
└────────┴─────┴────────────┘

Полный список форматов для импорта данных JSON доступен в статье Formats for Input and Output Data (см. форматы с префиксом JSON).

Импорт объектов JSON в строковом формате

Используйте формат JSONAsString, чтобы импортировать объекты JSON в таблицу ADQM как строки (без парсинга). Данные запишутся в единственный столбец таблицы типа String.

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

Пример

  1. Подготовьте данные в исходном локальном файле persons.json:

    [
  {"name":"john", "age":42, "hire_date":"2021-01-01"},
  {"name":"mary", "age":25, "hire_date":"2022-02-02"},
  {"name":"andrew", "age":33, "hire_date":"2023-03-03"}
]

  2. Создайте таблицу со столбцом типа String:

    CREATE TABLE json_as_str_table(data String) ENGINE = MergeTree ORDER BY tuple();

  3. Вставьте данные, используя формат JSONAsString:

    INSERT INTO json_as_str_table FROM INFILE 'persons.json' FORMAT JSONAsString;

  4. Результат:

    SELECT * FROM json_as_str_table;
    ┌─data──────────────────────────────────────────────────┐
│ {"name":"john", "age":42, "hire_date":"2021-01-01"}   │
│ {"name":"mary", "age":25, "hire_date":"2022-02-02"}   │
│ {"name":"andrew", "age":33, "hire_date":"2023-03-03"} │
└───────────────────────────────────────────────────────┘

Для дальнейшей обработки объектов JSON, записанных в виде строк, используйте функции для работы с JSON.

Например, в следующем запросе функция JSONExtractString анализирует строковые записи объектов JSON в столбце data и возвращает строковые значения поля name:

SELECT JSONExtractString(data, 'name') AS person_name, data FROM json_as_str_table;
┌─person_name─┬─data──────────────────────────────────────────────────┐
│ john        │ {"name":"john", "age":42, "hire_date":"2021-01-01"}   │
│ mary        │ {"name":"mary", "age":25, "hire_date":"2022-02-02"}   │
│ andrew      │ {"name":"andrew", "age":33, "hire_date":"2023-03-03"} │
└─────────────┴───────────────────────────────────────────────────────┘

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

По умолчанию ADQM игнорирует неизвестные столбцы при импорте данных JSON.

Например, если создать таблицу с двумя столбцами name и hire_date и вставить в нее объекты JSON с тремя полями name, hire_date и age (данные JSON из раздела выше), поле age проигнорируется.

CREATE TABLE short_table(name String, hire_date Date) ENGINE = MergeTree ORDER BY hire_date;
INSERT INTO short_table FROM INFILE 'persons.json' FORMAT JSONEachRow;
SELECT * FROM short_table;
┌─name───┬──hire_date─┐
│ john   │ 2021-01-01 │
│ mary   │ 2022-02-02 │
│ andrew │ 2023-03-03 │
└────────┴────────────┘

Отключить это поведение можно с помощью настройки input_format_skip_unknown_fields:

SET input_format_skip_unknown_fields = 0;

В этом случае ADQM сгенерирует исключение, если структуры таблицы и JSON не согласованы:

INSERT INTO short_table FROM INFILE 'persons.json' FORMAT JSONEachRow;
Exception on client:
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: age: (at row 1)

Импорт JSON с вложенными объектами

При импорте объектов JSON c вложенными структурами используйте сложные типы данных (JSON, Array или Tuple) для столбцов с вложенными объектами.

Пример

  1. Подготовьте файл /var/lib/clickhouse/user_files/persons.json:

    [
  {"name":"john", "age":42, "hire_date":"2021-01-01", "address": {"country":"USA", "city":"New York"}},
  {"name":"mary", "age":25, "hire_date":"2022-02-02", "address": {"country":"UK", "city":"London"}},
  {"name":"andrew", "age":33, "hire_date":"2023-03-03", "address": {"country":"Germany", "city":"Berlin"}}
]

  2. Выполните выборку данных с помощью табличной функции file, указав в параметрах структуру таблицы следующим образом:

    SELECT * FROM file('persons.json', JSONEachRow, 'name String, age Int32, hire_date Date, address JSON');

  3. В результирующей таблице столбец address содержит вложенные объекты JSON:

┌─name───┬─age─┬──hire_date─┬─address───────────────────────────────┐
│ john   │  42 │ 2021-01-01 │ {"city":"New York","country":"USA"}   │
│ mary   │  25 │ 2022-02-02 │ {"city":"London","country":"UK"}      │
│ andrew │  33 │ 2023-03-03 │ {"city":"Berlin","country":"Germany"} │
└────────┴─────┴────────────┴───────────────────────────────────────┘

Поля вложенных объектов

Чтобы ссылаться на поля вложенных объектов, нужно включить опцию input_format_import_nested_json:

SET input_format_import_nested_json = 1

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

Пример запроса:

SELECT * FROM file('persons.json', JSONEachRow, 'name String, `address.city` String');
┌─name───┬─address.city─┐
│ john   │ New York     │
│ mary   │ London       │
│ andrew │ Berlin       │
└────────┴──────────────┘

Автоматическое определение типов данных при импорте JSON

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

Пример

  1. Подготовьте файл-источник /var/lib/clickhouse/user_files/persons.json:

    {"name":"john","age":42,"hire_date":"2021-01-01"}
{"name":"mary","age":25,"hire_date":"2022-02-02"}
{"name":"andrew","age":33,"hire_date":"2023-03-03"}

  2. Выполните запрос DESCRIBE, чтобы проверить, какие типы данных определены для значений в файле persons.json:

    DESCRIBE TABLE file('persons.json', JSONEachRow);
    ┌─name──────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ name      │ Nullable(String) │              │                    │         │                  │                │
│ age       │ Nullable(Int64)  │              │                    │         │                  │                │
│ hire_date │ Nullable(Date)   │              │                    │         │                  │                │
└───────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

  3. Создайте таблицу, используя табличную функцию file:

    CREATE TABLE persons ENGINE = MergeTree ORDER BY tuple() AS SELECT * FROM file('persons.json', JSONEachRow);

  4. Убедитесь, что столбцы таблицы соответствуют типам полей импортируемого JSON:

    DESCRIBE TABLE persons;
    ┌─name──────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ name      │ Nullable(String) │              │                    │         │                  │                │
│ age       │ Nullable(Int64)  │              │                    │         │                  │                │
│ hire_date │ Nullable(Date)   │              │                    │         │                  │                │
└───────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Экспорт данных JSON

Большинство форматов для работы с JSON, которые поддерживает ADQM, можно использовать для вывода данных в запросах SELECT.

Форматы вывода данных JSON

Ниже приведены примеры использования различных форматов для экспорта данных в формате JSON из таблицы export_json_table:

┌─name───┬─age─┬──hire_date─┐
│ john   │  42 │ 2021-01-01 │
│ mary   │  25 │ 2022-02-02 │
│ andrew │  33 │ 2023-03-03 │
└────────┴─────┴────────────┘
Создание таблицы
CREATE TABLE export_json_table (`name` String, `age` Int32, `hire_date` Date) ENGINE = MergeTree ORDER BY hire_date;
INSERT INTO export_json_table VALUES ('john', 42, '2021-01-01'), ('mary', 25, '2022-02-02'), ('andrew', 33, '2023-03-03');

  • JSONEachRow

    Выводит каждую строку таблицы как отдельный JSON-объект на новой строке.

    SELECT * FROM export_json_table FORMAT JSONEachRow;
    {"name":"john","age":42,"hire_date":"2021-01-01"}
{"name":"mary","age":25,"hire_date":"2022-02-02"}
{"name":"andrew","age":33,"hire_date":"2023-03-03"}

  • JSONStringsEachRow

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

    Например, в результате выполнения следующего запроса значения поля age выводятся как строки, а не числа:

    SELECT * FROM export_json_table FORMAT JSONStringsEachRow;
    {"name":"john","age":"42","hire_date":"2021-01-01"}
{"name":"mary","age":"25","hire_date":"2022-02-02"}
{"name":"andrew","age":"33","hire_date":"2023-03-03"}

  • JSONCompactEachRow

    Выводит строки таблицы как массивы JSON — массивы значений без имен столбцов.

    SELECT * FROM export_json_table FORMAT JSONCompactEachRow;
    ["john", 42, "2021-01-01"]
["mary", 25, "2022-02-02"]
["andrew", 33, "2023-03-03"]

  • JSON

    Выводит данные в формате JSON. Кроме данных таблицы, также выводятся имена и типы столбцов, и некоторая дополнительная информация (например, количество выведенных строк и общее количество строк, которое могло бы быть выведено, если бы в запросе не было выражения LIMIT).

    SELECT * FROM export_json_table LIMIT 2 FORMAT JSON;
    {
        "meta":
        [
                {
                        "name": "name",
                        "type": "String"
                },
                {
                        "name": "age",
                        "type": "Int32"
                },
                {
                        "name": "hire_date",
                        "type": "Date"
                }
        ],

        "data":
        [
                {
                        "name": "john",
                        "age": 42,
                        "hire_date": "2021-01-01"
                },
                {
                        "name": "mary",
                        "age": 25,
                        "hire_date": "2022-02-02"
                }
        ],

        "rows": 2,

        "rows_before_limit_at_least": 3,

        "statistics":
        {
                "elapsed": 0.000559985,
                "rows_read": 3,
                "bytes_read": 59
        }
}

  • JSONCompact

    Выводит те же метаданные, но использует сжатую форму для самих данных.

    SELECT * FROM export_json_table LIMIT 2 FORMAT JSONCompact;
    {
        "meta":
        [
                {
                        "name": "name",
                        "type": "String"
                },
                {
                        "name": "age",
                        "type": "Int32"
                },
                {
                        "name": "hire_date",
                        "type": "Date"
                }
        ],

        "data":
        [
                ["john", 42, "2021-01-01"],
                ["mary", 25, "2022-02-02"]
        ],

        "rows": 2,

        "rows_before_limit_at_least": 3,

        "statistics":
        {
                "elapsed": 0.000571299,
                "rows_read": 3,
                "bytes_read": 59
        }
}

Полный список форматов для экспорта в JSON доступен в статье Formats for Input and Output Data документации ClickHouse.

Экспорт данных JSON в файл

Чтобы сохранить данные JSON в файл, можно использовать выражение INTO OUTFILE в запросе SELECT, например:

SELECT * FROM export_json_table INTO OUTFILE 'out.json' FORMAT JSONEachRow;

Проверьте, что файл создан (в папке, из которой запущен clickhouse-client) и данные в него записаны:

$ cat out.json
{"name":"john","age":42,"hire_date":"2021-01-01"}
{"name":"mary","age":25,"hire_date":"2022-02-02"}
{"name":"andrew","age":33,"hire_date":"2023-03-03"}
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней