In a shop, a laptop has memory, a monitor has a screen size, and headphones are wireless or not. Give every property its own column and the table turns into hundreds of mostly empty columns. On top of that, online services send data as JSON. That is why modern relational databases “understand” JSON: SQLite ships JSON functions, PostgreSQL has the json and jsonb types, MySQL has a JSON type. The lesson «PostgreSQL and MySQL in practice» touched on JSON columns briefly; here we go deep — reading, unnesting arrays, building JSON, indexing and, most importantly, when not to use JSON.
Storing JSON: a checked text column
SQLite has no separate JSON type: the document is stored in a TEXT column. To keep rubbish out we add CHECK (json_valid(specs)) — json_valid checks strict, standard JSON. The core, stable data (name, price) stays in products, and the variable properties go into a separate product_specs table as JSON. Inside the document you address values with a path:
JSON pathDefinition
$ is the whole document, .key an object field, [0] an array element (counting from 0). $.ports[0] is the first port; in SQLite $.ports[#-1] is the last element. If the path doesn't exist, the result is NULL, with no error.
CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY REFERENCES products(id),
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
SELECT p.name,
json_extract(s.specs, '$.color') AS color,
s.specs ->> '$.ram_gb' AS ram_gb,
s.specs ->> '$.ports[0]' AS first_port
FROM product_specs AS s
JOIN products AS p ON p.id = s.product_id
ORDER BY p.id;Expected output
name | color | ram_gb | first_port Laptop | silver | 16 | usb-c Smartphone | black | 8 | usb-c Headphones | black | NULL | NULL Monitor | NULL | NULL | hdmi
ram_gb for the headphones) simply gives NULL.CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY,
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES (5, '{"color": "blue", "pens": 12,}');Expected output
Error: CHECK constraint failed: json_valid(specs)
CHECK rejects the row.Reading values: json_extract, -> and ->>
json_extract(doc, path) and doc ->> path return an ordinary SQL value: text, a number or NULL. doc -> path returns a piece of JSON: a string comes with quotes, an array with brackets. For a simple key you may leave out $.: specs ->> 'color'.
SELECT '{"color": "black", "ports": ["usb-c"]}' -> '$.color' AS one_arrow,
'{"color": "black", "ports": ["usb-c"]}' ->> '$.color' AS two_arrows,
'{"color": "black", "ports": ["usb-c"]}' -> 'ports' AS ports_json,
typeof('{"ram_gb": 8}' -> '$.ram_gb') AS type_one,
typeof('{"ram_gb": 8}' ->> '$.ram_gb') AS type_two;Expected output
one_arrow | two_arrows | ports_json | type_one | type_two "black" | black | ["usb-c"] | text | integer
Predict the result
Problem
In one product's document the memory was written as text by mistake: {"ram_gb": "4"}. Will WHERE specs ->> 'ram_gb' > 10 find this product?
Show solutionHide solution
Solution
->> returns the text '4', because in the JSON the value is in quotes.2) In SQLite any text is “greater” than any number (the ordering of types).
3) So yes, it will — the 4 GB product lands in the “more than 10” list. Fix:
CAST(specs ->> 'ram_gb' AS INTEGER) > 10, or check the type on write with CHECK (json_type(specs, '$.ram_gb') = 'integer').SELECT '{"ram_gb": "4"}' ->> '$.ram_gb' > 10 AS text_check,
CAST('{"ram_gb": "4"}' ->> '$.ram_gb' AS INTEGER) > 10 AS number_check,
'{"ram_gb": 4}' ->> '$.colour' AS typo;Expected output
text_check | number_check | typo 1 | 0 | NULL
colour) gives no error either — just NULL.If a JSON field is searched often, create an expression index. Careful: SQLite uses the index only when the query's expression matches the indexed one exactly. An index on json_extract(specs, '$.color') is not used by specs ->> '$.color' = ... (checked: SCAN), but the same expression works:
CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY REFERENCES products(id),
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
CREATE INDEX idx_specs_color ON product_specs (specs ->> '$.color');
EXPLAIN QUERY PLAN
SELECT product_id FROM product_specs WHERE specs ->> '$.color' = 'black';Expected output
id | parent | notused | detail 3 | 0 | 53 | SEARCH product_specs USING COVERING INDEX idx_specs_color (<expr>=?)
Unnesting arrays and building JSON
json_eachDefinition
A table-valued function: it returns one row per element of a JSON array (or object); its main columns are key (index or key), value and type. It is written in FROM like a table and may refer to the columns of the table before it.
CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY REFERENCES products(id),
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
SELECT p.name, j.key AS idx, j.value AS port
FROM product_specs AS s
JOIN products AS p ON p.id = s.product_id,
json_each(s.specs, '$.ports') AS j
ORDER BY p.id, j.key;Expected output
name | idx | port Laptop | 0 | usb-c Laptop | 1 | hdmi Smartphone | 0 | usb-c Monitor | 0 | hdmi Monitor | 1 | dp
json_each behaves like an inner join.Once the array is unnested into rows, ordinary SQL works: grouping, joins, filters. Which port appears on how many products?
CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY REFERENCES products(id),
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
SELECT j.value AS port, COUNT(*) AS products
FROM product_specs AS s, json_each(s.specs, '$.ports') AS j
GROUP BY j.value
ORDER BY products DESC, port;Expected output
port | products hdmi | 2 usb-c | 2 dp | 1
The opposite direction — from a table to JSON — is needed for API responses. json_object(key, value, ...) builds one object, the aggregate json_group_array(x) builds an array from all values of a group (since SQLite 3.44 you can write ORDER BY inside it), and json_group_object(key, value) builds an object. The orders of customers from Azerbaijan, one JSON per customer:
SELECT c.name,
json_object('orders', COUNT(*),
'products', json_group_array(p.name ORDER BY o.order_date))
AS summary
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id
WHERE c.country = 'Azerbaijan'
GROUP BY c.id
ORDER BY c.id;Expected output
name | summary
Anar Mustafayev | {"orders":3,"products":["Laptop","Headphones","Notebook"]}
Lalə Əhmədova | {"orders":2,"products":["Notebook","Headphones"]}
Zəhra Hüseynli | {"orders":2,"products":["Smartphone","Water bottle"]}Four functions change a document: json_set (replaces if present, adds if not), json_insert (only adds), json_replace (only replaces) and json_remove. The path $.ports[#] appends to the end of an array. Each function returns a new document, so you write UPDATE ... SET specs = json_set(specs, ...):
CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY REFERENCES products(id),
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
UPDATE product_specs
SET specs = json_set(specs, '$.ram_gb', 32, '$.ports[#]', 'sd-card')
WHERE product_id = 1;
UPDATE product_specs
SET specs = json_remove(specs, '$.color')
WHERE product_id = 2;
SELECT product_id, specs FROM product_specs WHERE product_id <= 2 ORDER BY product_id;Expected output
product_id | specs
1 | {"ram_gb":32,"color":"silver","ports":["usb-c","hdmi","sd-card"]}
2 | {"ram_gb":8,"ports":["usb-c"]}PostgreSQL: json vs jsonb, operators, GIN
PostgreSQL has two types. json stores the text exactly as given — whitespace, key order and duplicate keys included — and parses it again on every access. jsonb is converted to a binary form on write: of duplicate keys the last one wins, keys are reordered, reading is fast, and indexes and the “contains” operators are supported. The rule is simple: almost always jsonb; json only when the exact text must be kept. The code in this section was checked in PGlite (PostgreSQL 18).
SELECT '{"b": 1, "a": 2, "a": 3}'::json AS as_json,
'{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;Expected output
as_json | as_jsonb
---------------------------+------------------
{"b": 1, "a": 2, "a": 3} | {"a": 3, "b": 1}
(1 row)| Operator | Meaning | Example |
|---|---|---|
-> / ->> | by key or index: JSON / text | specs ->> 'color' |
#> / #>> | by a path array: JSON / text | specs #>> '{ports,0}' |
@> | the left side contains the right side | specs @> '{"color": "black"}' |
? | this key (or this string in an array) exists | specs ? 'wireless' |
|| / - | merge / remove a key | specs - 'color' |
CREATE TABLE product_specs (product_id int PRIMARY KEY, specs jsonb NOT NULL);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
CREATE INDEX idx_specs ON product_specs USING GIN (specs);
SELECT product_id,
specs -> 'ports' AS ports,
specs #>> '{ports,0}' AS first_port,
(specs ->> 'ram_gb')::int * 2 AS double_ram
FROM product_specs
WHERE specs @> '{"color": "black"}'
ORDER BY product_id;
SELECT product_id FROM product_specs
WHERE specs -> 'ports' ? 'hdmi'
ORDER BY product_id;Expected output
product_id | ports | first_port | double_ram
------------+-----------+------------+------------
2 | ["usb-c"] | usb-c | 16
3 | | |
(2 rows)
product_id
------------
1
9
(2 rows)->> returns text, so it is cast with ::int before arithmetic.GIN (Generalized Inverted Index) is an “inverted index” of all keys and values inside the documents — like the subject index at the back of a book. The default operator class (jsonb_ops) speeds up @>, ?, ?| and ?&; jsonb_path_ops is smaller and faster but supports only @> (and the jsonpath operators). For specs ->> 'color' = 'black' GIN doesn't help — that needs an ordinary B-tree expression index: CREATE INDEX ON product_specs ((specs ->> 'color')). The table is tiny, so we “force” the planner towards indexes with SET enable_seqscan = off:
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT product_id FROM product_specs WHERE specs @> '{"color": "black"}';
EXPLAIN (COSTS OFF)
SELECT product_id FROM product_specs WHERE specs ->> 'color' = 'black';Expected output
QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on product_specs
Recheck Cond: (specs @> '{"color": "black"}'::jsonb)
-> Bitmap Index Scan on idx_specs
Index Cond: (specs @> '{"color": "black"}'::jsonb)
(4 rows)
QUERY PLAN
-------------------------------------------------------
Seq Scan on product_specs
Disabled: true
Filter: ((specs ->> 'color'::text) = 'black'::text)
(3 rows)@> goes through the GIN index, while ->> = is still a sequential scan (Disabled: true — the planner has no other way).| Task | SQLite | PostgreSQL (jsonb) | MySQL 8 |
|---|---|---|---|
| Read a value | d ->> '$.a' | d ->> 'a' | d ->> '$.a' |
| Array to rows | json_each | jsonb_array_elements | JSON_TABLE |
| Array from a group | json_group_array | jsonb_agg | JSON_ARRAYAGG |
| Build an object | json_object | jsonb_build_object | JSON_OBJECT |
| Change a field | json_set | jsonb_set | JSON_SET |
| Index | expression index | GIN or an expression index | index on a generated column |
When JSON is a bad idea, and where it fits
- The field is present in every row (price, customer, date) — that is an ordinary column.
- The field refers to another table — inside JSON there are no foreign keys and no join index; build a junction table instead of id arrays.
- You need
NOT NULL,CHECK,UNIQUE— types and rules inside JSON aren't checked by themselves. - One field of a large document changes often — the whole document is rewritten every time.
If some JSON field has become “popular”, you can promote it to a generated column: its value is computed from the JSON automatically, but it is indexed like an ordinary column and looks simple in queries. SQLite lets ALTER TABLE add only a VIRTUAL (not stored) one:
CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY REFERENCES products(id),
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
ALTER TABLE product_specs
ADD COLUMN color TEXT GENERATED ALWAYS AS (specs ->> '$.color') VIRTUAL;
CREATE INDEX idx_specs_color ON product_specs (color);
SELECT product_id, color FROM product_specs WHERE color = 'black' ORDER BY product_id;Expected output
product_id | color 2 | black 3 | black
Mini project: importing orders from an API. The healthiest place for JSON is the border of the system: a service sends JSON, and we turn it into ordinary tables right away.
Keep the raw JSON
The incoming request body goes into
api_orderswith ajson_validcheck — if something goes wrong, you still have the original.Unnest the array
json_each(a.body, '$.items')gives one row per item.Write to the relational table
INSERT INTO orders ... SELECT— the->>values land in ordinary columns.Check with plain SQL
Customer and product names and amounts via
JOIN.
CREATE TABLE api_orders (id INTEGER PRIMARY KEY, body TEXT CHECK (json_valid(body)));
INSERT INTO api_orders (body) VALUES
('{"customer_id": 4, "date": "2025-08-01",
"items": [{"product_id": 3, "qty": 2}, {"product_id": 5, "qty": 3}]}'),
('{"customer_id": 7, "date": "2025-08-02",
"items": [{"product_id": 10, "qty": 1}]}');
INSERT INTO orders (customer_id, product_id, quantity, order_date)
SELECT a.body ->> '$.customer_id', i.value ->> '$.product_id',
i.value ->> '$.qty', a.body ->> '$.date'
FROM api_orders AS a, json_each(a.body, '$.items') AS i;
SELECT o.id, c.name AS customer, p.name AS product, o.quantity,
ROUND(o.quantity * p.price, 2) AS amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id
WHERE o.id > 12
ORDER BY o.id;Expected output
id | customer | product | quantity | amount 13 | Olga Ivanova | Headphones | 2 | 241 14 | Olga Ivanova | Pen set | 3 | 23.7 15 | Mehmet Kaya | Chess set | 1 | 42
Notice that JSON lived only at the border of the system — after the import the data is relational again, and foreign keys, indexes and plain GROUP BY all work. This is the healthiest use of JSON in SQL.
Exercise
From the product_specs table in the starter code, show the name and the number of ports (ports, with json_array_length) of every product that has a ports array. Sort by the number of ports descending, then by name.
CREATE TABLE product_specs (
product_id INTEGER PRIMARY KEY REFERENCES products(id),
specs TEXT NOT NULL CHECK (json_valid(specs))
);
INSERT INTO product_specs VALUES
(1, '{"ram_gb": 16, "color": "silver", "ports": ["usb-c", "hdmi"]}'),
(2, '{"ram_gb": 8, "color": "black", "ports": ["usb-c"]}'),
(3, '{"color": "black", "wireless": true}'),
(9, '{"size_in": 27, "ports": ["hdmi", "dp"]}');
SELECT p.name
FROM product_specs AS s
JOIN products AS p ON p.id = s.product_id;Expected output
name | ports Laptop | 2 Monitor | 2 Smartphone | 1
Exercise
Build a category list for an API: for each category, the number of products (products) and a JSON array of their names in alphabetical order (names). Sort by category.
SELECT category, COUNT(*) AS products
FROM products
GROUP BY category
ORDER BY category;Expected output
category | products | names Accessories | 2 | ["Backpack","Water bottle"] Electronics | 4 | ["Headphones","Laptop","Monitor","Smartphone"] Games | 1 | ["Chess set"] Home | 1 | ["Desk lamp"] Stationery | 2 | ["Notebook","Pen set"]
Remember
- SQLite stores JSON as
TEXT(or aBLOBviajsonb());CHECK (json_valid(...))keeps rubbish out. ->returns a piece of JSON,->>andjson_extractan ordinary SQL value; a missing path givesNULL.json_eachunnests an array into rows;json_objectandjson_group_arraybuild JSON from a table.- In PostgreSQL almost always use
jsonb:@>and?are sped up by a GIN index,->> =needs an expression index. - Stable, related and validated fields stay in columns; JSON is for truly variable attributes and the system's border.