jsonb_pretty en PostgreSQL: salida JSONB legible

jsonb_pretty — это функция PostgreSQL, которая берёт значение типа jsonb и возвращает его как text, разложенный по строкам с отступами в два пробела на уровень вложенности. Нужна она там, где JSONB читает человек: в psql документ хранится как плотная однострочная лента без переносов, и jsonb_pretty превращает эту стену символов в аккуратное дерево, где каждый ключ стоит на своей строке. Это инструмент для отладки, ревью данных и разбора логов — а не для хранения в колонке и не для передачи по сети.

Базовое использование

Вызов простой: jsonb_pretty принимает ровно один аргумент типа jsonb и возвращает text. Проще всего почувствовать разницу, поставив компактную и развёрнутую форму одного документа рядом — передаём литерал прямо в функцию.

SELECT jsonb_pretty('{"name":"Ana","country":"ES","roles":["admin","ops"]}');

Вместо одной строки jsonb_pretty выдаёт развёрнутое дерево, которое читается без напряжения:

{
    "name": "Ana",
    "roles": [
        "admin",
        "ops"
    ],
    "country": "ES"
}

Заметили? country уехал вниз, за roles, хотя в исходнике стоял вторым. JSONB не хранит порядок ключей — он раскладывает их по собственному правилу (сначала короткие: name, потом roles, потом country), и jsonb_pretty печатает именно этот порядок. К самому правилу мы ещё вернёмся — там кроется маленький, но важный нюанс.

Инспекция реальных документов

Допустим, в таблице users есть колонка profile jsonb — настройки, флаги, всякая метаинформация. Без форматирования такая строка в psql выглядит как одна нескончаемая лента, по которой невозможно водить пальцем. Оберните колонку в jsonb_pretty прямо в SELECT — и каждый ключ встанет на свою строку, на своё место, не меняя данных в таблице.

SELECT u.id, jsonb_pretty(u.profile) AS profile
FROM users u
WHERE u.country = 'ES'
ORDER BY u.created_at DESC
LIMIT 5;

По-настоящему jsonb_pretty раскрывается на агрегатах. Соберём заказы пользователя в единый документ через jsonb_agg и тут же на него взглянем:

SELECT jsonb_pretty(
  jsonb_agg(
    jsonb_build_object(
      'order_id', o.id,
      'amount', o.amount,
      'status', o.status
    ) ORDER BY o.created_at
  )
) AS orders
FROM orders o
WHERE o.user_id = 42;

Так легко убедиться, что агрегация и jsonb_build_object собирают ровно ту структуру, какую вы ждёте, ещё до того как отдать её в приложение. На вложенных документах контраст ещё резче: глубокий объект с массивами внутри в одну строку не прочитает никто, а с отступами видно каждый уровень и каждую запятую. Тот же приём выручает и в обычном psql-сеансе, и при \copy, когда нужно выгрузить кусочек данных для багрепорта или показать его коллеге.

Сортировка ключей и нормализация

При разборе JSONB выбрасывает пробелы, схлопывает дубликаты ключей (побеждает последний) и не хранит исходный порядок. Внутри ключи лежат отсортированными сначала по длине, а при равной длине — побайтово (по коду символа). jsonb_pretty печатает их в этом же внутреннем порядке, ничего не переставляя от себя, поэтому вывод иногда удивляет.

SELECT jsonb_pretty('{"z":1,"a":2,"aa":3}');

Здесь легко ошибиться. По длине вперёд выходят односимвольные a и z, и только потом двухсимвольный aa. А внутри длины 1 решает не алфавит как таковой, а байтовое значение: a имеет код 0x61, z — 0x7a, поэтому a идёт раньше z. На выходе порядок будет a, z, aa — а вовсе не z, a, aa, как можно решить, машинально читая ключи слева направо. Для латиницы в нижнем регистре побайтовый порядок совпадает с алфавитным, но как только в дело вступают цифры, верхний регистр или не-ASCII, привычная «азбука» перестаёт работать, и ориентироваться надо именно на коды символов.

Само свойство неочевидное, но крайне полезное: оно стабилизирует diff. Два логически идентичных документа дадут байт в байт одинаковый pretty-вывод, как бы по-разному их ни записали, — это спасает, когда в тестах сравниваешь ожидаемый и фактический JSON обычным текстовым diff. А вот если вам внезапно понадобился именно исходный порядок ключей, JSONB его не вернёт никогда: для этого есть тип json (без b), который хранит текст ровно как пришёл, но расплачивается повторным разбором при каждом обращении.

Когда не нужно

Главные грабли — затащить jsonb_pretty в боевой путь данных, туда, где его быть не должно.

• Хранение. Никогда не пишите результат jsonb_pretty обратно в колонку. JSONB и так хранится в компактном бинарном виде; отступы превращают его в text — это лишние байты и потерянный тип. Храните компактный jsonb, а отступы добавляйте только при чтении.
• Транспорт. Для API и очередей отдавайте row_to_json или компактный ::text. Отступы от jsonb_pretty раздувают трафик, а клиенту они даром не нужны — он всё равно распарсит документ заново.
• Производительность. jsonb_pretty форматирует каждую строку результата отдельно. Не оборачивайте в него тяжёлые выборки на миллионы строк — только точечную отладку с LIMIT.

Подвох: jsonb_pretty отдаёт text, а не jsonb. Примените её, а потом попробуйте обратиться к результату через ->> или @> — и выражение свалится с ошибкой типа, потому что операторы JSONB к тексту неприменимы. Порядок обратный: сначала фильтруете и извлекаете по jsonb, а jsonb_pretty навешиваете в самом конце, исключительно ради отображения.

Отличия в других СУБД

• MySQL. Функции с именем jsonb_pretty нет, но есть встроенная JSON_PRETTY(doc), которая делает ровно то же самое для типа JSON — разворачивает документ с отступами для чтения.
• ClickHouse. Колоночная аналитика, прямого аналога jsonb_pretty внутри выражения нет; читаемый вид задают форматом вывода клиента (например FORMAT PrettyJSONEachRow), а не вызовом функции в проекции.

Итог короткий: jsonb_pretty — удобство для разработчика, и только. Вставляйте его в SELECT, когда руками копаетесь в JSONB через psql или готовите репорт, но сами данные в колонках и каналах держите компактным jsonb.