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:

sql
txn_id | metadata
───────┼────────────────────────────────────────
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 →

sql
txn_id | card_last4 | device
───────┼────────────┼──────────
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 →→

sql
txn_id | card_last4 | device | ip_address
───────┼────────────┼─────────┼───────────────────
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

sql
txn_id | amount | metadata
───────┼───────────┼────────────────────────────────────────────────
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

sql
txn_id | amount | metadata
───────┼───────────┼─────────────────────────────────────────────────
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

sql
txn_id | amount | risk_score
───────┼───────────┼────────────
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

sql
txn_id | key | value
───────┼────────────┼──────────────────────
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

sql
webhook_id | event_type | elem
────────────┼────────────────┼──────────────────────────────────
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.

Nested Navigation & Indexing with GIN

JSONB supports deep nesting. You can chain → operators to navigate multiple levels. For frequent containment queries, create a GIN index for performance.

Q9: Query Nested JSONB Paths

sql
webhook_id | merchant_name | txn_amount
────────────┼───────────────────┼────────────
1 | "CloudHosting KE" | 763.26
2 | "UrbanBasket" | 5204.16
3 | "UrbanBasket" | 6638.36
4 | "SkincareClinic" | 9628.94
5 | "LabTestsDirect" | 8528.87

Chaining → operators lets you navigate deep nesting: payload->'merchant'->'name' reaches the nested name field.

Q10: Create a GIN Index

sql
CREATE INDEX

GIN (Generalized Inverted Index) indexes accelerate JSONB containment (@>) queries. Without this index, PostgreSQL performs a slow sequential scan.

Q11: Verify Index Speed with EXPLAIN ANALYZE

sql
Seq Scan on transactions (cost=0.00..17.25 rows=111 width=12) (actual time=0.002..0.056 rows=83)
Filter: (metadata @> '{"country": "IN"}'::jsonb)
Rows Removed by Filter: 417
Planning Time: 0.020 ms
Execution Time: 0.062 ms

The plan shows a sequential scan (Seq Scan). With the GIN index, this would use index-based lookup, speeding up large datasets significantly.

Checking if Keys Exist

The ? operator checks if a JSONB object contains a specific key:

Q12: Find Transactions with verification_code

sql
txn_id
───────
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

sql
txn_id | amount
───────┼──────────
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

sql
UPDATE 1

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

sql
UPDATE 1

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 payload contains "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.

Show hint
sql

Test Your Knowledge

Question 1 of 5
What is the difference between → and →→ operators?
Question 2 of 5
How do you cast a JSONB value to INTEGER for comparison?
Question 3 of 5
What does the @> operator do?
Question 4 of 5
What does jsonb_each do?
Question 5 of 5
What is the best index type for JSONB containment (@>) queries?
0
/5
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →