PostgreSQL JSON & Semi-Structured Data: A Practitioner's Cheat Sheet

You already know SQL. This is the JSON/JSONB toolkit in Postgres, one schema, runnable queries, no fluff.
1. Schema
Three tables. JSONB columns hold the semi-structured parts: user preferences, product attributes, order items and shipping.
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
preferences JSONB NOT NULL DEFAULT '{}'
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL,
attributes JSONB NOT NULL DEFAULT '{}'
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users(user_id),
status TEXT NOT NULL,
ordered_at TIMESTAMPTZ NOT NULL DEFAULT now(),
shipping JSONB NOT NULL, -- object
items JSONB NOT NULL -- array of objects: [{product_id, qty, price}]
);
2. Dummy Data
INSERT INTO users (name, email, preferences) VALUES
('Alice', 'alice@example.com', '{"theme":"dark","lang":"en","notifications":{"email":true,"sms":false},"tags":["beta","newsletter"]}'),
('Bob', 'bob@example.com', '{"theme":"light","lang":"fr","notifications":{"email":false,"sms":true},"tags":["newsletter"]}'),
('Cara', 'cara@example.com', '{"theme":"dark","lang":"en","notifications":{"email":true,"sms":true},"tags":["beta","vip","newsletter"]}');
INSERT INTO products (name, price, attributes) VALUES
('Laptop', 1200, '{"brand":"Acme","specs":{"ram_gb":16,"cpu":"M2"},"colors":["silver","black"]}'),
('Phone', 800, '{"brand":"Zen","specs":{"ram_gb":8,"cpu":"S1"},"colors":["black"]}'),
('Headphones', 150, '{"brand":"Acme","specs":{"wireless":true},"colors":["white","black","red"]}');
INSERT INTO orders (user_id, status, ordered_at, shipping, items) VALUES
(1, 'paid', '2026-01-10', '{"city":"Dhaka","country":"BD","express":true}',
'[{"product_id":1,"qty":1,"price":1200},{"product_id":3,"qty":2,"price":150}]'),
(2, 'pending', '2026-01-12', '{"city":"Paris","country":"FR","express":false}',
'[{"product_id":2,"qty":1,"price":800}]'),
(3, 'paid', '2026-01-15', '{"city":"Berlin","country":"DE"}',
'[{"product_id":1,"qty":2,"price":1200},{"product_id":2,"qty":1,"price":800},{"product_id":3,"qty":1,"price":150}]');
3. ER Diagram
Paste into mermaid.live:
erDiagram
users ||--o{ orders : places
orders }o..o{ products : "items[].product_id (soft ref inside JSONB)"
users {
int user_id PK
text name
text email
jsonb preferences
}
products {
int product_id PK
text name
numeric price
jsonb attributes
}
orders {
int order_id PK
int user_id FK
text status
timestamptz ordered_at
jsonb shipping
jsonb items
}
It will look like this
The dotted line is intentional:
orders -> productslives inside the JSON array, so there is no FK enforcement. That is a real trade-off of embedding references.
4. JSON vs JSONB
JSON |
JSONB |
|
|---|---|---|
| Storage | Raw text, as written | Decomposed binary |
| Whitespace / key order / duplicate keys | Preserved | Normalized (last duplicate wins, keys reordered) |
| Write cost | Cheap (validate only) | Slightly higher (parse + convert) |
| Read / query cost | Re-parsed every access | Fast, no reparse |
| Indexing (GIN) | ❌ | ✅ |
Operators @>, ?, jsonb_set |
❌ | ✅ |
SELECT '{"b":1, "a":2, "a":3}'::json AS j, -- {"b":1, "a":2, "a":3}
'{"b":1, "a":2, "a":3}'::jsonb AS jb; -- {"a": 3, "b": 1}
Rule of thumb: default to JSONB. Use JSON only when you must preserve the exact input (audit logs, signed payloads).
5. Extraction: ->, ->>
-> returns jsonb, ->> returns text. Cast after ->> for comparisons.
SELECT name,
preferences -> 'notifications' AS notif_json,
preferences ->> 'theme' AS theme_text
FROM users;
/*
name |notif_json |theme_text|
-----+-----------------------------+----------+
Alice|{"sms": false, "email": true}|dark |
Bob |{"sms": true, "email": false}|light |
Cara |{"sms": true, "email": true} |dark |
*/
-- arrays by index (0-based; negatives count from the end)
SELECT attributes -> 'colors' ->> 0 AS first_color,
attributes -> 'colors' ->> -1 AS last_color
FROM products;
/*
first_color|last_color|
-----------+----------+
silver |black |
black |black |
white |red |
*/
-- cast for numeric filtering
SELECT name FROM products
WHERE (attributes -> 'specs' ->> 'ram_gb')::int >= 16;
/*
name |
------+
Laptop|
*/
6. Nested Extraction: #>, #>>
Path as a text[]. Cleaner than chaining.
SELECT name,
preferences #> '{notifications,email}' AS email_json,
preferences #>> '{notifications,email}' AS email_text
FROM users;
/*
name |email_json|email_text|
-----+----------+----------+
Alice|true |true |
Bob |false |false |
Cara |true |true |
*/
SELECT name, attributes #>> '{colors,0}' AS first_color FROM products;
/*
name |first_color|
----------+-----------+
Laptop |silver |
Phone |black |
Headphones|white |
*/
7. Containment: @>
"Does the left contain the right?" This is the workhorse for indexed filtering.
-- object containment
SELECT name FROM users WHERE preferences @> '{"theme":"dark"}';
/*
name |
-----+
Alice|
Cara |
*/
-- nested + array containment in one shot
SELECT name FROM users
WHERE preferences @> '{"notifications":{"sms":true},"tags":["beta"]}';
/*
name|
----+
Cara|
*/
-- inside an items array: orders containing product 3
SELECT order_id FROM orders WHERE items @> '[{"product_id":3}]';
/*
order_id|
--------+
1|
3|
*/
Index it:
-- supports @>, @?, @@ only; smaller and faster
CREATE INDEX idx_users_prefs ON users USING GIN (preferences jsonb_path_ops);
-- default jsonb_ops: also supports ?, ?|, ?&
CREATE INDEX idx_orders_items ON orders USING GIN (items);
8. Existence: ?, ?|, ?&
Checks top-level keys or string elements of an array, not nested paths.
-- key exists
SELECT order_id FROM orders WHERE shipping ? 'express';
/*
order_id|
--------+
1|
2|
*/
-- array contains string
SELECT name FROM users WHERE preferences -> 'tags' ? 'vip';
/*
name|
----+
Cara|
*/
-- any of
SELECT name FROM users WHERE preferences -> 'tags' ?| ARRAY['vip','beta'];
/*
name |
-----+
Alice|
Cara |
*/
-- all of
SELECT name FROM users WHERE preferences -> 'tags' ?& ARRAY['beta','newsletter'];
/*
name |
-----+
Alice|
Cara |
*/
⚠️
?collides with positional placeholders in some drivers (JDBC, etc.). Usejsonb_exists(),jsonb_exists_any(),jsonb_exists_all()instead.
9. Modification: JSONB_SET()
jsonb_set(target, path, new_value, create_missing = true). The new value must be jsonb.
-- update nested value
UPDATE users
SET preferences = jsonb_set(preferences, '{notifications,sms}', 'true')
WHERE name = 'Alice';
-- add a new nested key (creates last path element if missing)
UPDATE users
SET preferences = jsonb_set(preferences, '{notifications,push}', 'true', true)
WHERE name = 'Bob';
-- value from SQL: wrap with to_jsonb()
UPDATE users
SET preferences = jsonb_set(preferences, '{lang}', to_jsonb('de'::text))
WHERE name = 'Cara';
-- append to an array
UPDATE users
SET preferences = jsonb_set(preferences, '{tags}', (preferences -> 'tags') || '"early-access"')
WHERE name = 'Alice';
⚠️ Postgres rewrites the entire JSONB value on any update (TOAST included). Big documents with frequent partial updates are a bloat and write-amplification trap.
10. Removal: -, #-
-- remove top-level key
SELECT preferences - 'lang' FROM users;
/*
?column? |
----------------------------------------------------------------------------------------------------------------+
{"tags": ["newsletter"], "theme": "light", "notifications": {"sms": true, "push": true, "email": false}} |
{"tags": ["beta", "vip", "newsletter"], "theme": "dark", "notifications": {"sms": true, "email": true}} |
{"tags": ["beta", "newsletter", "early-access"], "theme": "dark", "notifications": {"sms": true, "email": true}}|
*/
-- remove multiple keys
SELECT preferences - ARRAY['lang','theme'] FROM users;
/*
?column? |
----------------------------------------------------------------------------------------------------------------+
{"tags": ["newsletter"], "theme": "light", "notifications": {"sms": true, "push": true, "email": false}} |
{"tags": ["beta", "vip", "newsletter"], "theme": "dark", "notifications": {"sms": true, "email": true}} |
{"tags": ["beta", "newsletter", "early-access"], "theme": "dark", "notifications": {"sms": true, "email": true}}|
*/
-- remove array element by index
SELECT preferences #- '{tags,0}' FROM users;
/*
?column? |
----------------------------------------------------------------------------------------------------------------------+
{"lang": "fr", "tags": [], "theme": "light", "notifications": {"sms": true, "push": true, "email": false}} |
{"lang": "de", "tags": ["vip", "newsletter"], "theme": "dark", "notifications": {"sms": true, "email": true}} |
{"lang": "en", "tags": ["newsletter", "early-access"], "theme": "dark", "notifications": {"sms": true, "email": true}}|
*/
-- remove nested key
SELECT preferences #- '{notifications,sms}' FROM users;
/*
?column? |
----------------------------------------------------------------------------------------------------------------------+
{"lang": "fr", "tags": [], "theme": "light", "notifications": {"sms": true, "push": true, "email": false}} |
{"lang": "de", "tags": ["vip", "newsletter"], "theme": "dark", "notifications": {"sms": true, "email": true}} |
{"lang": "en", "tags": ["newsletter", "early-access"], "theme": "dark", "notifications": {"sms": true, "email": true}}|
*/
11. Aggregation: JSON_AGG(), JSONB_AGG()
Rows → JSON array.
SELECT user_id,
jsonb_agg(jsonb_build_object('order_id', order_id, 'status', status)
ORDER BY order_id) AS orders
FROM orders
GROUP BY user_id;
/*
user_id|orders |
-------+--------------------------------------+
1|[{"status": "paid", "order_id": 1}] |
2|[{"status": "pending", "order_id": 2}]|
3|[{"status": "paid", "order_id": 3}] |
*/
json_agg preserves insertion order and duplicates as-is. jsonb_agg gives you a jsonb you can immediately index or operate on.
12. Object Aggregation: JSON_OBJECT_AGG(), JSONB_OBJECT_AGG()
Rows → JSON object (key, value).
-- product catalog as {name: price}
SELECT jsonb_object_agg(name, price) FROM products;
/*
jsonb_object_agg |
----------------------------------------------------------+
{"Phone": 800.00, "Laptop": 1200.00, "Headphones": 150.00}|
*/
-- per-user order status map
SELECT user_id, jsonb_object_agg(order_id, status) AS status_by_order
FROM orders
GROUP BY user_id;
/*
user_id|status_by_order |
-------+----------------+
1|{"1": "paid"} |
2|{"2": "pending"}|
3|{"3": "paid"} |
*/
json_object_aggkeeps duplicate keys;jsonb_object_aggkeeps the last one.
13. Expanding Arrays: JSONB_ARRAY_ELEMENTS()
Array → rows. Pair with CROSS JOIN LATERAL.
-- revenue per product, straight from the embedded items
SELECT p.name,
SUM((e.item ->> 'qty')::int * (e.item ->> 'price')::numeric) AS revenue
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS e(item)
JOIN products p ON p.product_id = (e.item ->> 'product_id')::int
WHERE o.status = 'paid'
GROUP BY p.name
ORDER BY revenue DESC;
/*
name |revenue|
----------+-------+
Laptop | 3600|
Phone | 800|
Headphones| 450|
*/
-- need the position? WITH ORDINALITY
SELECT o.order_id, e.idx, e.item ->> 'product_id' AS product_id
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.items) WITH ORDINALITY AS e(item, idx);
/*
order_id|idx|product_id|
--------+---+----------+
1| 1|1 |
1| 2|3 |
2| 1|2 |
3| 1|1 |
3| 2|2 |
3| 3|3 |
*/
Use jsonb_array_elements_text() when elements are scalars (e.g. tags).
14. Expanding Objects: JSONB_EACH(), JSONB_EACH_TEXT()
Object → (key, value) rows.
-- all preference keys; value stays jsonb
SELECT u.name, k.key, k.value
FROM users u
CROSS JOIN LATERAL jsonb_each(u.preferences) AS k;
/*
name |key |value |
-----+-------------+-------------------------------------------+
Bob |lang |"fr" |
Bob |tags |["newsletter"] |
Bob |theme |"light" |
Bob |notifications|{"sms": true, "push": true, "email": false}|
Cara |lang |"de" |
Cara |tags |["beta", "vip", "newsletter"] |
Cara |theme |"dark" |
Cara |notifications|{"sms": true, "email": true} |
Alice|lang |"en" |
Alice|tags |["beta", "newsletter", "early-access"] |
Alice|theme |"dark" |
Alice|notifications|{"sms": true, "email": true} |
*/
-- notification flags as text
SELECT u.name, k.key, k.value
FROM users u
CROSS JOIN LATERAL jsonb_each_text(u.preferences -> 'notifications') AS k
WHERE k.value = 'true';
/*
name |key |value|
-----+-----+-----+
Bob |sms |true |
Bob |push |true |
Cara |sms |true |
Cara |email|true |
Alice|sms |true |
Alice|email|true |
*/
15. Record Conversion: JSONB_TO_RECORD(), JSONB_TO_RECORDSET()
JSON → typed columns. You declare the schema; missing keys become NULL.
-- one object → one row
SELECT o.order_id, s.*
FROM orders o
CROSS JOIN LATERAL jsonb_to_record(o.shipping)
AS s(city text, country text, express boolean);
/*
order_id|city |country|express|
--------+------+-------+-------+
1|Dhaka |BD |true |
2|Paris |FR |false |
3|Berlin|DE | |
*/
-- array of objects → many typed rows (cleaner than array_elements + casts)
SELECT o.order_id, x.*
FROM orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.items)
AS x(product_id int, qty int, price numeric);
/*
order_id|product_id|qty|price|
--------+----------+---+-----+
1| 1| 1| 1200|
1| 3| 2| 150|
2| 2| 1| 800|
3| 1| 2| 1200|
3| 2| 1| 800|
3| 3| 1| 150|
*/
16. JSONPath: JSONPATH, JSONB_PATH_QUERY()
SQL/JSON path (PG12+). Filters, arithmetic and wildcards in one expression.
-- items with qty > 1
SELECT o.order_id, jsonb_path_query(o.items, '$[*] ? (@.qty > 1)') AS bulk_item
FROM orders o;
/*
jsonb_path_query|
----------------+
1 |
2 |
1 |
2 |
*/
-- project a field after filtering
SELECT jsonb_path_query(items, '$[*] ? (@.price > 500).product_id') FROM orders;
/*
order_id|
--------+
1|
3|
*/
-- parameterised path
SELECT order_id
FROM orders
WHERE jsonb_path_exists(items, '$[*] ? (@.qty >= $min)', '{"min":2}');
/*
order_id|
--------+
1|
3|
*/
-- shorthand operators: @? (exists), @@ (predicate)
SELECT order_id FROM orders WHERE items @? '$[*] ? (@.product_id == 3)';
SELECT order_id FROM orders WHERE shipping @@ '$.express == true';
/*
order_id|
--------+
1|
*/
-- collapse results to one array
SELECT jsonb_path_query_array(items, '$[*].price') FROM orders;
/*
jsonb_path_query_array|
----------------------+
[1200, 150] |
[800] |
[1200, 800, 150] |
*/
Related: jsonb_path_query_first(), jsonb_path_exists(), jsonb_path_match(). Add strict/lax mode and silent => true for error handling.
17. Quick Gotchas
Index what you query.
@>and@?use a GIN index, but->> 'x' = '...'does not. For hot scalar paths, use an expression index:CREATE INDEX idx_orders_country ON orders ((shipping ->> 'country'));Promote hot fields to real columns. If you filter, join or constrain on it constantly, it is not semi-structured anymore. Generated columns work well here:
ALTER TABLE orders ADD COLUMN country TEXT GENERATED ALWAYS AS (shipping ->> 'country') STORED;No FK or type enforcement inside JSON. Add
CHECK (jsonb_typeof(items) = 'array')orCHECK (shipping ? 'country')where it matters.JSON null vs SQL NULL.
'null'::jsonbis notNULL.->>on a JSON null returns SQLNULL;->returns jsonbnull.Large documents: every update rewrites the full value. If you update small parts of a large document often, normalize it.
Comments
Join the discussionNo comments yet. Be the first to comment.



