jsonb_pretty в PostgreSQL: как красиво вывести JSONB для чтения и отладки

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

В таблице JSONB может лежать примерно так:

{"name":"Ana","country":"ES","roles":["admin","ops"],"settings":{"theme":"dark","email":true}}

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

Для таких случаев в PostgreSQL есть функция jsonb_pretty. Она берёт значение типа jsonb и возвращает его как обычный text, но уже красиво разложенный по строкам с отступами.

Проще говоря:

jsonb_pretty превращает плотную JSONB-строку в аккуратное дерево, которое удобно читать глазами.

Это не функция для хранения данных и не функция для API. Это инструмент для отладки, ревью и спокойного разбора сложных JSONB-документов.

Что делает jsonb_pretty

Функция принимает один аргумент типа jsonb и возвращает text.

Базовый пример:

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

Без форматирования документ выглядит так:

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

А после jsonb_pretty — так:

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

Читать стало намного проще:

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

Это особенно удобно в psql, когда вы руками смотрите данные из таблицы и пытаетесь понять, что именно лежит внутри JSONB-колонки.

Важный момент: результат — это text

jsonb_pretty возвращает не jsonb, а text.

Это очень важно.

После форматирования результат нужен только для отображения. С ним уже нельзя работать как с JSONB-документом через операторы ->, ->>, @> и другие JSONB-инструменты.

Правильный порядок такой:

1. Сначала фильтруем, ищем, извлекаем поля и проверяем условия по настоящему jsonb.
2. В самом конце оборачиваем результат в jsonb_pretty, чтобы красиво показать человеку.

Например:

SELECT jsonb_pretty(profile) AS profile_pretty
FROM users
WHERE profile ->> 'country' = 'ES';

Здесь условие работает по колонке profile, которая имеет тип jsonb.

А вот так делать не нужно:

SELECT jsonb_pretty(profile) ->> 'country' AS country
FROM users;

Такой запрос сломается, потому что jsonb_pretty(profile) уже вернул text, а оператор ->> применяется к JSON или JSONB, а не к тексту.

Запомнить можно просто:

jsonb_pretty — последний штрих перед показом результата человеку.

Пример с таблицей пользователей

Допустим, есть таблица пользователей:

CREATE TABLE users (
    id         bigint PRIMARY KEY,
    country    text NOT NULL,
    created_at date NOT NULL,
    profile    jsonb NOT NULL
);

В колонке profile хранятся настройки, роли, флаги, язык интерфейса, параметры уведомлений.

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

SELECT id, profile
FROM users
WHERE country = 'ES'
ORDER BY created_at DESC
LIMIT 5;

В psql вы увидите длинные строки. Если документов несколько, глаза быстро устанут.

Добавим jsonb_pretty:

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

Теперь каждый профиль будет выведен в читаемом виде.

Данные в таблице при этом не меняются. Мы не переписываем колонку, не добавляем отступы в хранилище, не превращаем jsonb в text навсегда. Мы просто просим PostgreSQL красиво показать документ в результате запроса.

Когда jsonb_pretty особенно полезен

jsonb_pretty хорошо подходит для ситуаций, где JSONB читает человек.

Например:

• посмотреть профиль пользователя;
• проверить настройки аккаунта;
• разобрать тело события из лога;
• изучить ответ внешнего API, сохранённый в базе;
• проверить результат jsonb_build_object;
• посмотреть, что получилось после jsonb_agg;
• подготовить фрагмент данных для багрепорта;
• показать коллеге структуру документа.

Представьте, что у пользователя есть сложный профиль:

{"name":"Ana","settings":{"theme":"dark","notifications":{"email":true,"sms":false}},"roles":["admin","ops"]}

В одну строку это читается тяжело.

А так уже понятно:

{
    "name": "Ana",
    "roles": [
        "admin",
        "ops"
    ],
    "settings": {
        "theme": "dark",
        "notifications": {
            "sms": false,
            "email": true
        }
    }
}

На маленьких документах разница приятная. На больших — спасительная.

Проверка JSON, собранного в SQL

Одна из самых полезных задач — смотреть не только готовые JSONB-колонки, но и документы, которые вы собираете прямо в запросе.

Например, нужно собрать список заказов пользователя в JSONB-массив:

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 создаёт JSONB-объект для одного заказа.

jsonb_agg собирает такие объекты в массив.

ORDER BY o.created_at внутри агрегации задаёт порядок заказов в массиве.

jsonb_pretty в конце красиво показывает результат.

Без jsonb_pretty вы получите длинную строку. С ним — понятный массив объектов:

[
    {
        "amount": 1200.00,
        "status": "paid",
        "order_id": 101
    },
    {
        "amount": 850.00,
        "status": "paid",
        "order_id": 102
    }
]

Так удобно проверять, правильно ли вы собрали структуру ещё до того, как её заберёт приложение.

Почему порядок ключей может измениться

Есть важная особенность jsonb.

Тип jsonb не хранит исходный текст документа как есть. Он разбирает JSON, нормализует его и хранит в удобном для поиска и обработки виде.

Из-за этого jsonb:

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

Поэтому такой пример может удивить:

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

В исходном документе порядок ключей такой:

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

А в красивом выводе может быть так:

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

country оказался после roles.

Это не ошибка jsonb_pretty. Функция не пытается восстановить ваш исходный порядок. Она печатает документ так, как он лежит в jsonb.

Главный вывод:

если вам важен исходный порядок ключей, тип jsonb не подходит для этой задачи.

В большинстве рабочих сценариев порядок ключей в JSON-объекте не должен иметь значения. Для приложения важно, что есть ключ country со значением ES, а не то, был он вторым или третьим в исходной строке.

JSONB нормализует документ

Посмотрим на ещё один пример:

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

Вы можете ожидать порядок z, a, aa, потому что именно так ключи написаны в исходном тексте.

Но jsonb не обязан сохранить этот порядок. Он хранит объект в нормализованном виде и выводит ключи по своим внутренним правилам.

Это полезно для сравнения документов.

Например, два JSON-документа записаны по-разному:

{"a":2,"z":1,"aa":3}

{"z":1,"aa":3,"a":2}

Для человека они выглядят разными, но как JSONB-документы могут означать одно и то же: те же ключи, те же значения.

После приведения к jsonb и вывода через jsonb_pretty такие документы будут выглядеть стабильно. Это удобно в тестах, ревью и текстовых diff-сравнениях: меньше шума от случайного порядка ключей.

Но за это есть цена: исходный порядок уже не вернуть.

json и jsonb: в чём разница для красивого вывода

В PostgreSQL есть два похожих типа: json и jsonb.

json хранит текст JSON ближе к тому виду, в котором он пришёл.

jsonb хранит разобранное бинарное представление, удобное для индексов, поиска и операторов.

Для большинства рабочих задач в PostgreSQL чаще выбирают jsonb, потому что с ним удобнее фильтровать и искать:

SELECT id
FROM users
WHERE profile @> '{"role":"admin"}'::jsonb;

Но если вам по какой-то редкой причине нужно сохранить исходный порядок ключей и исходное форматирование, jsonb этого не гарантирует.

jsonb_pretty работает именно с jsonb, поэтому он показывает нормализованный документ, а не оригинальный текст, который когда-то был вставлен.

Не храните результат jsonb_pretty в колонке

Самая плохая идея — взять результат jsonb_pretty и записать его обратно в таблицу.

Например, так делать не нужно:

UPDATE users
SET profile_text = jsonb_pretty(profile);

Если это отдельная временная колонка для ручной диагностики — ещё можно понять. Но как обычная практика хранения это почти всегда лишнее.

Почему?

Во-первых, jsonb_pretty возвращает text, а не jsonb.

Во-вторых, отступы и переносы строк занимают лишнее место.

В-третьих, вы теряете удобство JSONB-операторов и индексов, если начинаете хранить красивый текст вместо структурированного типа.

Правильная стратегия:

храните компактный jsonb, а красивый вывод делайте только в момент чтения.

То есть в таблице пусть лежит нормальный jsonb, а в отладочном запросе используйте jsonb_pretty.

Не используйте jsonb_pretty в API и очередях

Ещё одна ловушка — отправлять красивый JSON наружу: в API, Kafka, RabbitMQ, файл обмена или куда-то ещё.

На первый взгляд красиво: отступы, переносы, всё читается. Но для машин это лишнее.

Клиентское приложение всё равно будет парсить JSON как структуру. Ему не нужны пробелы и переносы строк. А трафик и размер сообщений вырастут.

Для API и очередей обычно лучше отдавать компактный JSON:

SELECT profile::text AS profile
FROM users
WHERE id = 42;

Или собирать документ через JSON-функции:

SELECT row_to_json(u) AS user_data
FROM users u
WHERE u.id = 42;

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

Осторожно с большими выборками

jsonb_pretty форматирует каждую строку результата.

Если вы сделаете так на миллионах строк:

SELECT jsonb_pretty(payload)
FROM events;

база будет тратить время на красивое форматирование огромного количества документов. А вы получите гигантский результат, который всё равно невозможно прочитать руками.

Для отладки почти всегда нужен маленький фрагмент:

SELECT
    id,
    jsonb_pretty(payload) AS payload
FROM events
WHERE event_type = 'payment_failed'
ORDER BY created_at DESC
LIMIT 10;

Такой запрос разумен: мы нашли конкретные события и красиво посмотрели только несколько документов.

Общее правило:

jsonb_pretty хорошо сочетается с точечным WHERE и небольшим LIMIT, но плохо подходит для тяжёлой массовой выгрузки.

Удобный приём для psql

Если вы работаете в psql, длинные JSONB-документы могут всё равно выглядеть неидеально, особенно если рядом много колонок.

Часто удобно выбирать только нужный идентификатор и красивый JSONB:

SELECT
    id,
    jsonb_pretty(profile) AS profile
FROM users
WHERE id = 42;

А если документ очень широкий, можно включить расширенный вывод в psql командой:

\x

Это не SQL-запрос, а команда клиента psql. Она показывает каждую строку результата вертикально, и большие JSONB-документы становятся ещё удобнее для чтения.

Пример для багрепорта

Допустим, пользователь жалуется: в интерфейсе не показывается настройка уведомлений. В базе настройки лежат в profile.

Можно быстро достать читаемый фрагмент:

SELECT
    id,
    jsonb_pretty(profile) AS profile
FROM users
WHERE id = 42;

В багрепорт можно вставить результат:

{
    "name": "Ana",
    "settings": {
        "theme": "dark",
        "notifications": {
            "sms": false,
            "email": true
        }
    }
}

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

Вот для таких задач jsonb_pretty и создан: быстро сделать JSONB понятным человеку.

Аналоги в других СУБД

В MySQL нет функции с названием jsonb_pretty, потому что там нет типа jsonb как в PostgreSQL. Но есть похожая функция JSON_PRETTY, которая форматирует значение типа JSON в читаемый вид.

Пример:

SELECT JSON_PRETTY(profile) AS profile
FROM users;

В ClickHouse подход другой. Там читаемый JSON-вывод чаще задают не функцией в запросе, а форматом результата на уровне клиента. Например, можно использовать JSON-форматы вывода, если задача — красиво посмотреть результат запроса.

То есть идея похожая, но инструмент зависит от СУБД:

• в PostgreSQL — jsonb_pretty;
• в MySQL — JSON_PRETTY;
• в ClickHouse — форматы вывода и функции под конкретную задачу.

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

jsonb_pretty не нужен, если JSON читает не человек, а программа.

Не стоит использовать его:

• при сохранении данных в таблицу;
• в API-ответах по умолчанию;
• в сообщениях очередей;
• в тяжёлых выгрузках;
• в условиях WHERE;
• в индексах;
• перед JSONB-операторами.

Это функция не для обработки JSONB, а для красивого отображения.

Если нужно достать поле, используйте JSONB-операторы:

SELECT profile ->> 'country' AS country
FROM users;

Если нужно проверить наличие фрагмента, используйте JSONB-условия:

SELECT id
FROM users
WHERE profile @> '{"country":"ES"}'::jsonb;

Если нужно красиво показать результат — добавляйте jsonb_pretty в самом конце:

SELECT jsonb_pretty(profile) AS profile
FROM users
WHERE profile @> '{"country":"ES"}'::jsonb;

Главное из статьи

jsonb_pretty — функция PostgreSQL для красивого вывода значений типа jsonb.

Она принимает jsonb, а возвращает text с переносами строк и отступами.

Базовый вызов выглядит так:

SELECT jsonb_pretty(profile) AS profile
FROM users;

Функция полезна для отладки, ревью данных, анализа логов, проверки результата jsonb_build_object и jsonb_agg.

jsonb_pretty не меняет данные в таблице. Она только форматирует значение в результате запроса.

После jsonb_pretty результат уже не является jsonb, поэтому к нему нельзя применять JSONB-операторы вроде ->>, @> и похожие.

jsonb не сохраняет исходный порядок ключей, поэтому красивый вывод может отличаться от порядка в исходном JSON-тексте.

Не храните результат jsonb_pretty в колонках и не используйте его в API без необходимости. Для хранения, поиска и передачи данных оставляйте компактный jsonb.

Главное правило простое:

работайте с данными как с jsonb, а jsonb_pretty используйте только в конце, когда результат нужно удобно прочитать глазами.