Working with nested JSON: exploring it, mocking it, and querying it in Postgres

Question

Flat JSON is easy. Nested JSON is where the day goes: an API response with orders inside customers inside accounts, a jsonb column three levels deep, a config file that grew a tree. Three tasks come up over and over, and each one has a trick that makes it painless. Here they are.

1. Reading a huge nested payload

Pretty-printing a 4,000-line response does not actually help. The indentation is correct, but finding one thing still means scrolling through thousands of lines. The fix is to stop treating JSON as text to scroll and start treating it as data to query.

Say the question is: does any item in this order have a null price?

{
  "account": {
    "id": 91,
    "customer": {
      "orders": [
        { "id": 4821, "items": [ { "sku": "A-19", "price": 12.5 } ] },
        { "id": 4822, "items": [ { "sku": "B-04", "price": null } ] }
      ]
    }
  }
}

Three moves turn this from a scroll into a lookup:

• Filter by path, key, type, or value. Filtering to price pulls every price field out of the whole tree at once. Filtering by the value null jumps straight to the offending row. The needle in 4,000 lines becomes a single filtered result.
• Select the nesting level. Instead of expanding everything, expand the tree to level 2 to see the shape of the data, then drill only into the branch that matters. This is the difference between reading structure and drowning in leaves.
• Switch to a table view when a branch is really a list of records. Two hundred objects with the same keys is a spreadsheet pretending to be JSON, and reading it as rows and columns is far faster than reading it as nested braces.

Once JSON is filterable and foldable, payload size stops mattering.

2. Generating realistic nested JSON, fast

The opposite problem: there is no payload yet, and building a parser, a UI, or a test needs one. Hand-writing mock data is slow, and worse, it is too clean. Every record ends up with the same id, the same date, the same shape, which is exactly the uniformity that hides off-by-one bugs, dedupe bugs, and sort bugs.

Two shortcuts cover most cases:

• Load a realistic nested sample. Start from a ready-made nested document (an order, a log line, an analytics event) and adapt it, instead of inventing structure from scratch.
• Multiply one object into many varied records. Write a single object by hand, pick a count, and get back an array where fields like id, email, and dates actually vary per record. That variation is the point: a list of 50 rows that are genuinely different is what surfaces the bug that 50 identical rows would hide.

Realistic nested test data in seconds beats a hand-typed array of clones every time.

3. Querying and updating nested JSON in Postgres

Eventually that nested JSON lands in a jsonb column, and a viewer cannot help anymore. This is where people get stuck, so here is the practical core.

Assume a table orders with a jsonb column data holding the payload above.

Selecting. The two operators to internalize are -> and ->>. -> returns JSON, ->> returns text:

-- returns jsonb
SELECT data -> 'account' -> 'customer' FROM orders;

-- returns text (note the final ->>)
SELECT data -> 'account' ->> 'id' AS account_id FROM orders;

For a deep path, #> and #>> take an array of keys and are much cleaner than chaining arrows:

-- text value deep in the structure
SELECT data #>> '{account,customer,orders,0,id}' AS first_order_id
FROM orders;

To filter rows by a nested value, containment (@>) is both readable and indexable with a GIN index:

SELECT * FROM orders
WHERE data @> '{"account": {"id": 91}}';

And to work across an array, expand it into rows with jsonb_array_elements:

SELECT elem ->> 'id' AS order_id
FROM orders,
     jsonb_array_elements(data #> '{account,customer,orders}') AS elem;

Updating. The workhorse is jsonb_set(target, path, new_value). The gotcha that trips everyone: new_value must itself be jsonb, so cast it.

-- set a deep scalar (note the ::jsonb cast)
UPDATE orders
SET data = jsonb_set(data, '{account,customer,orders,0,id}', '9001'::jsonb)
WHERE id = 1;

Add or replace a key at a level with the || merge operator, and remove a path with #-:

-- shallow-merge a new key into the top level
UPDATE orders SET data = data || '{"archived": true}'::jsonb WHERE id = 1;

-- delete a nested key
UPDATE orders SET data = data #- '{account,customer,orders,0,items}' WHERE id = 1;

One honest warning: || merges only one level deep, so it will not deep-merge nested objects. For nested changes, reach for jsonb_set. And the genuinely hard case, updating a specific array element chosen by a condition rather than a fixed index, needs more than jsonb_set alone. That is a whole topic on its own, and it is the one worth reading a dedicated write-up on before you fight it in production.

Here is the honest reason all of this matters: nobody keeps these operators in their head. It is -> versus ->>, the #>> path syntax, the ::jsonb cast that jsonb_set quietly requires, the fact that || only merges one level. I forget them between projects every single time. So I wrote them down as a permanent, on-page reference next to the formatter: Querying nested JSON in PostgreSQL (SELECT and UPDATE) collects the common reads and writes in one place, for the moment memory does not serve. It is reference for the everyday cases, not a full Postgres manual.

Where the tool fits

I built all three of these into one place, at catssaymeow.org/json-formatter, because I got tired of one tab that only pretty-prints and another that only explores.

It formats, validates, and minifies (invalid JSON is pinpointed by line and column, not a bare "invalid"), and then lets you actually work with the structure: explore it as a tree, graph, or table, filter by path, key, type, or value, and expand to a chosen nesting level. When there is no data to work with yet, it can load a realistic nested sample or multiply one object into an array of varied mock records. It runs as client-side JavaScript, so the JSON stays in the browser, and clean JSON can be sent straight to a built-in API Client as a request body.

And for the part that happens after the database call, the same page carries a Postgres jsonb querying reference for the SELECT and UPDATE syntax that never sticks. Three recurring nested-JSON tasks, one page to come back to.

If you put it up against your own worst nested payload, I would like to hear what it struggles with.