jsonb stores JSON in a decomposed binary form, which supports indexing and containment queries. It is the right choice for genuinely flexible or sparse data, but a query that extracts a field without a matching index becomes a full scan, so the access pattern drives the schema.
Before you start
You should be comfortable with WHERE, indexes and expressions. This article uses PostgreSQL’s jsonb operators and a GIN index.
Step-by-step walkthrough
Step 1: Prefer jsonb over json
json stores the exact text and reparses it on every access; jsonb parses once, allows indexing and supports containment. Unless you must preserve key order and whitespace, use jsonb. Note that jsonb removes duplicate keys and normalizes formatting.
Step 2: Extract with the right operator
payload -> 'status' returns a jsonb value, and payload ->> 'status' returns text. Compare with ->> when you want a string, and use -> when you continue navigating the JSON structure. Mixing them up causes type errors or unexpected jsonb comparisons.
Step 3: Index the queries you run
A GIN index on the column supports containment (@>) and existence checks. For a query that extracts one field and filters it, an expression index on (payload ->> 'status') or a B-tree on the extracted text is more targeted. Add the index that matches the WHERE clause.
Worked scenario
The containment query filters events by type using a GIN index.
SELECT payload ->> 'status' AS status
FROM events
WHERE payload @> '{"type": "signup"}';Walk through the example
payload @> '{"type": "signup"}' is a containment test that a GIN index can serve, so the planner avoids scanning every row. The projection extracts status as text with ->>. If instead you filtered payload ->> 'type' = 'signup', a containment index would not help unless you add an expression index for that form.
Common mistake
Storing relational data as JSON and losing constraints, or querying extracted fields without an index, which turns a fast lookup into a full scan. Another is using json and wondering why containment and indexing do not work.
Verify the behavior
Run EXPLAIN on a containment query and confirm the GIN index is used. Compare the same query written with ->> and check whether an index applies. Insert a document with a duplicate key and observe how jsonb normalizes it.
Interview exercise
When should data live in a jsonb column rather than in normal columns?
Answer and reasoning
Use jsonb when the shape is genuinely variable or sparse, such as provider-specific webhook payloads or user-defined metadata, where normalizing every field would create wide, mostly empty tables. Keep anything you filter, join or constrain frequently in real columns, so you get types, indexes and constraints. The rule is to model the access pattern, not to avoid migrations.
Continue learning
Compare schema choices in MongoDB schema validation and SQL constraints as invariants. Read the PostgreSQL JSON types documentation and try the SQL interview questions.