What is JSONB?
JSONB is PostgreSQL's binary JSON type. Unlike regular JSON (which stores text), JSONB compresses and parses JSON data into a binary format, making queries faster and storage more efficient.
JSONB is perfect when you need to store semi-structured data — data that doesn't fit neatly into fixed columns but still needs to be queryable. Real-world examples:
- API responses — Webhook payloads, payment processor data, third-party integrations.
- User metadata — Device info, preferences, custom attributes that vary per user.
- Transaction details — Device type, IP, risk flags, optional fields like verification codes.
- Product variants — Color, size, custom specifications that differ per product.
In this lesson, you'll query the NovaPay payment platform database, which stores transaction metadata as JSONB. By the end, you'll extract values, filter by nested fields, expand JSON arrays, and optimize queries with indexes.
Extracting Values: → vs →→
The two most important JSONB operators are → and →→. They both extract values, but return different types:
→ (arrow) — Extracts a value as JSON. Useful when the value is itself an object or array.
→→ (double arrow) — Extracts a value as text (VARCHAR). Use this for final text/number output.
Q1: Retrieve JSONB Objects
Let's start by fetching raw JSONB metadata from the transactions table:
───────┼────────────────────────────────────────
2 | {"ip": "192.168.96.85", "device": "desktop", "risk_score": 51}
3 | {"ip": "192.168.200.181", "device": "pos", "card_last4": "5771", "risk_score": 84}
4 | {"ip": "192.168.129.102", "device": "pos", "risk_score": 3}
The metadata column contains different fields per transaction. Device can be desktop, pos, mobile, or tablet. Some rows have card_last4; others don't.
Q2: Extract as JSON Using →
───────┼────────────┼──────────
2 | null | "desktop"
3 | "5771" | "pos"
4 | null | "pos"
5 | null | "tablet"
6 | "3315" | "desktop"
Notice: The → operator returns JSON values with quotes ("desktop", not desktop). Use → when extracting nested objects or when the value will be fed to another JSONB function.
Q3: Extract as Text Using →→
───────┼────────────┼─────────┼───────────────────
2 | null | desktop | 192.168.96.85
3 | 5771 | pos | 192.168.200.181
4 | null | pos | 192.168.129.102
5 | null | tablet | 192.168.54.93
6 | 3315 | desktop | 192.168.67.234
Now the values are text (no quotes). Use →→ for your final SELECT clause when you need clean text output.
Containment Queries with @>
The @> operator checks if a JSONB value contains a specific key-value pair or subobject. This is powerful for filtering based on nested data.
@> syntax — jsonb_column @> '{"key": value}'. Returns true if the left JSONB contains the right JSONB as a subset.
Q4: Find Transactions by Device Type
───────┼───────────┼────────────────────────────────────────────────
9 | 99279.16 | {"ip": "192.168.12.79", "device": "mobile", "card_last4": "9518", "risk_score": 97}
18 | 6935.05 | {"ip": "192.168.200.161", "device": "mobile", "risk_score": 98}
21 | 77189.11 | {"ip": "192.168.57.147", "device": "mobile", "risk_score": 23}
28 | 1559.27 | {"ip": "192.168.125.187", "device": "mobile", "risk_score": 60}
29 | 932.37 | {"ip": "192.168.129.102", "device": "mobile", "card_last4": "5042", "risk_score": 59}
The @> operator finds all transactions where metadata contains the pair "device": "mobile", regardless of other fields in metadata.
Q5: Filter by Risk Score Containment
───────┼───────────┼─────────────────────────────────────────────────
3 | 25666.55 | {"ip": "192.168.200.181", "device": "pos", "card_last4": "5771", "risk_score": 84}
16 | 8998.43 | {"ip": "192.168.245.189", "device": "mobile", "risk_score": 99}
17 | 42879.45 | {"ip": "192.168.155.125", "device": "pos", "risk_score": 78}
Containment searches for exact matches. For range queries (e.g., risk_score > 60), use casting (see next section).
Casting JSONB Values to Numbers
JSONB values are stored as JSON. To compare numbers or dates, you must cast them explicitly:
Q6: Extract risk_score as Integer and Filter
───────┼───────────┼────────────
288 | 35655.20 | 100
215 | 2648.53 | 100
333 | 954.77 | 100
150 | 15791.48 | 100
16 | 8998.43 | 99
172 | 36943.48 | 99
325 | 26809.36 | 99
95 | 40883.77 | 99
156 | 87273.66 | 99
137 | 28024.93 | 99
Key pattern: (metadata->>'risk_score')::INTEGER. Use →→ to extract as text, then cast with ::INTEGER for numeric comparisons.
Expanding JSONB with jsonb_each and jsonb_array_elements
When you have multiple key-value pairs or nested arrays, you can expand JSONB into separate rows. Two key functions:
jsonb_each(jsonb) — Expands each key-value pair into a separate row. Returns (key, value) columns.
jsonb_array_elements(jsonb) — Expands each array element into a separate row.
Both functions require LATERAL to reference columns from the outer query.
Q7: Expand Metadata with jsonb_each
───────┼────────────┼──────────────────────
1 | ip | "192.168.57.87"
1 | device | "pos"
1 | risk_score | 49
2 | ip | "192.168.96.85"
2 | device | "desktop"
2 | risk_score | 51
3 | ip | "192.168.200.181"
3 | device | "pos"
3 | card_last4 | "5771"
3 | risk_score | 84
Each metadata object expands from 1 row to 3-4 rows (one per key-value pair). Useful for pivoting JSONB data or performing aggregations per key.
Q8: Expand Nested Arrays with jsonb_array_elements
────────────┼────────────────┼──────────────────────────────────
3 | payment.success | {"qty": 5, "name": "Item 13", "price": 472.42}
3 | payment.success | {"qty": 1, "name": "Item 111", "price": 77.03}
6 | refund.created | {"qty": 2, "name": "Item 66", "price": 289.83}
6 | refund.created | {"qty": 3, "name": "Item 157", "price": 77.89}
9 | payment.success | {"qty": 5, "name": "Item 72", "price": 83.71}
9 | payment.success | {"qty": 2, "name": "Item 193", "price": 117.83}
11 | payment.success | {"qty": 2, "name": "Item 83", "price": 96.74}
11 | payment.success | {"qty": 3, "name": "Item 137", "price": 179.28}
14 | refund.created | {"qty": 1, "name": "Item 57", "price": 84.95}
14 | refund.created | {"qty": 1, "name": "Item 144", "price": 80.26}
The LATERAL jsonb_array_elements(payload->'items') expands each array element. After expansion, you can extract fields from each item object.
Checking if Keys Exist
The ? operator checks if a JSONB object contains a specific key:
Q12: Find Transactions with verification_code
───────
10
15
17
20
45
48
67
81
93
111
The ? operator is fast and useful for finding optional fields that may not be present in all rows.
Q13: Find Transactions with Multiple Keys
───────┼──────────
2 | 387.25
3 | 25666.55
4 | 28093.71
5 | 35757.48
6 | 723.14
7 | 36179.32
8 | 20386.06
9 | 99279.16
10 | 43500.48
11 | 49787.64
Combine ? with AND/OR to check multiple keys at once.
Modifying JSONB: Adding & Removing Keys
Beyond querying, you can update JSONB columns using concatenation and deletion operators:
|| operator — Merges two JSONB objects. Use to add or update keys.
- operator — Removes a key from JSONB.
Q14: Add a Key to JSONB
The metadata for txn_id=1 now includes "processed": true. If the key already existed, its value is updated.
Q15: Remove a Key from JSONB
The - operator removes the key from the JSONB object entirely. After this, the 'processed' key is gone from txn_id=1's metadata.
Challenge
Query the webhooks table:
- Find webhooks where
payloadcontains"event_type": "payment.success". - Extract the transaction amount from
payload->'transaction'->'amount'. - Expand the 'items' array (if present) using
jsonb_array_elements. - For each item, extract 'price' and 'qty' as text.
Bonus: Add a GIN index on the payload column and check the query plan.
Test Your Knowledge
JSON in SQL — best of both worlds.
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →