Querying JSONB in Postgres: Operators, Paths and Indexes

To query JSONB in Postgres, use -> to get a value as jsonb, ->> to get it as text, @> to test whether a document contains a sub-document, and ? to test whether a key exists. Index the column with a GIN index for @>, ? and jsonpath queries, or with a B-tree expression index on (col ->> 'key') when you filter or sort on one specific key. Postgres 17 adds the SQL-standard JSON_TABLE, JSON_VALUE, JSON_QUERY and JSON_EXISTS functions on top.

This is a task-by-task reference. Every query below runs on Postgres 17 against one sample table, so you can paste it into a SQL editor and follow along.

#The sample table

create table public.orders (
  id bigint generated always as identity primary key,
  created_at timestamptz not null default now(),
  data jsonb not null
);

insert into public.orders (data) values
('{"customer": {"email": "[email protected]", "country": "DE"},
  "status": "paid", "total": 129.90,
  "tags": ["gift", "express"],
  "items": [{"sku": "A-1", "qty": 2, "price": 40.00},
            {"sku": "B-7", "qty": 1, "price": 49.90}]}'),
('{"customer": {"email": "[email protected]", "country": "FI"},
  "status": "pending", "total": 15.00,
  "tags": [],
  "items": [{"sku": "C-3", "qty": 3, "price": 5.00}]}');

#Operator cheat sheet

Two rules save a lot of confusion. First, -> keeps JSON, ->> gives text, so compare ->> results with strings and cast them for numbers. Second, extraction operators return NULL when the path does not exist; they do not raise errors.

#Read values

select id,
       data ->> 'status'                     as status,
       (data ->> 'total')::numeric           as total,
       data #>> '{customer,email}'           as email,
       jsonb_array_length(data -> 'items')   as item_count
from public.orders;

Since Postgres 14 you can also use subscripts, which some people find easier to read. Subscripts return jsonb:

select data['customer']['country'] from public.orders;

#Filter rows

#Match a value

-- text comparison on one key
select id from public.orders where data ->> 'status' = 'paid';

-- containment: same result, and can use a GIN index
select id from public.orders where data @> '{"status": "paid"}';

-- nested containment
select id from public.orders where data @> '{"customer": {"country": "DE"}}';

@> checks that the right side appears inside the left side, at the same nesting. It ignores extra keys and array order.

#Compare numbers and dates

Cast after extracting with ->>:

select id from public.orders where (data ->> 'total')::numeric > 100;

If some rows hold non-numeric text in that key, the cast errors. jsonpath avoids that, because it suppresses type errors:

select id from public.orders where data @@ '$.total > 100';

#Arrays

-- array contains the element "gift"
select id from public.orders where data -> 'tags' ? 'gift';

-- same with containment (indexable on the whole column)
select id from public.orders where data @> '{"tags": ["gift"]}';

-- any item with qty above 2
select id from public.orders where data @? '$.items[*] ? (@.qty > 2)';

-- empty array
select id from public.orders where data -> 'tags' = '[]'::jsonb;

#Key existence

select id from public.orders where data ? 'coupon';
select id from public.orders where not (data ? 'coupon');

? only looks at top-level keys (or string elements of a top-level array). For nested keys, use data -> 'customer' ? 'email' or a jsonpath like data @? '$.customer.email'.

#Turn JSON into rows

#Expand arrays

select o.id, item ->> 'sku' as sku, (item ->> 'qty')::int as qty
from public.orders o
cross join lateral jsonb_array_elements(o.data -> 'items') as item;

#JSON_TABLE (Postgres 17)

JSON_TABLE maps a JSON document to typed columns in one step. It reads well for reports:

select o.id, jt.*
from public.orders o,
     json_table(o.data, '$.items[*]'
       columns (
         line   for ordinality,
         sku    text    path '$.sku',
         qty    int     path '$.qty',
         price  numeric path '$.price'
       )) as jt;

#JSON_VALUE and JSON_EXISTS (Postgres 17)

select id,
       json_value(data, '$.total' returning numeric default 0 on empty) as total
from public.orders
where json_exists(data, '$.items[*] ? (@.sku == "A-1")');

These are standard SQL, so the same query shapes work in other databases that implement SQL/JSON. The native operators remain fine; pick whichever your team reads more easily.

#Aggregate

-- revenue per country
select data #>> '{customer,country}' as country,
       sum((data ->> 'total')::numeric) as revenue
from public.orders
group by 1;

-- build JSON for an API response
select jsonb_build_object(
         'country', data #>> '{customer,country}',
         'orders',  jsonb_agg(id order by id)
       )
from public.orders
group by data #>> '{customer,country}';

#Update JSONB

-- set or replace one nested key
update public.orders
set data = jsonb_set(data, '{status}', '"shipped"')
where id = 1;

-- same with a subscript (Postgres 14+)
update public.orders set data['status'] = '"shipped"' where id = 1;

-- merge several keys at the top level
update public.orders set data = data || '{"carrier": "DHL", "tracking": "123"}' where id = 1;

-- remove a key, or a nested key
update public.orders set data = data - 'tags' where id = 2;
update public.orders set data = data #- '{customer,email}' where id = 2;

-- append to an array
update public.orders
set data = jsonb_set(data, '{tags}', (data -> 'tags') || '"priority"')
where id = 2;

Every update writes a whole new version of the row, even if you changed one key. Very large documents that change often are a reason to move the busy keys into columns.

#Indexes: which query uses which

Without an index, every JSONB filter scans the table. Pick the index from the operators you use.

-- general purpose
create index orders_data_gin on public.orders using gin (data);

-- containment and jsonpath only, smaller and often faster
create index orders_data_path on public.orders using gin (data jsonb_path_ops);

-- equality, ranges and sorting on one key
create index orders_status on public.orders ((data ->> 'status'));
create index orders_total  on public.orders (((data ->> 'total')::numeric));

The expression in the query must match the expression in the index. An index on ((data ->> 'status')) helps where data ->> 'status' = 'paid', but not where data @> '{"status": "paid"}', which needs the GIN index. And jsonb_path_ops creates no entries for keys with empty structures like {"a": {}}, so a search for those falls back to a full index scan.

Always confirm with EXPLAIN:

explain select id from public.orders where data @> '{"status": "paid"}';

On a tiny table the planner may still choose a sequential scan because it is cheaper; test with realistic row counts. The index types guide explains GIN and B-tree in more detail, and finding slow queries shows how to read the plan.

#Promote hot keys to columns

If one key shows up in most queries, a generated column gives you statistics, constraints and a simple index:

alter table public.orders
  add column status text generated always as (data ->> 'status') stored;
create index on public.orders (status);

Adding a stored generated column rewrites the table, so on a large, busy table do it during a quiet period.

#JSONB queries on StitchDB

A StitchDB project is a full Postgres database, so everything above runs as written in the Studio SQL Editor (Ctrl or Cmd + Enter). Some platform details help:

• From the REST API and SDK, JSON columns support the cs, cd and ov filters (Filtering and pagination):

const { data, error } = await stitchdb
  .from('orders')
  .select('id, data')
  .contains('data', { status: 'paid', customer: { country: 'DE' } })

• For anything more complex, put the SQL in a function in public and call it with rpc() (Call functions from the API):

create function public.orders_with_sku(p_sku text)
returns setof public.orders
language sql stable as $$
  select * from public.orders
  where data @> jsonb_build_object('items', jsonb_build_array(jsonb_build_object('sku', p_sku)))
$$;

const { data } = await stitchdb.rpc('orders_with_sku', { p_sku: 'A-1' })

The function runs with the caller's permissions by default, so your Row Level Security policies still decide which orders come back.

• Indexes can be created in Studio under Database, then Indexes (btree, gin, gist, hash or brin), and Database, then Query performance shows whether your JSON queries are the slow ones (Query performance).

#FAQ

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

-> returns the value as jsonb, so you can keep navigating into it. ->> returns it as text, which you compare with strings or cast to another type.

#How do I index a JSONB column in Postgres?

Use create index ... using gin (col) for containment, key existence and jsonpath queries, or gin (col jsonb_path_ops) if you only use @> and jsonpath. For equality or sorting on one key, use a B-tree expression index such as ((col ->> 'status')).

#Why is my JSONB query not using the index?

Usually the query and the index do not match: a B-tree on (col ->> 'key') does not serve @>, and jsonb_path_ops does not serve ?. On small tables the planner may also prefer a sequential scan. Check with EXPLAIN.

#Is JSON_TABLE available in Postgres 17?

Yes. Postgres 17 added JSON_TABLE along with JSON_EXISTS, JSON_QUERY and JSON_VALUE. Older versions can use jsonb_array_elements and jsonb_to_recordset instead.

#Can I query JSONB through the REST API?

Yes. Use the contains, containedBy and overlaps filters for JSON columns, and wrap anything more complex in a SQL function you call with rpc().

Run these queries on your own Postgres database: start a free project, then open the SQL Editor.