Working with JSON in SQL
Store, extract, build, validate and index semi-structured data inside a relational database
Big Idea: A
JSON column lets a single cell hold a whole document — nested objects and arrays — instead of a flat scalar. MySQL parses and validates that document on write, then gives you a family of functions and the -> / ->> operators to reach inside it at query time. This is powerful for sparse, variable or evolving attributes, but it is not a licence to abandon normalization: every design choice trades schema flexibility against query performance and data integrity.
By the end of this topic you will be able to:
- declare a
JSONcolumn and understand how MySQL stores and validates it, - extract values with
JSON_EXTRACT, the->operator and the unquoting->>operator, - read a JSON path like
$.color,$.tags[0]or$.dims.w, - build JSON with
JSON_OBJECT,JSON_ARRAYand aggregate it withJSON_ARRAYAGG/JSON_OBJECTAGG, - modify documents with
JSON_SET,JSON_REMOVEand friends, - validate with
JSON_VALID/JSON_TYPEand index JSON via generated columns and multi-valued indexes, - decide honestly when a real relational table beats a JSON blob.
1. Our example schema
Everything below runs against one tiny shop database. Each product has a few fixed columns and a JSON column called attributes that carries whatever varies per product: colour, size, an array of tags, nested dimensions. A phone and a t-shirt do not share the same specs, and inventing a wide table with dozens of mostly-empty columns would be wasteful — this is the classic case for JSON.
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(8,2) NOT NULL,
attributes JSON
);
INSERT INTO products (name, price, attributes) VALUES
('Cotton Tee', 19.99,
'{"color":"red","size":"M","tags":["sale","cotton"]}'),
('Running Shoe', 89.50,
'{"color":"black","size":42,"tags":["sport"],"dims":{"w":10,"h":30}}'),
('Ceramic Mug', 12.00,
'{"color":"white","tags":[]}');Notice the second row has a nested dims object and a numeric size, while the mug omits size entirely and has an empty tags array. JSON tolerates this per-row variation; a fixed set of columns cannot.