jsonb_pretty in PostgreSQL: Readable JSONB Output

jsonb_pretty formats a JSONB value with indentation and line breaks, turning a dense one-line document into readable text. It is a tool for human eyes: debugging in psql, reviewing data and logs, not for storage or transport.

Basic usage

The function takes a single jsonb value and returns text indented two spaces per nesting level. Compare the compact and expanded form of the same document.

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

The output is spread across lines and easy to scan:

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

Notice that country lands after roles. JSONB does not keep the original key order, which we cover below.

Inspecting real documents

Suppose users has a profile jsonb column with settings and metadata. Without formatting, a row in psql reads like a wall of text. With jsonb_pretty, every key sits on its own line.

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

It shines when checking aggregates. Build a user's orders into one document and look at it directly:

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;

This makes it easy to confirm that the aggregation and jsonb_build_object produce exactly the structure you expect before handing it to an application. The difference is even starker on nested documents: a deep object with arrays inside is unreadable on one line, while indentation exposes every level and every comma. The same trick works in a plain psql session and inside \copy when you export a sample of data for a bug report or a colleague.

Key sorting and normalization

When parsing, JSONB drops whitespace and duplicate keys (the last one wins) and does not preserve order. Internally keys are stored sorted by length first, and by byte value when lengths are equal. jsonb_pretty prints them in that same internal order, rearranging nothing of its own, so the output sometimes surprises you.

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

This is an easy spot to trip on. By length, the one-character keys a and z come before the two-character aa. Within length 1, the tie is broken not by the alphabet as such but by byte value: a is code 0x61 and z is code 0x7a, so a sorts before z. The output order is therefore a, z, aa, not z, a, aa as you might guess from reading the keys left to right. For lowercase ASCII letters the byte order happens to match the alphabet, but the moment digits, uppercase, or non-ASCII characters appear, that intuition breaks and you must reason from the actual character codes.

This property is not obvious but very handy: it stabilizes diffs. Two logically equal documents produce byte-for-byte identical pretty output no matter how differently they were written, which helps when comparing expected versus actual JSON in tests through a plain text diff. If you actually need the original key order, JSONB will never give it back; that is what the json type (without the b) is for, since it stores the raw text but pays a re-parse cost on every access.

When not to use it

The classic mistake is dragging jsonb_pretty into a production data path.

• Storage. Never write the result of jsonb_pretty back into a column. JSONB is already binary; indentation is text, extra bytes, and a lost type. Store compact jsonb.
• Transport. For APIs and queues, emit row_to_json or a compact ::text. Indentation bloats payloads and the client does not need it.
• Performance. It formats per row. Do not wrap heavy scans over millions of rows in it, only targeted debugging with LIMIT.

Gotcha: the result is text, not jsonb. If you apply jsonb_pretty and then try to filter with ->> or @>, the expression fails with a type error. Filter on jsonb first and format at the very end, for display only.

Differences in other databases

• MySQL. No direct equivalent name, but JSON_PRETTY(doc) is the built-in that does the same job for the JSON type.
• ClickHouse. Column-oriented analytics with no per-expression pretty function for JSON; for readable output you choose a client output format (for example FORMAT PrettyJSONEachRow) rather than a function in the projection.

Bottom line: jsonb_pretty is a developer convenience. Reach for it in a SELECT during manual psql debugging and in reports, but keep the data in your columns and channels compact.