MySQL JSON Functions

JSON functions are like tools for reaching inside a little labeled bundle in one cell to read or change just one item without opening the whole thing.

In this page:

Working with JSON Columns
Extracting JSON Values
Creating JSON Data
Modifying JSON Data
Querying by JSON Key Values

Syntax

sql

JSON_EXTRACT(json_column, '$.key')
json_column->'$.key'
JSON_OBJECT('key1', value1, 'key2', value2)

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

sql

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

sql

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

sql

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

sql

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

sql

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:

← Previous Lesson SHOW TABLES & DESCRIBE Next Lesson → Full-Text Search