Вложенный JSON в плоскую таблицу
JSON устроен деревом, таблица — прямоугольник. Перевод одного в другое всегда компромисс, и важно понимать, какой именно компромисс вы выбираете.
Вложенные объекты: просто
Объект внутри объекта разворачивается в колонки через точку:
{"id": 1, "owner": {"fam": "Иванов", "im": "Пётр"}} → колонки id, owner.fam, owner.im.
Потерь нет, имена остаются понятными. Единственная тонкость — глубина: на пятом уровне вложенности имена становятся нечитаемыми, поэтому разумно ограничиться разумной глубиной и оставить остаток как есть.
Массивы: здесь начинается выбор
Массив примитивов — "tags": ["a", "b"] — разумно склеить в строку a, b. Читается глазами, ищется поиском, не плодит колонки.
Массив объектов — "items": [{...}, {...}] — это уже вторая таблица внутри первой. Вариантов три:
- оставить как JSON-строку в ячейке — ничего не теряется, но и работать с этим нельзя;
- развернуть в колонки
items.0.name,items.1.name— годится, когда элементов ровно два-три и всегда одинаково; - сделать массив самостоятельной таблицей — правильный ответ, когда элементов много.
Третий вариант — это и есть выбор «что считать строкой». Он же решает задачу в XML и описан в статье про разворачивание XML: механика одна и та же, отличается только синтаксис.
Разные ключи у разных объектов
В JSON никто не обещал, что у всех элементов массива одинаковый набор полей. У половины записей может не быть поля owner вовсе.
Колонки собираются как объединение ключей: если поле встретилось хотя бы у одного элемента, колонка появится, а у остальных будет пустой. Это честнее, чем брать ключи из первого элемента, — иначе часть данных исчезнет молча.
Практическое следствие: увидев в таблице колонку, заполненную на 3%, не спешите считать это ошибкой. Скорее всего, так устроен источник.
Где на самом деле лежит таблица
Выгрузки из API редко бывают голым массивом. Чаще это объект с метаданными, а данные — где-то внутри:
{"status": "ok", "payload": {"total": 1500, "items": [ ... ]}}
Искать массив нужно по всему документу, а не только в корне, и при нескольких кандидатах предлагать самый крупный массив объектов — обычно он и есть данные. Но выбор должен оставаться за человеком: иногда нужен как раз маленький массив справочника.
Частые вопросы
Почему в таблице колонка со значением вида {"a":1}?
Это вложенный объект, который не стали разворачивать — либо разворачивание выключено, либо превышена глубина.
Можно ли вернуть таблицу обратно в JSON?
Да, выгрузка в JSON и JSON Lines есть. Плоские колонки с точками при этом останутся плоскими — исходная вложенность не восстанавливается.
Что если ключи в JSON на русском?
Ничего особенного: они станут именами колонок как есть. Проблемы появятся только при выгрузке в DBF, где имя поля не длиннее десяти символов.