Документ в ячейке: объект, массив, значение

Глава 1 — «JSON в ячейке»

Сортировочная осталась за переборкой. Соседний отсек Приёмного уровня на станции зовут Распаковочной: вдоль стен тянутся стеллажи, а на полках лежат посылки. Так здесь называют вторую часть первого сигнала. Вместе с базой «Котомаркета» и сырым текстом пришла служебная переписка машин магазина за две последние недели перед обрывом, с 10 по 24 февраля 2025 года.

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

Комендант ставит задачу в двух строках. Ему нужна сводка о последних двух неделях магазина: что покупали, сколько было заказов, как платили.

Ты открываешь первую посылку, событие 3 из таблицы app_events. Четыре колонки, на вид ничего необычного: номер, время, тип purchase. Но в четвёртой, payload, уместился целый заказ. Там покупатель и его город, две позиции с ценами, пустой список купонов и итог. Для таблицы это одна ячейка. Внутри у неё целое дерево.

КВЕРИ: Представь таблицу как шкаф с полками, а JSON как коробку с коробками внутри. Пока наша задача простая: понять, что где лежит и что потеряется, если переложить содержимое.

Значения по одному пока не достаём. Берём одну такую ячейку и разбираем её устройство: где объект, где массив, какого типа отдельные значения и что именно PostgreSQL делает с исходным текстом при превращении в документ.

Из чего собран документ

Возьмём событие попроще. Событие 1 — просмотр наушников, и его payload помещается в одну строку. PostgreSQL печатает его так:

{"app": "4.6.0", "user": {"id": 23, "city": "Санкт-Петербург"}, "price": 5990, "product_id": 1, "schema_version": 2}

Фигурные скобки ограничивают объект. Внутри через запятую записаны пары «ключ: значение». Ключ всегда строка в двойных кавычках: "app", "price", "product_id". А значения уже разные. "4.6.0" — строка, поэтому она тоже в двойных кавычках. 5990 записан без кавычек: это число. У ключа "user" значение сложнее — ещё один объект с ключами id и city. Получилась коробка внутри коробки.

Теперь вернёмся к заказу из сцены, событию 3. Там есть ключ "items", а его значение начинается с квадратной скобки: [{...}, {...}]. Квадратные скобки обозначают массив — упорядоченный список значений через запятую. У элементов массива нет ключей, зато есть порядок: первый, второй и так далее. В заказе 5001 массив items содержит два объекта-позиции, наушники и футболки. Рядом записано "coupons": []. Перед нами ещё один массив, только пустой: купонов в заказе не было.

Значение без вложенных объектов и массивов называют скаляром. В JSON таких вариантов четыре:

• строка в двойных кавычках: "Санкт-Петербург";
• число без кавычек: 5990, 1.2, -3;
• логическое значение true или false (у беспроводных наушников в карточке записано "wireless": true);
• null, значение «ничего».

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

JSON расшифровывается как JavaScript Object Notation. Запись выросла из синтаксиса объектов JavaScript, а в начале 2000-х Дуглас Крокфорд описал её как самостоятельный формат обмена данными. Сейчас JSON постоянно прилетает из веб-сервисов, поэтому встретить его в базе скорее обычное дело, чем исключение.

Где JSON встречается в работе

Посылки пришли из пяти мест. Такие источники постоянно встречаются в продуктовых базах.

app_events.payload — события мобильного приложения. Набор свойств зависит от события, поэтому разработчики не заводят под каждое поле отдельную колонку. Просмотру хватает товара и цены, а оформлению заказа уже нужны позиции, купоны и итог.

payments.response — ответы платёжного шлюза. Это чужой API, и магазин не контролирует, какие поля появятся там завтра. Поэтому ответ удобно сохранить целиком таким, каким его прислал шлюз, а нужные поля разобрать позже.

product_cards.attrs — характеристики товаров. У книги есть число страниц и год издания, у наушников батарея и цвета, у кофеварки мощность. Если делать колонку под каждую характеристику, таблица быстро расползётся, поэтому такие свойства часто складывают в документ.

user_settings.settings — настройки покупателей: тема оформления, рассылка, уведомления, флаги экспериментов. Приложение добавило новый параметр — появился ещё один ключ, без отдельной миграции таблицы.

raw_inbox.body — входящий поток за последний вечер: вебхуки, импорт, сообщения партнёров и бота поддержки. Это уже настоящий сырьевой ящик: сюда записывают входные данные без проверки.

В чужих проектах я чаще всего встречал JSON именно в ответах внешних API. Команда сохраняет весь ответ «на всякий случай», а через полгода внезапно выясняется, что редкое поле из него действительно понадобилось.

Ячейка ниже берёт по одному документу из каждой таблицы и показывает тип колонки, где он хранится. Документы приведены к text, чтобы все пять строк поместились в одну колонку doc и PostgreSQL показал их как текст. Перед запуском сделай прогноз: в четырёх таблицах колонка с документом имеет тип jsonb. Что тогда увидим у raw_inbox.body, куда данные пишут без проверки?

Документ лесенкой: jsonb_pretty

Строка платежа в предыдущем выводе тянется далеко вправо. На таком тексте быстро перестаёшь видеть, где закрылся вложенный объект и к какому уровню относится следующий ключ.

Для этого у PostgreSQL есть jsonb_pretty. Функция печатает документ лесенкой: каждый ключ переносит на отдельную строку, а каждый уровень вложенности сдвигает вправо на четыре пробела. На вход ей нужен jsonb, результатом будет обычный текст.

Возьмём заказ из сцены. Перед запуском посчитай по схеме выше глубину самой длинной ветки: внешний объект, затем items, затем одна позиция. Заодно попробуй угадать, как в такой лесенке будет выглядеть пустой массив coupons.

Следующий документ открой уже без схемы. Возьми платёж 2, тот самый отказ из обзора, и до запуска найди три вещи: на какой глубине лежит город покупателя, сколько элементов в массиве charges и где шлюз записал причину отказа.

Какого типа значение: jsonb_typeof

У raw_inbox колонка body имеет тип text. PostgreSQL видит там только символы, поэтому JSON-функции ещё применять не к чему. Текст можно превратить в документ приведением типа: body::jsonb или полной записью CAST(body AS jsonb). В этот момент PostgreSQL разбирает строку по правилам JSON и строит дерево значений.

После этого можно спросить у базы, что получилось. Функция jsonb_typeof вернёт одно из шести слов: object, array, string, number, boolean или null.

Но у сырого потока есть проблема: туда писали без проверки, поэтому часть строк вообще не JSON. Если сразу привести к jsonb всю колонку, запрос остановится на первой плохой строке:

SELECT msg_id, jsonb_typeof(body::jsonb) AS kind
FROM raw_inbox;

ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.

Первой помехой стало пустое сообщение 7. Из двенадцати сообщений валидный JSON лежит в семи: с первого по шестое и в десятом. В пятой главе построим проверку, которая найдёт такие строки без падения запроса. Сейчас номера семи известных сообщений укажем явно.

Сначала прогноз. Сообщения 1 и 2 начинаются с фигурной скобки, значит, перед нами объекты. Сообщение 3 начинается с квадратной — массив. Остались 4, 5 и 6. Особенно присмотрись к четвёртому: весь его текст снаружи заключён в двойные кавычки.

[], 42, "hello" и null — тоже JSON

Здесь часто ошибаются те, кто привык видеть JSON только в ответах API. Сервис обычно возвращает объект, реже массив, и постепенно закрепляется ложная привычка: искать обязательно фигурные или квадратные скобки.

По действующему стандарту JSON, RFC 8259, на верхнем уровне может стоять любое значение: число, строка, true, false, null. А вот старая спецификация RFC 4627 2006 года действительно требовала объект или массив. Отсюда и взялось правило, которое до сих пор всплывает в коде и разговорах. PostgreSQL работает по современному варианту и принимает скаляры.

Сообщение 4 особенно показательное. Отправитель превратил объект в текст, а затем этот текст ещё раз упаковал в JSON-строку. У такого случая есть название — двойная сериализация. В вебхуках я встречал её не раз. Внутри строки действительно виден документ, но PostgreSQL пока воспринимает всё целиком как одну строку без доступных ключей.

С сообщением 6 другая ловушка. В ячейке записаны четыре символа: n, u, l, l. После ::jsonb они превращаются в JSON-значение null. SQL NULL устроен иначе: колонка body объявлена NOT NULL, и эта конкретная ячейка совершенно точно заполнена. К разнице между двумя вариантами «ничего» вернёмся в третьем уроке главы.

Теперь проверим это на коротких литералах. Ниже шесть строк: каждую приводим к jsonb, затем спрашиваем её тип. До запуска отметь, какие строки, по-твоему, пройдут. Отдельно реши судьбу 'null': будет ошибка, SQL NULL или значение третьего вида?

text, json, jsonb: три способа хранить документ

Один и тот же JSON PostgreSQL умеет хранить тремя способами. На коротком документе разница почти незаметна. Но как только начинаешь читать поля, сравнивать документы или разбирать сырой поток, тип уже влияет на результат.

text хранит просто символы и не проверяет, получился ли из них JSON. Поэтому в raw_inbox.body спокойно соседствуют пустая строка, жалоба на курьера и JSON с хвостом OK. Чтобы потом обратиться к такому содержимому как к документу, тип придётся привести явно.

json проверяет синтаксис при записи, а потом сохраняет исходный текст буква в букву: пробелы, порядок ключей и даже повторы остаются на месте. Когда читаешь поле, PostgreSQL снова разбирает сохранённый текст.

jsonb разбирает документ при записи и хранит уже готовое дерево в двоичном виде. Поля из него читать быстрее, документы можно сравнивать, а для поиска по ним строят индексы — до индексов дойдём в шестой главе. Но исходную строку после такой записи уже не восстановить. Лишние пробелы пропадут, ключи база напечатает в своём порядке, а из повторяющихся ключей сохранится последнее значение.

Документация PostgreSQL рекомендует jsonb для большинства прикладных задач. json нужен реже, например когда надо сохранить исходный порядок ключей. В нашем датасете четыре колонки с разобранными документами имеют тип jsonb.

Возьмём строку, в которой различия специально сделаны заметными. После 18, идут три пробела подряд, ключ qty повторён дважды, а первым стоит product_id. Пробелы добавлены через repeat(' ', 3): функция повторяет пробел три раза, а || склеивает части строки. Так число пробелов видно прямо в запросе. Перед запуском попробуй угадать, какой текст окажется в каждой из трёх колонок после приведения.

В конце as_json и as_jsonb стоит ::text. Из-за этого все три результата становятся обычным текстом, и их можно сравнить глазами символ за символом именно в том виде, в котором их возвращает PostgreSQL.

Повтор ключа в настоящем сообщении

Предыдущий литерал был нарочно составлен для опыта, но повторяющиеся ключи встречаются и в настоящих данных. Сообщение 10 партнёр прислал в 23:44. Сначала там записан status со значением "queued", затем объект data с событием, а в конце снова status, уже со значением "sent". Судя по записи, система партнёра дописала новый статус в конец документа и не удалила старый.

Стандарт JSON прямо не запрещает такую запись, но предупреждает: программы могут обрабатывать повтор ключа по-разному. PostgreSQL ведёт себя предсказуемо для своих двух типов: json сохраняет оба значения, а jsonb оставляет последнее.

Перед запуском зафиксируй прогноз. Какой статус останется в as_jsonb? Сколько раз status встретится в as_json? И посмотри на порядок: какой ключ PostgreSQL напечатает первым в as_jsonb?

Порядок ключей и пробелы

Мы уже два раза поймали jsonb на перестановке ключей. В учебном литерале qty оказался раньше product_id. В заказе из сцены schema_version напечатался в самом низу, хотя в исходном событии стоял первым. Причина во внутреннем представлении jsonb: PostgreSQL хранит объект в своём порядке и так же его печатает. Здесь порядок такой: сначала идут ключи меньшей длины в байтах, а среди ключей одинаковой длины — побайтно. Документация не гарантирует этот порядок, поэтому строить на нём логику нельзя.

Исходные пробелы тоже не сохраняются. При выводе jsonb PostgreSQL сам ставит по одному пробелу после двоеточия и запятой, сколько бы их ни было во входной строке.

Ниже три сообщения сырого потока: слева исходный текст, справа результат после jsonb. Сначала попробуй угадать, что изменится. В сообщении 1 ключи записаны как event, user_id, product_id. В сообщении 2 сначала идёт event, затем user. Сообщение 3 отличается: это массив из двух объектов. Отдельно подумай, имеет ли PostgreSQL право переставить элементы самого массива.

Сравниваем документы по содержанию

Теперь понятно, почему сравнивать JSON как обычный текст рискованно. Два документа могут содержать одинаковые ключи и значения, но отличаться пробелами или их порядком. Строковое = увидит разные символы. Сравнение двух значений jsonb проверит содержание и в таком случае вернёт true.

Есть две границы, о которых легко забыть. В массиве порядок элементов входит в содержание, поэтому [6, 7] и [7, 6] не равны. Тип значения тоже учитывается: число 1290 отличается от строки "1290", хотя глазами цифры выглядят одинаково.

У json оператора равенства вообще нет. Запрос body::json = body::json закончится ошибкой operator does not exist: json = json, а попытка сделать DISTINCT или GROUP BY по json даст could not identify an equality operator for type json. Для документов, которые приходится сравнивать и группировать, jsonb поэтому заметно удобнее.

Ниже три пары, и каждую мы сравним дважды: как текст и как jsonb. Первую пару можно решить сразу. Ключи и значения совпадают, отличаются только порядок и пробелы, поэтому same_text будет ложным, а same_jsonb истинным. Для второй и третьей сначала запиши свой ответ, потом нажимай «Запустить».

Финальная задача урока. Интегратор пишет в поддержку «Котомаркета»: «Отправили вам вебхуком {"product_id":12,"event":"cart","user":{"id":23}}. Дошло?» Дежурный аналитик сравнил присланный текст с каждым валидным сообщением и ответил: нет.

Сам запрос ниже работает без ошибок, но во всех семи строках получается false. Прежде чем что-то править, найди глазами в сыром потоке сообщение, которое по содержанию похоже на документ интегратора.

Частые ошибки

Считать JSON только объектом

Строка: сообщение 5, текст 42.

Ожидание: раз нет скобок и ключей, значит, это вроде бы не JSON.

Проверка быстро ломает это предположение: jsonb_typeof(body::jsonb) возвращает number. На верхнем уровне JSON допустим не только объект. В нашем сыром потоке четыре валидных сообщения пришли в другом виде: массив 3, строка 4, число 5 и null 6.

Что делать: перед разбором колонки посчитай типы верхнего уровня. Если там встретился не только object, один общий запрос для всех строк может дать неверный результат.

Путать JSON null и SQL NULL

Строка: сообщение 6, текст null.

Ожидание: его найдёт условие body IS NULL.

Запусти проверку — результат будет false. В ячейке лежат четыре буквы, которые после приведения становятся JSON-значением null. SQL NULL означает другую ситуацию: значения в самой ячейке нет.

Что делать: для JSON null jsonb_typeof вернёт строку null, а для настоящего SQL NULL результатом будет сам NULL. В третьем уроке разберём эту пару подробнее.

Приводить к jsonb всю сырую колонку

Строка: сообщение 7, пустой текст.

Ожидание: body::jsonb разберёт хорошие строки, а для мусора вернёт NULL.

На практике падает весь запрос с ошибкой invalid input syntax for type json. Приведение не умеет молча пропускать плохую строку: первая ошибка останавливает выполнение.

Что делать: пока приводи только строки, о которых уже известно, что они валидны. В пятой главе соберём безопасную проверку всей колонки.

Ждать от jsonb исходного вида

Строка: сообщение 10, ключ status дважды.

Ожидание: после ::jsonb запись останется ровно такой, какой её прислал партнёр.

Но "queued" пропадает, а ключи выводятся в другом порядке. jsonb хранит разобранное содержание и исходную строку уже не помнит.

Что делать: если исходная запись нужна для аудита, подписи вебхука или разбора чужой ошибки, сохраняй её отдельно в text или json.

Сравнивать документы как строки

Строка: сообщение 2 и документ интегратора.

Ожидание: одинаковое содержание должно дать одинаковые строки.

Здесь порядок ключей и пробелы различаются, поэтому обычный = возвращает false.

Что делать: сравнивай jsonb с jsonb. При этом [6, 7] и [7, 6] останутся разными, как и число 1290 со строкой "1290".

Перенос: первая встреча с JSON-колонкой

Когда я впервые открываю незнакомую колонку с документами, до отчётов обычно делаю четыре проверки. Они занимают несколько минут и хорошо ловят неприятные сюрпризы вроде сообщения 4 или пустой строки 7.

Сначала смотрю тип колонки. Для text, json и jsonb дальнейшие запросы будут разными: где-то понадобится приведение, а для сырого text ещё надо убедиться, что все строки его переживут. Тип можно увидеть в схеме таблицы или проверить через pg_typeof(колонка).

Затем открываю два-три документа через jsonb_pretty. Обычно беру короткий, длинный и один из середины периода. По одной строке легко решить, что все документы устроены одинаково; две-три уже часто показывают разные ветки или неожиданный тип.

После этого считаю типы верхнего уровня:

SELECT jsonb_typeof(payload) AS kind, count(*) AS events
FROM app_events
GROUP BY jsonb_typeof(payload)
ORDER BY events DESC;

Для app_events получится одна строка: все 36 событий имеют тип объекта. Это хороший результат, но лучше увидеть число 36 в выводе, чем предположить его по первым двум событиям. В сыром потоке картина уже другая: из семи валидных сообщений объектами оказались только три.

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