JSONObjectEachRow

В этом формате все данные представлены в виде одного объекта JSON, а каждая строка — как отдельное поле этого объекта, аналогично формату JSONEachRow.

Предположим, есть такой JSON:

{
  "row_1": {"num": 42, "str": "hello", "arr":  [0,1]},
  "row_2": {"num": 43, "str": "hello", "arr":  [0,1,2]},
  "row_3": {"num": 44, "str": "hello", "arr":  [0,1,2,3]}
}

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

Допустим, у нас есть таблица test с двумя столбцами:

┌─object_name─┬─number─┐
│ first_obj   │      1 │
│ second_obj  │      2 │
│ third_obj   │      3 │
└─────────────┴────────┘

Давайте выведем это в формате JSONObjectEachRow и используем настройку format_json_object_each_row_column_for_object_name:

SELECT * FROM test SETTINGS format_json_object_each_row_column_for_object_name='object_name'

{
    "first_obj": {"number": 1},
    "second_obj": {"number": 2},
    "third_obj": {"number": 3}
}

Предположим, что мы сохранили результат из предыдущего примера в файл data.json:

SELECT * FROM file('data.json', JSONObjectEachRow, 'object_name String, number UInt64') SETTINGS format_json_object_each_row_column_for_object_name='object_name'

┌─object_name─┬─number─┐
│ first_obj   │      1 │
│ second_obj  │      2 │
│ third_obj   │      3 │
└─────────────┴────────┘

Это также работает и для автоматического определения схемы:

DESCRIBE file('data.json', JSONObjectEachRow) SETTING format_json_object_each_row_column_for_object_name='object_name'

┌─name────────┬─type────────────┐
│ object_name │ String          │
│ number      │ Nullable(Int64) │
└─────────────┴─────────────────┘

INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1} {"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

ClickHouse допускает:

• Любой порядок пар ключ-значение в объекте.
• Пропуск некоторых значений.

ClickHouse игнорирует пробелы между элементами и запятые после объектов. Все объекты можно передать в одной строке. Разделять их переводами строк не обязательно.

ClickHouse заменяет пропущенные значения значениями по умолчанию для соответствующих типов данных.

Если указано DEFAULT expr, ClickHouse использует разные правила подстановки в зависимости от настройки input_format_defaults_for_omitted_fields.

Рассмотрим следующую таблицу:

CREATE TABLE IF NOT EXISTS example_table
(
    x UInt32,
    a DEFAULT x * 2
) ENGINE = Memory;

• Если input_format_defaults_for_omitted_fields = 0, то значение по умолчанию для x и a равно 0 (это значение по умолчанию для типа данных UInt32).
• Если input_format_defaults_for_omitted_fields = 1, то значение по умолчанию для x равно 0, а для a — x * 2.

Рассмотрим таблицу UserActivity в качестве примера:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Запрос SELECT * FROM UserActivity FORMAT JSONEachRow возвращает:

{"UserID":"4324182021466249494","PageViews":5,"Duration":146,"Sign":-1}
{"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

В отличие от формата JSON, недопустимые последовательности UTF-8 не подменяются. Значения экранируются так же, как в JSON.

Если у вас есть таблица со столбцами типа Nested, вы можете вставлять JSON-данные с той же структурой. Чтобы включить эту возможность, используйте настройку input_format_import_nested_json.

Например, рассмотрим следующую таблицу:

CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory

Как видно из описания типа данных Nested, ClickHouse рассматривает каждый элемент вложенной структуры как отдельный столбец (n.s и n.i в нашей таблице). Вы можете вставить данные следующим образом:

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]}

Чтобы вставлять данные в виде иерархического объекта JSON, задайте input_format_import_nested_json=1.

{
    "n": {
        "s": ["abc", "def"],
        "i": [1, 23]
    }
}

При отсутствии этой настройки ClickHouse генерирует исключение.

SELECT name, value FROM system.settings WHERE name = 'input_format_import_nested_json'

┌─name────────────────────────────┬─value─┐
│ input_format_import_nested_json │ 0     │
└─────────────────────────────────┴───────┘

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}

Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n: (at row 1)

SET input_format_import_nested_json=1
INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}
SELECT * FROM json_each_row_nested

┌─n.s───────────┬─n.i────┐
│ ['abc','def'] │ [1,23] │
└───────────────┴────────┘