JSON: read_json, автоструктура и ndjson

JSON — это древовидный, вложенный, нестрого типизированный формат. SQL — это плоские таблицы со строгими типами колонок. Между ними пропасть, и каждая аналитическая система переходит её по-своему. DuckDB переходит её замечательно прямо: он умеет вывести из JSON-документов реляционную схему — с настоящими колонками настоящих типов, включая вложенные типы STRUCT и LIST — и дать запрашивать JSON так же, как Parquet. Этот урок про то, как read_json это делает и где лежат подводные камни.

Два формата JSON-файлов

Сначала надо различать две принципиально разные раскладки JSON в файле, потому что читаются они по-разному.

Массив объектов — весь файл это один валидный JSON: открывающая скобка [, объекты через запятую, закрывающая ]. Чтобы понять, где кончается файл, парсер обязан дочитать его до конца — нельзя разбить такой файл на независимые куски.

NDJSON (newline-delimited JSON, он же JSON Lines) — каждая строка файла это отдельный самостоятельный JSON-объект, без запятых между ними и без обрамляющих скобок. Это и есть стандарт для данных и логов: файл можно резать по переводам строк, читать кусками, дописывать в конец, обрабатывать параллельно. Каждая строка независима.

read_json определяет формат сам по структуре файла. Но его можно задать явно опцией format: format = 'array', format = 'newline_delimited' (она же nd) или format = 'auto' (по умолчанию). Для больших файлов NDJSON предпочтительнее: он параллелится, тогда как один гигантский JSON-массив приходится разбирать в один поток.

-- read_json сам разберётся с форматом
SELECT * FROM read_json('events.ndjson') LIMIT 3;

-- Формат указан явно
SELECT * FROM read_json('snapshot.json', format = 'array');

Автоопределение структуры

Самое интересное в read_json — вывод схемы. DuckDB читает выборку документов и строит из них реляционный тип. Для каждого ключа верхнего уровня он смотрит значения по всей выборке и решает, какого типа должна быть колонка.

Скаляры превращаются в обычные типы: число — в BIGINT или DOUBLE, строка — в VARCHAR, true/false — в BOOLEAN, null отмечает колонку как nullable. А вот вложенность сохраняется через вложенные типы DuckDB: JSON-объект становится STRUCT с именованными полями, JSON-массив — LIST. Это не разворачивание в строки и не превращение в текст — это настоящие колоночные вложенные типы, которые движок хранит и обрабатывает нативно.

Возьмём NDJSON-файл users.ndjson:

{"id": 1, "name": "Anna", "address": {"city": "Berlin", "zip": "10115"}, "tags": ["pro", "eu"]}
{"id": 2, "name": "Bob", "address": {"city": "Lyon", "zip": "69001"}, "tags": ["eu"]}

read_json выведет такую схему:

DESCRIBE SELECT * FROM read_json('users.ndjson');

┌─────────────┬────────────────────────────────────────┐
│ column_name │              column_type               │
│   varchar   │                varchar                 │
├─────────────┼────────────────────────────────────────┤
│ id          │ BIGINT                                 │
│ name        │ VARCHAR                                │
│ address     │ STRUCT(city VARCHAR, zip VARCHAR)      │
│ tags        │ VARCHAR[]                              │
└─────────────┴────────────────────────────────────────┘

Колонка address — это STRUCT, колонка tags — список строк. DuckDB не «сплющил» JSON в плоскую таблицу и не оставил его текстом — он отразил вложенность один в один.

Доступ к вложенным данным

Раз вложенность сохранена в типах, к ней применимы обычные операторы DuckDB для вложенных типов. К полю STRUCT обращаются через точку, к элементу LIST — через индекс в квадратных скобках (индексация с 1):

SELECT
    name,
    address.city          AS city,   -- поле struct через точку
    tags[1]               AS main_tag, -- элемент списка, индекс с 1
    len(tags)             AS tag_count
FROM read_json('users.ndjson');

┌─────────┬─────────┬──────────┬───────────┐
│  name   │  city   │ main_tag │ tag_count │
│ varchar │ varchar │ varchar  │   int64   │
├─────────┼─────────┼──────────┼───────────┤
│ Anna    │ Berlin  │ pro      │         2 │
│ Bob     │ Lyon    │ eu       │         1 │
└─────────┴─────────┴──────────┴───────────┘

Списки можно «развернуть» в строки функцией unnest — каждый элемент станет отдельной строкой. Это превращает один документ во много строк и нужно постоянно при работе с JSON, где интересные данные лежат внутри массива:

-- Одна строка на каждый тег каждого пользователя
SELECT name, unnest(tags) AS tag
FROM read_json('users.ndjson');

┌─────────┬─────────┐
│  name   │   tag   │
│ varchar │ varchar │
├─────────┼─────────┤
│ Anna    │ pro     │
│ Anna    │ eu      │
│ Bob     │ eu      │
└─────────┴─────────┘

Когда структура нерегулярна

Автоопределение работает гладко, пока документы однородны. Реальный JSON часто не таков: у одних объектов есть поле, у других нет; одно и то же поле где-то число, где-то строка; глубина вложенности разная. У read_json есть рычаги на эти случаи.

maximum_depth ограничивает глубину, на которую разбирается структура. Глубже этого уровня DuckDB не выводит STRUCT, а оставляет поддерево как тип JSON (фактически строку с JSON-содержимым). Это спасает от взрыва числа колонок на сильно или непредсказуемо вложенных данных.

union_by_name = true нужен, когда в файле (или в нескольких файлах по globу) у объектов разный набор ключей. С ним итоговая схема — объединение всех встреченных полей; где поля в документе не было, в колонке окажется NULL.

columns задаёт схему явно, как и у CSV: фаза вывода пропускается, чтение быстрее и предсказуемо.

-- Не разворачивать структуру глубже 2 уровней
SELECT * FROM read_json('deep.ndjson', maximum_depth = 2);

-- Объединить разнородные документы по именам полей
SELECT * FROM read_json('mixed/*.ndjson', union_by_name = true);

Тип JSON и точечная навигация

Иногда схему выводить не нужно или нельзя — данные слишком нерегулярны. Тогда столбец можно держать как тип JSON и доставать значения JSON-функциями. Оператор -> извлекает элемент как JSON, оператор ->> — сразу как текст. Поддерживаются JSONPath-выражения:

SELECT
    payload ->> '$.user.name'        AS user_name,
    payload ->  '$.items[0].price'   AS first_price
FROM read_json('raw.ndjson', columns = {'payload': 'JSON'});

Это компромисс: гибкость вместо строгой схемы. Колоночные оптимизации (projection pushdown по конкретному полю) на сыром JSON-столбце не работают так же хорошо, как на выведенных STRUCT-колонках, потому что движок видит один большой текстовый столбец. Поэтому, если структура у данных всё-таки регулярная, выведенная схема со STRUCT почти всегда лучше сырого JSON.

Попробуй сам

Подготовьте NDJSON-файл с вложенностью — например, экспорт событий, где у каждого события есть объект context и массив items.

1. Выполните DESCRIBE SELECT * FROM read_json('events.ndjson') и разберите выведенную схему: какие поля стали STRUCT, какие LIST, какие примитивами.
2. Достаньте вложенное поле через точку (context.session_id) и элемент массива по индексу (items[1]). Затем разверните массив через unnest(items) и сравните число строк до и после.
3. Добавьте в файл несколько строк, где какого-то ключа нет, а в другой — где знакомое поле имеет другой тип. Перечитайте файл и посмотрите через DESCRIBE, как изменилась схема. Затем прочитайте с union_by_name = true и сравните.
4. Прочитайте тот же файл с maximum_depth = 1 и посмотрите, какие колонки превратились в тип JSON вместо STRUCT. Объясните, почему это полезно на непредсказуемо вложенных данных.