PostgreSQL JSONB Queries
advancedpostgresql
2 min read
What is JSONB?
JSONB is PostgreSQL's binary JSON type. It stores JSON data in a decomposed binary format, enabling efficient querying, indexing, and manipulation.
Operators
| Operator | Description |
|---|---|
-> | Get JSON element (returns jsonb) |
->> | Get JSON element (returns text) |
@> | Contains (left contains right) |
<@ | Contained by |
? | Key exists |
Syntax
SELECT data->>'key' FROM table_name;
SELECT data->'address'->>'city' FROM table_name;
SELECT * FROM table_name WHERE data @> '{"status": "active"}'::jsonb;Key Points
- JSONB vs JSON — JSONB is stored in binary format, is faster for queries, and supports indexing. Always prefer JSONB.
- GIN Indexes — Create a GIN index on JSONB columns for fast containment and key-existence queries.
- Path Queries — Use
#>>for path-based access:data #>> '{address, city}'. - jsonb_each — Expands a JSONB object into key-value rows.
- Updating — Use
jsonb_set()to update specific keys without replacing the entire value.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
PostgreSQL JSONB Queries Challenge
Write a query that solve this task: get the name and city from a JSONB column.
Expected result
Name and city extracted as text from the JSONB data column.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is JSONB?
Initializing database...Each run starts from fresh sample data.
More Examples
Filter by JSONB containment
Find records where JSONB data contains a specific key-value pair.
Initializing database...Each run starts from fresh sample data.
Update a JSONB field
Update a specific key within a JSONB column.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between -> and ->> in PostgreSQL?
-> returns the JSON element as jsonb type. ->> returns it as text. Use ->> when you need to compare or display the value as text.
How do I index a JSONB column?
Create a GIN index: CREATE INDEX idx_data ON table_name USING GIN (data_column);