JSONB basics
jsonb stores JSON documents in a binary form that PostgreSQL can index and query.
In this page:
Syntax
column_name jsonb
SELECT column -> 'key' FROM table_name;
SELECT column ->> 'key' FROM table_name;
WHERE column @> '{"key": "value"}'JSONB basics
The jsonb type keeps JSON as a binary, indexable structure.
The -> operator returns JSON, ->> returns text, @> tests containment and ? tests for a key. jsonb_build_object, jsonb_set and jsonb_agg build and change documents, and GIN indexes make containment queries fast. Use json only when you must preserve exact text formatting.
Note:
Use ->> to get a value as text for comparisons and display.
Example: JSONB basics
shop=# CREATE TABLE events (id serial PRIMARY KEY, data jsonb);
shop=# INSERT INTO events (data) VALUES ('{"type": "click", "user": {"name": "Ada", "age": 36}, "tags": ["a", "b"]}');
shop=# SELECT data->'user'->>'name' AS name, (data->'user'->>'age')::int AS age FROM events;
name | age
------+-----
Ada | 36
shop=# SELECT id FROM events WHERE data @> '{"type": "click"}';
id
----
1
shop=# CREATE INDEX idx_events_data ON events USING GIN (data);⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Using json instead of jsonb
- Forgetting ->> returns text
- Storing relational data as JSON
Chapter Summary
- jsonb is a binary JSON type
- -> returns JSON, ->> returns text
- @> tests containment
- GIN indexes speed up queries
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: