Working with JSON

Modern databases can store and query JSON documents inside ordinary tables. That's useful for data with a flexible shape — product attributes that vary by category, API payloads, user preferences — while keeping the rest of the data relational. This lesson uses SQLite's JSON functions and maps them to other databases.

Storing JSON

SQL

ALTER TABLE products ADD COLUMN attributes TEXT;

UPDATE products SET attributes = '{"colour": "black", "wireless": true, "battery_hours": 24}' WHERE id = 4;
UPDATE products SET attributes = '{"colour": "white", "watts": 65}' WHERE id = 5;
UPDATE products SET attributes = '{"pages": 320, "format": "hardback", "tags": ["astronomy", "maps"]}' WHERE id = 3;

SELECT name, attributes FROM products WHERE attributes IS NOT NULL;

Result 3 rows

name	attributes
Night Sky Atlas	{"pages": 320, "format": "hardback", "tags": ["astronomy", "maps"]}
Wireless Earbuds	{"colour": "black", "wireless": true, "battery_hours": 24}
USB-C Charger	{"colour": "white", "watts": 65}

A keyboard needs different details from a book. Rather than dozens of mostly-empty columns, a JSON column holds whatever applies to each product. PostgreSQL has dedicated json/jsonb types and MySQL a JSON type; SQLite stores JSON as text (or as binary jsonb from version 3.45).

Extracting values

SQL

ALTER TABLE products ADD COLUMN attributes TEXT;
UPDATE products SET attributes = '{"colour": "black", "wireless": true, "battery_hours": 24}' WHERE id = 4;
UPDATE products SET attributes = '{"pages": 320, "format": "hardback", "tags": ["astronomy", "maps"]}' WHERE id = 3;

SELECT name,
       json_extract(attributes, '$.colour') AS colour,
       attributes ->> '$.pages' AS pages,
       attributes -> '$.tags' AS tags_json,
       attributes ->> '$.tags[0]' AS first_tag
FROM products
WHERE id IN (3, 4);

Result 2 rows

name	colour	pages	tags_json	first_tag
Night Sky Atlas	NULL	320	["astronomy","maps"]	astronomy
Wireless Earbuds	black	NULL	NULL	NULL

A path like $.colour navigates the document: $ is the root, .key selects a field and [0] an array element. ->> returns a plain SQL value; -> returns JSON. Missing paths give NULL.

Filtering on JSON

SQL

ALTER TABLE products ADD COLUMN attributes TEXT;
UPDATE products SET attributes = '{"colour": "black", "wireless": true}' WHERE id = 4;
UPDATE products SET attributes = '{"colour": "white", "watts": 65}' WHERE id = 5;
UPDATE products SET attributes = '{"colour": "black", "wireless": false}' WHERE id = 6;

SELECT name FROM products WHERE attributes ->> '$.colour' = 'black';

Result 2 rows

name
Wireless Earbuds
Mechanical Keyboard

Building JSON from rows

SQL

SELECT json_object('id', id, 'name', name, 'price', price) AS product_json
FROM products
WHERE category = 'Toys';

Result 2 rows

product_json
{"id":10,"name":"Jigsaw Puzzle 1000","price":18}
{"id":11,"name":"Wooden Train Set","price":39.9}

SQL

SELECT c.name,
       json_group_array(o.id) AS order_ids
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
HAVING COUNT(*) > 1;

Result 5 rows

name	order_ids
Ana Pereira	[1,3,11]
Ben Carter	[2,7]
Chloé Martin	[4,13]
David Müller	[5,14]
George Brown	[8,15]

Databases can produce JSON directly — handy for APIs. PostgreSQL uses json_build_object and json_agg; MySQL uses JSON_OBJECT and JSON_ARRAYAGG; SQL Server uses FOR JSON.

Turning JSON arrays into rows

SQL

SELECT value AS tag
FROM json_each('["astronomy", "maps", "gift"]');

Result 3 rows

tag
astronomy
maps
gift

SQL

ALTER TABLE products ADD COLUMN attributes TEXT;
UPDATE products SET attributes = '{"tags": ["astronomy", "maps"]}' WHERE id = 3;
UPDATE products SET attributes = '{"tags": ["gardening", "gift"]}' WHERE id = 1;

SELECT p.name, t.value AS tag
FROM products AS p, json_each(p.attributes, '$.tags') AS t
ORDER BY tag;

Result 4 rows

name	tag
Night Sky Atlas	astronomy
The Patient Gardener	gardening
The Patient Gardener	gift
Night Sky Atlas	maps

json_each expands an array into a table of rows, so you can join, filter and count JSON contents with normal SQL. The PostgreSQL equivalents are jsonb_array_elements and jsonb_each; MySQL and Oracle have JSON_TABLE.

Modifying JSON

SQL

ALTER TABLE products ADD COLUMN attributes TEXT;
UPDATE products SET attributes = '{"colour": "black", "wireless": true}' WHERE id = 4;

UPDATE products
SET attributes = json_set(attributes, '$.colour', 'navy', '$.warranty_years', 2)
WHERE id = 4;

SELECT attributes FROM products WHERE id = 4;

Result 1 row

attributes
{"colour":"navy","wireless":true,"warranty_years":2}

Validation

SQL

SELECT json_valid('{"a": 1}') AS good, json_valid('{a: 1}') AS bad;

Result 1 row

good	bad
1	0

Add CHECK (json_valid(attributes)) to make sure only valid JSON is stored.

JSON or columns?

Use real columns for data you filter, join, sort or constrain regularly — they're faster, typed and validated. Use JSON for genuinely variable or rarely queried details. If you find yourself filtering on a JSON field constantly, promote it to a column (or index it: PostgreSQL GIN indexes, or expression indexes on the extracted value).

Try it yourself

Store a preferences document for a customer and read individual settings from it.

PreviousTriggers NextSQL Injection & Security