15.1 JSON in SQL — The Complete SQL Course

JSON lets one column hold a small document: nested objects, arrays, optional keys, and vendor-shaped payloads that would be awkward to flatten immediately. In PostgreSQL, the practical lesson is use jsonb for most stored JSON, then extract, filter, and expand only the parts your query needs.

A cafe inbox with JSONB payloads

Start with one deliberately mixed table. The relational columns say what every row has in common; the payload column holds the shape that varies between orders, events, and preferences:

CREATE TABLE cafe_json_feed (
  feed_id int PRIMARY KEY,
  doc_type text NOT NULL,
  payload jsonb NOT NULL
);
 
INSERT INTO cafe_json_feed VALUES
  (1, 'order', '{"order_no":"A100","channel":"mobile","customer":{"name":"Ada","loyalty":"gold"},"items":[{"sku":"espresso","qty":2,"price":2.50},{"sku":"croissant","qty":1,"price":3.20}],"delivery":{"city":"Berlin","instructions":"leave at counter"},"tags":["rush","paid"]}'::jsonb),
  (2, 'order', '{"order_no":"A101","channel":"counter","customer":{"name":"Linus","loyalty":"silver"},"items":[{"sku":"flat_white","qty":1,"price":4.00},{"sku":"cinnamon_roll","qty":2,"price":4.10}],"delivery":{"city":"Austin","instructions":null},"tags":["paid"]}'::jsonb),
  (3, 'order', '{"order_no":"A102","channel":"mobile","customer":{"name":"Grace","loyalty":"none"},"items":[{"sku":"cold_brew","qty":1,"price":4.50},{"sku":"sparkling_water","qty":2,"price":2.20}],"delivery":{"city":"Lisbon","instructions":"call on arrival"},"tags":["refund_requested"]}'::jsonb),
  (4, 'event', '{"event":"order_paid","order_no":"A100","device":{"kind":"kiosk","id":"K-7"},"source":"pos"}'::jsonb),
  (5, 'preference', '{"customer":"Ada","preferences":{"milk":"oat","receipts":"email"},"allergies":["almond"]}'::jsonb),
  (6, 'preference', '{"customer":"Linus","preferences":{"milk":"whole","receipts":"paper"},"allergies":[]}'::jsonb);

CREATE TABLE
INSERT 0 6

Postgres has both json and jsonb. json stores the original JSON text and reparses it when you work with it. jsonb stores a parsed representation: input is a little more work, but queries can process it faster, use more operators, and build indexes later. The trade-off is visible: jsonb normalizes the value instead of preserving your exact whitespace or object-key order.

SELECT pg_typeof('{"b": 2, "a": 1}'::json) AS json_type,
       pg_typeof('{"b": 2, "a": 1}'::jsonb) AS jsonb_type,
       '{"b": 2, "a": 1}'::jsonb AS jsonb_value;

 json_type | jsonb_type |   jsonb_value
-----------+------------+------------------
 json      | jsonb      | {"a": 1, "b": 2}
(1 row)

That is why PostgreSQL teams usually reach for jsonb when the column is stored and queried. Keep plain json for the rare case where the exact incoming text is itself the fact you must preserve.

Extract the part you mean

JSON operators come in pairs: one keeps JSON, the other returns text. -> returns a JSON value, so nested objects and arrays stay JSONB. ->> returns text, which is what you want for labels, comparisons, and casts. For a path several keys deep, #>> returns text from an array-style path. jsonb_path_query uses SQL/JSON path syntax and can pull many matching values from one document:

SELECT f.feed_id,
       f.payload -> 'customer' AS customer_json,
       f.payload ->> 'channel' AS channel_text,
       f.payload #>> '{delivery,city}' AS city_text,
       sku.value AS item_sku
FROM cafe_json_feed AS f
CROSS JOIN LATERAL jsonb_path_query(f.payload, '$.items[*].sku') AS sku(value)
WHERE f.doc_type = 'order'
ORDER BY f.feed_id, sku.value::text;

 feed_id |             customer_json              | channel_text | city_text |     item_sku
---------+----------------------------------------+--------------+-----------+-------------------
       1 | {"name": "Ada", "loyalty": "gold"}     | mobile       | Berlin    | "croissant"
       1 | {"name": "Ada", "loyalty": "gold"}     | mobile       | Berlin    | "espresso"
       2 | {"name": "Linus", "loyalty": "silver"} | counter      | Austin    | "cinnamon_roll"
       2 | {"name": "Linus", "loyalty": "silver"} | counter      | Austin    | "flat_white"
       3 | {"name": "Grace", "loyalty": "none"}   | mobile       | Lisbon    | "cold_brew"
       3 | {"name": "Grace", "loyalty": "none"}   | mobile       | Lisbon    | "sparkling_water"
(6 rows)

Read the output types carefully. customer_json is still a JSON object, and item_sku is still a JSON string, so the grid includes JSON quotes. channel_text and city_text are ordinary SQL text.

Filter inside the document

Once a value is text, regular SQL comparisons work. But JSONB also has containment: left @> right asks whether the left document contains the smaller JSON shape on the right. This finds mobile orders, then uses a nested text extraction to keep loyalty members:

SELECT f.feed_id,
       f.payload ->> 'order_no' AS order_no,
       f.payload #>> '{customer,loyalty}' AS loyalty
FROM cafe_json_feed AS f
WHERE f.doc_type = 'order'
  AND f.payload @> '{"channel": "mobile"}'::jsonb
  AND f.payload #>> '{customer,loyalty}' <> 'none'
ORDER BY f.feed_id;

 feed_id | order_no | loyalty
---------+----------+---------
       1 | A100     | gold
(1 row)

Containment is exact about JSON shape and JSON values. A string "2" is not the number 2, and an object snippet matches object keys, not arbitrary text. That precision is the point: JSONB is not a LIKE '%mobile%' escape hatch.

Turn arrays into rows

Arrays inside JSON are still arrays inside one row. To analyze item lines, expand them. jsonb_array_elements returns one JSONB value per array element; CROSS JOIN LATERAL lets that function read the current row's payload:

SELECT f.feed_id,
       f.payload ->> 'order_no' AS order_no,
       item.value ->> 'sku' AS sku,
       CAST(item.value ->> 'qty' AS int) AS qty
FROM cafe_json_feed AS f
CROSS JOIN LATERAL jsonb_array_elements(f.payload -> 'items') AS item(value)
WHERE f.doc_type = 'order'
ORDER BY f.feed_id, sku;

 feed_id | order_no |       sku       | qty
---------+----------+-----------------+-----
       1 | A100     | croissant       |   1
       1 | A100     | espresso        |   2
       2 | A101     | cinnamon_roll   |   2
       2 | A101     | flat_white      |   1
       3 | A102     | cold_brew       |   1
       3 | A102     | sparkling_water |   2
(6 rows)

That is the lightweight expansion pattern. PostgreSQL 17 added the more standard-shaped JSON_TABLE, which turns JSON paths into a table expression with named, typed columns:

SELECT f.feed_id,
       jt.sku,
       jt.qty
FROM cafe_json_feed AS f
CROSS JOIN LATERAL JSON_TABLE(
  f.payload,
  '$.items[*]'
  COLUMNS (
    sku text PATH '$.sku',
    qty int PATH '$.qty'
  )
) AS jt
WHERE f.doc_type = 'order'
ORDER BY f.feed_id, jt.sku;

 feed_id |       sku       | qty
---------+-----------------+-----
       1 | croissant       |   1
       1 | espresso        |   2
       2 | cinnamon_roll   |   2
       2 | flat_white      |   1
       3 | cold_brew       |   1
       3 | sparkling_water |   2
(6 rows)

Use whichever shape reads better for the job. jsonb_array_elements feels native to Postgres and keeps each element as JSONB until you choose fields. JSON_TABLE is more relational up front: name the projected columns, give them SQL types, and query them like any other table source.

sql — playgroundlive

⌘/Ctrl + Enter to run

The standard is moving too

SQL:2023 added a native JSON type and dot-notation accessor to the standard, and PostgreSQL's JSON_TABLE support is part of that larger SQL/JSON direction. Do not turn that into a portability fairy tale: engines differ on which JSON pieces they ship and how complete the implementation is. Write PostgreSQL-first code here, and label dialect accents when you leave it.

The next lesson stays in semi-structured territory but switches containers: PostgreSQL arrays and composite types, where the value is structured but no longer JSON.

Checkpoint

Answer all three to mark this lesson complete

JSON in SQL