PostgreSQL JSONB Query Builder

Querying a jsonb column means remembering which arrow returns json and which returns text, how to reach into nested objects and arrays, and — the part that usually bites — which index actually helps. Enter your table, jsonb column and a dotted path like user.address.city, choose an operator, and this builder generates the SELECT with correctly quoted keys, the matching GIN or B-tree expression index for that specific operator, and a cookbook of the JSONB operations you reach for next: expanding arrays with jsonb_array_elements, updating single keys with jsonb_set, and SQL/JSON path queries. All generated locally in your browser.

TableJSONB columnPath (dot / [index] notation)

OperatorValueExtra SELECT columns

Generated query

SELECT id, created_at,
       payload->'user'->'address'->>'city' AS city
FROM   events
WHERE  payload->'user'->'address'->>'city' = 'Berlin'
ORDER  BY 1
LIMIT  100;

Indexes for this query

-- Which index you need depends on the operator you are using.

-- ℹ️  GIN indexes do NOT speed up ->> equality — see the expression index below
-- jsonb_ops (default): indexes every key AND every value, larger but supports ? operators
CREATE INDEX idx_events_user_address_city_gin ON events USING gin (payload);

-- jsonb_path_ops: ~30-50% smaller and faster, supports @> only (no ? operators)
CREATE INDEX idx_events_user_address_city_path ON events USING gin (payload jsonb_path_ops);

-- ✅ Matches this query: a B-tree expression index on the extracted value
-- The expression must match the WHERE clause character for character to be used
CREATE INDEX idx_events_user_address_city_expr ON events ((payload->'user'->'address'->>'city'));

-- For numeric comparisons, index the cast — not the raw text
CREATE INDEX idx_events_user_address_city_num ON events (((payload->'user'->'address'->>'city')::numeric));

-- Confirm the planner actually picks the index
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE payload->'user'->'address'->>'city' = 'Berlin';

JSONB cookbook

-- Expand a JSON array into rows
SELECT e.id, item->>'sku' AS sku, (item->>'qty')::int AS qty
FROM   events e
CROSS  JOIN LATERAL jsonb_array_elements(e.payload->'items') AS item;

-- Every key/value pair of an object as rows
SELECT e.id, kv.key, kv.value
FROM   events e, jsonb_each(e.payload) AS kv;

-- Aggregate rows back into a JSON array
SELECT jsonb_agg(jsonb_build_object('id', id, 'data', payload)) FROM events;

-- Update one key without rewriting the document (creates the key if missing)
UPDATE events SET payload = jsonb_set(payload, '{status}', '"active"', true) WHERE id = 1;

-- Delete a key / a nested path
UPDATE events SET payload = payload - 'temp' WHERE id = 1;
UPDATE events SET payload = payload #- '{user,address,zip}' WHERE id = 1;

-- Merge two objects (right side wins on conflicts)
UPDATE events SET payload = payload || '{"source":"api","v":2}'::jsonb WHERE id = 1;

-- SQL/JSON path (Postgres 12+): filter inside the document itself
SELECT * FROM events WHERE payload @@ '$.items[*].qty > 10';
SELECT jsonb_path_query(payload, '$.items[*] ? (@.qty > 10)') FROM events;

-- Pretty-print for debugging
SELECT jsonb_pretty(payload) FROM events LIMIT 1;

Notes

• -> returns jsonb, ->> returns text. Chain -> for intermediate levels and finish with ->> only when you want a text value you can compare or cast.
• #> and #>> take a whole path as an array: payload #>> '{user,address,city}' is the same as payload->'user'->'address'->>'city'.
• A GIN index on a jsonb column accelerates @>, ?, ?| and ?& — it does NOT accelerate ->> = 'x'. For that, create a B-tree expression index whose expression matches your WHERE clause exactly.
• jsonb_path_ops indexes are noticeably smaller and faster than the default jsonb_ops, but only support the @> containment operator.
• Numbers extracted with ->> come back as text, so '9' > '10' is true. Always cast with ::numeric or ::int before comparing.
• The ? operator conflicts with the placeholder syntax in many drivers (JDBC, node-postgres). Use jsonb_exists(col, 'key') instead, or escape it as ?? where your driver supports it.
• Use jsonb, not json: json stores the raw text and reparses it on every access, and cannot be indexed with GIN.

Do more than postgresql jsonb query builder — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the table and jsonb column, then a path such as user.address.city or items[0].sku — array indexes and nested keys are both supported.
Pick the operator you need (text equality, numeric comparison, JSON containment, key existence) and type the value to match.
Copy the generated SELECT, then create the index shown below it — the tool picks GIN or a B-tree expression index based on the operator you chose.

Frequently asked questions

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

-> returns a jsonb value, so you can keep chaining into it: payload->'user'->'address' is still jsonb. ->> returns text, which ends the chain but gives you a value you can compare with =, pass to ILIKE, or cast with ::numeric. The rule of thumb is to use -> for every level except the last, and ->> on the final key. If you compare with ->, remember the right side has to be jsonb too — payload->'age' = '30'::jsonb, not = 30.

How do I index a JSONB column in PostgreSQL?

It depends on the operator. For containment and key-existence queries (@>, ?, ?|, ?&) create a GIN index: CREATE INDEX ... USING gin (payload), or USING gin (payload jsonb_path_ops) for a smaller, faster index that supports @> only. A GIN index does not help a query like payload->>'city' = 'Berlin' — for that you need a B-tree expression index: CREATE INDEX ... ON events ((payload->>'city')). The expression in the index must match the one in your WHERE clause exactly, or the planner will ignore it.

Should I use json or jsonb in PostgreSQL?

Use jsonb in almost every case. json stores an exact copy of the input text and reparses it on every single access, preserves key order and duplicate keys, and cannot be indexed with GIN. jsonb stores a decomposed binary form: slightly slower to insert, much faster to query, deduplicates keys, and supports the full operator and indexing surface. Only pick json when you must round-trip the original document byte for byte. To explore jsonb documents and test these queries against a live database with AI-assisted SQL, try Chat2DB: download at https://chat2db.ai/download or use https://app.chat2db.ai.