JSONB querying and GIN indexing — how do you query and index schemaless data efficiently?

Q: JSONB querying and GIN indexing — how do you query and index schemaless data efficiently?

**Target Audience:** Developers who store JSON in PostgreSQL and want to query it correctly and fast, and candidates who need to explain GIN, operator classes and when an index does not apply. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 for all `jsonb`, GIN and plan output, run in scratch databases for this page. The runnable JSON examples use SQLite 3.45.0 (sql.js 1.10.2, the page engine), whose JSON functions and `->` / `->>` operators are close cousins of PostgreSQL's. > **How to read this doc:** Each idea is first explained in plain language, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and plan shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A post office keeps every parcel in one huge room, each with a label stuck on in any format the sender liked: some say "fragile", some list a phone number, some have a c

The problem

Question presented to candidate: "You have an events table with a JSONB payload column. Show me how you would pull fields out of it, filter on it, and index it so those filters stay fast. Which queries can a GIN index help with, and which ones can it not?"

What a strong answer should cover:

The extraction operators: -> returns jsonb, ->> returns text, #> / #>> follow a path; casting extracted text before comparing numbers
The search operators: containment @>, key existence ?, ?|, ?&, and SQL/JSON path @? / @@ (PostgreSQL 12+)
GIN is an inverted index from keys and values to rows; it serves @>, ? and jsonpath operators, but not ->> 'k' = 'v', ranges or sorting, which need a B-tree expression index
The two operator classes: default jsonb_ops (supports ?, ?|, ?&, @>, @?, @@) and jsonb_path_ops (only @>, @?, @@, smaller and more selective)
How to prove it: EXPLAIN showing a Bitmap Index Scan, and the pitfalls: JSON null vs missing key, text comparison of numbers, GIN write cost

Code / implementation expected: Yes — extraction queries, a containment filter, a GIN index with an EXPLAIN plan, and an expression index for an equality filter.

Schema used by the examples:

jsonb
gin-index
postgres

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who store JSON in PostgreSQL and want to query it correctly and fast, and candidates who need to explain GIN, operator classes and when an index does not apply. Difficulty: Medium Engines: PostgreSQL 16.15 for all jsonb, GIN and plan output, run in scratch databases for this page. The runnable JSON examples use SQLite 3.45.0 (sql.js 1.10.2, the page engine), whose JSON functions and -> / ->> operators are close cousins of PostgreSQL's.

How to read this doc: Each idea is first explained in plain language, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and plan shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A post office keeps every parcel in one huge room, each with a label

The solution is hidden so you can try first

16 min read · 9 sections

Run the code

Edit the query and run it against the tables for this question.

SQLPull fields out of JSON: -> keeps JSON, ->> returns a plain SQL value, paths reach nested keys

SQLUnnest a JSON array into rows with json_each, then filter and sort like a table

PostgreSQL: containment, key existence, jsonpath and path extraction on jsonb

SELECT id,
       p @> '{"type": "purchase"}' AS is_purchase,
       p ? 'coupon' AS has_coupon_key,
       p ->> 'coupon' IS NULL AS coupon_is_sql_null,
       p ?| ARRAY['page', 'referrer'] AS has_page_or_referrer,
       p @? '$.amount ? (@ > 40)' AS amount_over_40,
       p #>> '{device,os}' AS os
FROM (SELECT id, payload::JSONB AS p FROM events) AS e
ORDER BY id;

PostgreSQL: a GIN index (jsonb_path_ops) serves @> containment on 20000 rows

-- 20000 generated events; 1 in 100 is a signup
CREATE TABLE big_events AS
SELECT g AS id,
       jsonb_build_object(
         'type', CASE WHEN g % 100 = 0 THEN 'signup'
                      WHEN g % 3 = 0 THEN 'purchase' ELSE 'click' END,
         'user', g % 500
       ) AS payload
FROM generate_series(1, 20000) AS g;
CREATE INDEX big_events_gin ON big_events USING GIN (payload jsonb_path_ops);
ANALYZE big_events;

EXPLAIN (COSTS OFF)
SELECT id FROM big_events WHERE payload @> '{"type": "signup"}';

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.