Skip to main content

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

Updated 11 min readMarkdown
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 -> products lives 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.). Use jsonb_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_agg keeps duplicate keys; jsonb_object_agg keeps 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') or CHECK (shipping ? 'country') where it matters.

  • JSON null vs SQL NULL. 'null'::jsonb is not NULL. ->> on a JSON null returns SQL NULL; -> returns jsonb null.

  • Large documents: every update rewrites the full value. If you update small parts of a large document often, normalize it.

No comments yet. Be the first to comment.