# 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.

```sql
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

```sql
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](https://mermaid.live):

```mermaid
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

![](https://cdn.hashnode.com/uploads/covers/6437a45c54a76290a679588c/3143de3d-cfb9-4e12-854b-3e537939755b.png align="center")

> 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` | ❌ | ✅ |

```sql
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.

```sql
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.

```sql
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.

```sql
-- 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:**

```sql
-- 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.

```sql
-- 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**.

```sql
-- 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: `-`, `#-`

```sql
-- 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.

```sql
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).

```sql
-- 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`.

```sql
-- 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.

```sql
-- 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`.

```sql
-- 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.

```sql
-- 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:

  ```sql
  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:

  ```sql
  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.
