How to Use JSON in MySQL

How do I use JSON columns and functions in MySQL?

MySQL’s JSON data type lets you store, query, and manipulate structured JSON documents directly inside tables.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why choose JSON columns over TEXT?

JSON columns validate input, preserve key order, and let MySQL index individual paths. TEXT lacks validation and can’t be efficiently queried with JSON functions.

How do I declare a JSON column?

Add a JSON column type when creating or altering a table. Example: ALTER TABLE Products ADD COLUMN specs JSON;

What syntax extracts JSON values?

Use JSON_EXTRACT(col, '$.path') or the shorthand col->'$.path'. They return the JSON value at the specified path.

How can I build JSON objects in SELECT?

JSON_OBJECT() and JSON_ARRAYAGG() turn relational rows into well-formed JSON objects or arrays for APIs.

Which functions help modify JSON?

Use JSON_SET(), JSON_REPLACE(), and JSON_REMOVE() to update documents atomically without rewriting the full column.

How do I index a JSON path?

Create a virtual generated column that extracts the path, then index it: ALTER TABLE Orders ADD order_year INT GENERATED ALWAYS AS (JSON_EXTRACT(details,'$.year')) STORED, ADD INDEX(order_year);

Can I join on JSON keys?

Yes—use a generated column or JSON_EXTRACT in the join condition, but be aware that functions in joins may prevent index use.

Best practice roundup

Validate JSON on insert, keep documents small, index frequently used paths, and back up with traditional relational columns when values must be enforced.

Why How to Use JSON in MySQL is important

How to Use JSON in MySQL Example Usage


-- Return each customer with a JSON array of their order IDs
SELECT
  c.id,
  c.name,
  JSON_ARRAYAGG(o.id) AS order_ids
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

How to Use JSON in MySQL Syntax


-- Create table with JSON
CREATE TABLE Orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    details JSON,
    INDEX idx_customer (customer_id)
);

-- Core functions
SELECT JSON_OBJECT('id', id, 'total', total_amount)       -- build JSON
FROM Orders;

SELECT JSON_EXTRACT(details, '$.shipping.city')            -- query path
FROM Orders;

UPDATE Orders
SET details = JSON_SET(details, '$.status', 'shipped')     -- modify JSON
WHERE id = 42;

Common Mistakes

Storing JSON as TEXT. This bypasses validation and stops MySQL from using JSON indexes. Always declare the column as JSON to gain type-checking and function support.
Running functions in WHERE without indexes. JSON_EXTRACT on the fly forces full scans. Instead, create virtual generated columns, index them, and query the generated column.