MySQL JSON Functions
In this page:
Working with JSON Columns
MySQL can store an entire JSON document inside a single column, which is a good fit for optional or loosely structured data — like a product's variable attributes — that doesn't map cleanly onto fixed relational columns.
Example: Working with JSON Columns
CREATE TABLE products (id INT, attributes JSON);
INSERT INTO products VALUES (1, '{"color": "red", "size": "M"}');Extracting JSON Values
JSON_EXTRACT, or the more concise inline arrow operator, lets you pull a specific value out of a JSON column directly inside a query, so you can filter or select on nested JSON data without loading the whole document into your application.
Example: Extracting JSON Values
CREATE TABLE products (id INT, attributes JSON);
INSERT INTO products VALUES (1, '{"color": "red", "size": "M"}');
SELECT id, JSON_EXTRACT(attributes, '$.color') AS color FROM products;
SELECT id, attributes->'$.color' AS color FROM products;Creating JSON Data
Rather than hand-writing raw JSON text, MySQL provides functions that assemble relational values — numbers, strings, other columns — directly into properly formatted JSON output for you.
Example: Creating JSON Data
SELECT JSON_OBJECT('color', 'red', 'size', 'M') AS built_json;Modifying JSON Data
JSON_SET updates a value inside an existing JSON document in place, either overwriting an existing key or adding a new one, without you needing to replace the entire document.
Example: Modifying JSON Data
CREATE TABLE products (id INT, attributes JSON);
INSERT INTO products VALUES (1, '{"color": "red"}');
UPDATE products SET attributes = JSON_SET(attributes, '$.size', 'M') WHERE id = 1;
SELECT * FROM products;Querying by JSON Key Values
Filtering rows based on a value buried inside a JSON column works about as efficiently as filtering on a normal column, especially once a generated column or index is set up over the JSON path you query most often.
Example: Querying by JSON Key Values
CREATE TABLE products (id INT, attributes JSON);
INSERT INTO products VALUES (1, '{"color": "red"}'), (2, '{"color": "blue"}');
SELECT * FROM products WHERE JSON_EXTRACT(attributes, '$.color') = 'red';Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: