JSON columns

A JSON column accepts only valid JSON and stores it in a binary format that can be read by path without reparsing the text. Paths start at $: $.color is a member, $.sizes[0] an array element, $.items[*].sku every element's sku.

-> and ->>

col->'$.path' is shorthand for JSON_EXTRACT(col, '$.path') and returns a JSON value. col->>'$.path' is JSON_UNQUOTE(JSON_EXTRACT(...)) and returns text. The difference shows the moment a JSON string is turned into SQL text, for example by CONCAT.

1CREATE TABLE products (id INT PRIMARY KEY, attrs JSON NOT NULL);2INSERT INTO products VALUES3  (1, '{"color": "red",  "sizes": ["S", "M"], "weight": 1.5}'),4  (2, '{"color": "blue", "sizes": ["M", "L"], "weight": 10}'),5  (3, '{"colour": "red", "sizes": [],         "weight": "10"}');

1SELECT id,2       CONCAT('color: ', attrs->'$.color')  AS with_arrow,3       CONCAT('color: ', attrs->>'$.color') AS with_double_arrow,4       JSON_TYPE(attrs->'$.weight')         AS weight_type,5       attrs->'$.weight' = 10               AS json_equals_10,6       attrs->>'$.weight' = 10              AS text_equals_107FROM products8ORDER BY id;

• With -> the color is the JSON string "red", quotes included; with ->> it is the plain text red.
• JSON values keep their types: JSON_TYPE reports DOUBLE, INTEGER and STRING for the three weights. Comparing the JSON value with the number 10 matches only the JSON number 10; the JSON string "10" is not equal to it. After ->>, the text '10' is converted to a number and matches.
• A path that does not exist returns SQL NULL, not an error. Product 3 spells the key colour, so its color is NULL.

JSON_TABLE: arrays into rows

JSON_TABLE turns a JSON document into a derived table with typed columns, so an array of line items can be joined, grouped and summed like any other rows.

1CREATE TABLE products (sku VARCHAR(10) PRIMARY KEY, name VARCHAR(20) NOT NULL);2INSERT INTO products VALUES ('A', 'Pen'), ('B', 'Ink');3CREATE TABLE orders (id INT PRIMARY KEY, doc JSON NOT NULL);4INSERT INTO orders VALUES5  (1, '{"customer": "Ann", "items": [{"sku": "A", "qty": 2}, {"sku": "B", "qty": 1}]}'),6  (2, '{"customer": "Bob", "items": [{"sku": "A", "qty": 5}, {"sku": "Q", "qty": 1}]}'),7  (3, '{"customer": "Cy",  "items": []}');

1SELECT o.id, o.doc->>'$.customer' AS customer, li.sku, li.qty, p.name AS product2FROM orders o3CROSS JOIN JSON_TABLE(o.doc, '$.items[*]'4             COLUMNS (sku VARCHAR(10) PATH '$.sku',5                      qty INT         PATH '$.qty')) AS li6-- JSON_TABLE text takes the connection's collation; match the column's.7LEFT JOIN products p ON p.sku = li.sku COLLATE utf8mb4_0900_ai_ci8ORDER BY o.id, li.sku;

Each element of $.items[*] became a row, with sku and qty cast to the declared types. An order whose items array is empty contributes no rows. Bob's second item names SKU Q, which is not in products: the JSON column accepted it, because nothing inside a JSON document can be the subject of a foreign key. (The COLLATE is needed because JSON_TABLE text takes the connection's collation; without it this join fails with error 1267.)

Indexing JSON

A plain index cannot be built on a JSON column. You have two options:

• for a scalar path, index a generated column or a functional index on (CAST(doc->>'$.sku' AS CHAR(20))); the next lessons show both;
• for array membership, a multi-valued index on (CAST(doc->'$.sizes' AS CHAR(5) ARRAY)), which serves MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS.

1CREATE TABLE products (2  id    INT PRIMARY KEY,3  attrs JSON NOT NULL,4  INDEX idx_sizes ((CAST(attrs->'$.sizes' AS CHAR(5) ARRAY)))5);6INSERT INTO products VALUES7  (1, '{"sizes": ["S", "M"]}'), (2, '{"sizes": ["M", "L"]}'),8  (3, '{"sizes": []}'),         (4, '{"sizes": ["XL"]}'),9  (5, '{"sizes": ["S"]}');10ANALYZE TABLE products;

1EXPLAIN FORMAT=JSON INTO @plan2  SELECT id FROM products WHERE 'M' MEMBER OF (attrs->'$.sizes');3 4SELECT JSON_UNQUOTE(JSON_EXTRACT(@plan, '$.query_block.table.access_type')) AS access_type,5       JSON_UNQUOTE(JSON_EXTRACT(@plan, '$.query_block.table.key'))         AS index_used,6       (SELECT GROUP_CONCAT(id ORDER BY id) FROM products7         WHERE 'M' MEMBER OF (attrs->'$.sizes'))                            AS ids_with_m;

JSON and normalisation

First normal form asks that each column hold a single value and that a list be stored as rows. A JSON array of line items is a list packed into one field, so it breaks first normal form, and it gives up what a child table would enforce: foreign keys (the orphan Q above), NOT NULL, types, CHECK constraints and a key that stops one SKU appearing twice in an order. The normalised design is a table:

CREATE TABLE order_items (
  order_id INT NOT NULL,
  sku      VARCHAR(10) NOT NULL,
  qty      INT NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, sku),
  FOREIGN KEY (order_id) REFERENCES orders (id),
  FOREIGN KEY (sku) REFERENCES products (sku)
);

JSON_TABLE rebuilds that table on every query, with none of its guarantees. Second and third normal form go further: a non-key column depends on the whole primary key and nothing else, so each fact lives in one place (a product's name in products, not in every order line). JSON earns its place for genuinely variable attributes or a payload stored and returned whole; a path that most queries filter or join on wants to be a real column or a child table.

Common mistakes

• Building text from ->. CONCAT('color: ', doc->'$.color') includes the JSON quotes; use ->>.
• Comparing a JSON number with a JSON string. "10" and 10 are different JSON values, as product 3 shows.
• Expecting an index on the JSON column itself. It is not allowed; index a generated column, an expression, or a multi-valued array.