JSON Query Patterns in Postgres With JSONB

Postgres gives you two ways to store JSON: json and jsonb. Pick jsonb almost every time you plan to query the data. That's the whole thesis. The rest of this piece is about what "query the data" actually looks like once you've made that choice.

Why jsonb wins before you even get to querying

json stores your data as text, exactly as it came in. Every time you read it, Postgres reparses that text from scratch. jsonb does the opposite: it parses once, on write, and stores a decomposed binary form. That single design decision is the reason almost everyone reaches for jsonb.

The binary format isn't free, though. It costs you a few things:

Whitespace is gone. Pretty-printed input comes back compact. Duplicate keys collapse. If a document has the same key twice, only the last value survives. Key order isn't guaranteed. Postgres can reorder keys internally.

For nearly every production use case, none of that matters. The PostgreSQL 18 documentation, as cited by DbSchema, states that jsonb is "significantly faster to process, since no reparsing is needed." That's the trade you're making: a little fidelity for a lot of speed.

So when does json actually win? Narrow cases. Signature verification, where you need the exact byte sequence a client sent. Logging raw API payloads character-for-character, where "close enough" isn't good enough. Outside of those, the case for json is thin.

| | json | jsonb | |---|---|---| | Storage format | Text as written | Decomposed binary | | Keeps whitespace and key order | Yes | No | | Keeps duplicate keys | Yes | Last value only | | Containment (@>) | Not supported | Supported | | GIN index on the column | Not supported | Supported | | Parsing cost | On every read | Once, on write |

jsonb isn't a replacement for a normal relational schema. If every row in a table has the same fields, and those fields appear in WHERE clauses constantly, jsonb just adds a cast to every query and buys you nothing. Normalize that data. jsonb earns its keep when the shape of the data actually varies, row to row: product attributes that differ by category, event payloads whose fields depend on the event type, settings that grow a new key every quarter. Variable shape is the whole argument for jsonb. Without it, you're just making your own life harder.

The core operators and what each one returns

Assume a simple events table for all the examples below: id, event_type, data JSONB, created_at TIMESTAMP DEFAULT NOW(). That's enough to show every operator without inventing new schemas as you go.

Extraction operators pull values out of a jsonb column, but they don't all return the same type, and that difference changes which operations and comparisons will work on the result.

-> returns a jsonb value. Safe to chain into more JSON operations.
->> returns plain text. This ends the chain. Want to compare it as a number? You need an explicit cast.
#> follows a multi-level path, like '{specs,cpu}', and returns jsonb.
#>> does the same path traversal but returns text, replacing what would otherwise be a long chain of arrows.

Run both extraction forms on the same field and the difference becomes obvious: data -> 'user_id' returns 123 as jsonb, complete with type metadata. data ->> 'user_id' returns '123' as plain text. Look almost the same. Behave completely differently.

Why does this matter? Because ->> 'price' = '100' is a string comparison. (data ->> 'price')::NUMERIC > 100 is arithmetic. If your cast doesn't match the way an index was built, Postgres will quietly ignore that index and scan the whole table instead. No error, no warning. Just a slow query and a confused developer.

Containment and existence operators ask a different kind of question: does this document have something, rather than equal something.

@> asks "does the left side contain the right side?" This is the operator a GIN index is built to accelerate, and it works on both objects and arrays.
<@ is the reverse: "is the left side contained by the right?" Less common, but handy when testing a literal value against a column.
? checks whether a key or array element exists. Supported by the default GIN operator class, but not by jsonb_path_ops (more on that trade-off later).
?| checks whether any of a list of keys exists.
?& checks whether all of a list of keys exist.

There are also modification operators that aren't the focus here: || concatenates arrays or merges objects, and - removes a key, a matching array element, or an index.

Chaining arrows is the most natural way to walk into nested JSON. data -> 'specs' ->> 'cpu' reads left to right: each -> step narrows down into the structure, and the final ->> pulls out the text you actually want.

That works fine for two or three levels. Past that, it gets hard to read and easy to mess up. At four or more levels deep, a path array is the better tool: metadata #>> '{specs,storage,type}' says the same thing as a long chain of arrows, but it's one expression instead of five.

Arrays work the same way, indexed by position. data -> 'items' -> 0 grabs the first element. data -> 'items' -> -1 grabs the last one, since negative indexes count backward from the end.

Take a products table with a metadata column. These two expressions return the same value:

metadata -> 'inventory' -> 0 -> 'quantity'
metadata #>> '{inventory,0,quantity}'

Same result, two different levels of readability. As a rule: keep intermediate steps in jsonb (using ->) so you can keep navigating, and only switch to ->> or an explicit cast at the very end, once you're done drilling down.

A mistake that's easy to make: comparing a ->> result to a number without casting it. WHERE data ->> 'age' > 30 WHERE data ->> 'age' > 30 just runs a string comparison and gives you the wrong rows back, silently. It just runs a string comparison and gives you the wrong rows back, silently. Postgres won't stop you. You have to catch it yourself.

The fix is a cast, applied consistently. Following an example from a Germany/France population comparison: WHERE (cities -> 0 ->> 'population')::INT > 3000000. The cast is required because ->> always returns text, no matter what the underlying value looks like.

Containment queries with @> versus equality filters

data @> '{"user_id": 123}' asks a structural question: does this document contain this sub-object? That's a broader question than equality. It works for nested objects and for checking array membership, not just matching a single scalar value.

A GIN index on a jsonb column supports @> directly, which is what actually matters for performance. A filter written as WHERE data ->> 'user_id' = '123', on the same column, with the same index in place, still falls back to a sequential scan (unless you've built a separate expression index for it). Same data, same intent, very different query plan. Whether the index gets used depends on the operator you choose.

This isn't limited to flat key-value checks. Containment works on nested structure too: WHERE payload @> '{"type":"page_view","client":{"platform":"mobile"}}' checks the shape and presence of nested fields, not a scalar match. Because the GIN index is checking structural fingerprints, this kind of filter doesn't get slower as documents get bigger or more nested.

Array containment follows the same logic: WHERE data @> '{"items": [{"sku": "ABC"}]}' checks whether the items array contains an element matching that shape. Useful, but there's a catch: the literal you're checking against has to be shaped exactly like the array element you're trying to match.

For simpler membership checks, like "does this tags array include 'urgent'," the existence operators (?, ?|, ?&) are the right tool. They're built for scalar arrays: tags, roles, capabilities, anything you're just checking for presence in a list. Note the same caveat as before: these work with the default GIN operator class but are not available when using jsonb_path_ops.

If EXPLAIN (ANALYZE, BUFFERS) shows a sequential scan on a table that clearly has a GIN index, check the operator in your WHERE clause first. It's very often ->> where @> should be. Rewriting the filter to a containment form, or adding a targeted expression index, usually fixes it.

Array unnesting with jsonb_array_elements versus containment

Containment is great until the condition you need isn't "does this shape exist" but "does any item in this array meet some condition." @> can't express "any item where price is over 50" or "any review with a rating below 3." It only checks for an exact shape match. Once your filter needs a comparison, not a match, containment is the wrong tool.

That's where unnesting comes in. The pattern looks like this:

FROM events e, jsonb_array_elements(e.data -> 'items') AS item
WHERE item ->> 'sku' = 'ABC'

This is a lateral cross-join: each element of the array becomes its own row. That's powerful, but it also means your result set can balloon fast if an array has a lot of elements. If what you actually want back is the parent row, not the individual elements, use DISTINCT e.* or aggregate the results back together. Otherwise you'll get one row per matching array element instead of one row per event.

A couple of utility functions round this out:

jsonb_array_length(data -> 'items') counts elements without unnesting anything. Cheap and simple when you just need a count.
jsonb_array_elements_text() unnests a scalar array, strings or numbers, directly into text rows, skipping the jsonb-to-text conversion step.

On the way back out, jsonb_agg() (and jsonb_agg() FILTER (WHERE ...)) let you reassemble documents or pivot conditionally, right inside the database. That keeps large result sets from getting shipped to the application layer just to be reshaped there.

So which approach do you actually reach for? Three options, three different trade-offs:

Containment (@>): fastest, index-friendly, but only works for exact shape matches.
Unnest and filter: flexible, handles any per-element condition, but multiplies rows and can get expensive on large arrays.
Expression index on position 0 (e.g., (data -> 'items' -> 0 ->> 'sku')), a middle ground, useful when the first element is the one that matters most in practice.

Pick based on the shape of the question you're asking, not habit.

SQL/JSON path queries with jsonb_path_exists and jsonb_path_query

Postgres 12 introduced the SQL/JSON path language, and it's the right tool once your filter conditions get complicated. The syntax borrows familiar symbols: $ for the root, .key for a field, [n] for an array index, [*] for every element, and ?() for a filter expression.

Four functions cover most of what you'll need:

jsonb_path_exists(col, path) returns true or false. Use it in a WHERE clause for existence or conditional checks.
jsonb_path_query(col, path) returns every match as its own row.
jsonb_path_query_array(col, path) bundles all matches into a single jsonb array.
jsonb_path_query_first(col, path) returns just the first match, useful when one result is all you need.

Inside the ?() filter, @ refers to the current element being evaluated. You get comparison operators (==, !=, <, >, <=, >=), logical operators (&&, ||, !), and string matching (like_regex, starts with), plus existence checks.

This is where this earns its place over containment. Say you want every inventory location where quantity is over 100:

jsonb_path_query(metadata, '$.inventory[*] ? (@.quantity > 100)')

There's no clean way to write that with @>, because the threshold is a range, not a fixed value you can match against. Containment answers "is this exact thing present." Path queries answer "which things satisfy this condition." Different questions.

You can combine conditions in one filter, too: $.inventory[*] ? (@.warehouse == "NYC" && @.quantity > 40) checks a field match and a numeric range in a single expression. Or match on a string pattern: $.tags[*] ? (@ starts with "port"), straight from the PostgreSQL documentation, with no real containment equivalent.

So when should you reach for a path query instead of unnesting? When the condition itself is the complicated part, multiple fields, a range, a regex, and you'd rather avoid the overhead of a lateral join. Also when you want the matching sub-documents returned directly, not the whole parent row.

One caveat: a jsonb_path_ops GIN index supports the @? and @@ operators that back these path queries, but not every path expression will actually use that index. Check with EXPLAIN (ANALYZE, BUFFERS) before assuming it's fast.

Indexing strategy: matching the right index type to the query shape

Every operator covered so far is only as fast as the index behind it, and Postgres gives you three real options for jsonb.

GIN with the default operator class supports the widest range of operators: @>, <@, ?, ?|, ?&, @?, and @@. This is the general-purpose choice, the one to reach for if you're not sure yet which query patterns will dominate.

GIN with jsonb_path_ops is narrower by design. It supports @>, @?, and @@, and nothing else, notably not the ?, ?|, or ?& existence operators. In exchange for that narrower support, it's typically more compact and faster for workloads that are heavy on containment queries. If most of your traffic is @> filters and you don't need key-existence checks, this is usually the better pick.

The choice between the two comes down to one question: do you need ?, ?|, or ?& anywhere in your query patterns? If yes, default GIN. If no, and containment is your main workload, jsonb_path_ops is worth the trade.

Whichever index type you land on, the same rule from earlier applies at every level: check EXPLAIN (ANALYZE, BUFFERS) before trusting that an index is doing what you think it's doing. An index that exists isn't the same as an index that gets used. Postgres will use it when the operator and the cast in your query match what the index was built for, and quietly skip it otherwise. The right operator, matched to the right index, is what turns a jsonb column from a flexible dumping ground into something that actually queries fast, as every section here shows.