scribase
Guide

How do you query a JSONB column in Postgres?

JSONB stores JSON in a decomposed binary form that Postgres can index and query directly, unlike the json type, which stores an exact text copy and reparses it on every access. Querying JSONB mainly comes down to two operator families: -> and ->> to pull a value out by key or index, and @> to test whether one JSON document contains another — which is the operator a GIN index speeds up.

Published November 16, 20267 min read

1.Extract a value: -> and ->>

-> returns the value at a key or array index as jsonb (so it can be chained or compared with other jsonb). ->> returns the same value as text, which is what you want in a WHERE clause comparing against a string or number, or in the final SELECT list for a plain scalar.

sql
select data -> 'address' -> 'city' as city_jsonb, -- jsonb
       data ->> 'name' as name_text          -- text
from customers
where data ->> 'plan' = 'pro';

-- array index (0-based)
select items -> 0 ->> 'sku' from orders;

2.Path extraction: #> and #>>

For a nested value several levels deep, #> and #>> take a text array path instead of chaining -> repeatedly. #>> returns text, same as ->>.

sql
select data #>> '{address,city}' as city
from customers
where data #>> '{address,country}' = 'US';

3.Containment: @> and the GIN index

The @> operator tests whether the left JSONB value contains the right one — the standard way to ask "does this document have these key/value pairs, anywhere, possibly nested". It also supports a direct equality lookup on a top-level key. A GIN index on the column (or a jsonb_path_ops GIN index, smaller and faster for pure containment) makes @> queries use an index scan instead of scanning every row and reparsing its JSON.

sql
-- find rows where data contains {"plan": "pro", "active": true}
select * from customers where data @> '{"plan": "pro", "active": true}';

-- index that speeds up @> queries on this column
create index on customers using gin (data jsonb_path_ops);

-- confirm the index is used
explain analyze select * from customers where data @> '{"plan": "pro"}';

4.Expand a JSON array into rows

jsonb_array_elements turns a JSON array value into one row per element (set-returning, used in FROM or a lateral join), which is the usual way to filter or aggregate over items inside a JSONB array column.

sql
select o.id, item ->> 'sku' as sku, (item ->> 'qty')::int as qty
from orders o,
     jsonb_array_elements(o.items) as item
where item ->> 'sku' = 'SKU-1';
FAQ

Common questions

What is the difference between -> and ->> in Postgres?

-> returns the extracted value as jsonb, so it can be chained into another -> or compared to other jsonb. ->> returns the same value cast to text, which is what a WHERE clause or a final scalar column usually needs.

What is the difference between json and jsonb?

json stores the exact input text and reparses it on every read; jsonb stores a decomposed binary form, is faster to query, does not preserve whitespace or key order, and is the type that can be indexed with GIN. Use jsonb unless you specifically need to preserve the original text.

How do I index a JSONB column for fast queries?

Add a GIN index: create index on table using gin (column) for general containment and key-existence queries, or using gin (column jsonb_path_ops) for a smaller, faster index when you only need the @> containment operator.

How do I query a specific key inside a JSONB column efficiently?

For one specific key accessed often, a B-tree expression index on that extracted value — create index on table ((data ->> 'plan')) — is usually faster than a full GIN index and works with the ->> operator directly in WHERE.

Ready to try it?

Start a free project and follow this guide against a real backend, or move an existing project in with a dry run first.