JSON columns

Store, query and update JSON with ->, ->>, JSON_EXTRACT, JSON_TABLE, and index it with generated columns.

Sometimes data doesn't fit neatly into fixed columns: product attributes that differ per category, user preferences, API payloads, event metadata. MySQL's native JSON type stores JSON documents, validates them on insert, keeps them in an efficient binary format, and lets you query, update and index values inside them.

Creating and inserting JSON#

SQL

CREATE TABLE products (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    attrs JSON NOT NULL
);

INSERT INTO products (name, attrs) VALUES
    ('Laptop Pro', '{"brand": "Acme", "ram_gb": 16, "ports": ["usb-c", "hdmi"], "dims": {"w": 31, "h": 1.6}}'),
    ('Phone X',    '{"brand": "Zeta", "ram_gb": 8,  "ports": ["usb-c"], "colours": ["black", "blue"]}'),
    ('Desk Lamp',  JSON_OBJECT('brand', 'Lumo', 'watts', 9, 'ports', JSON_ARRAY()));

INSERT INTO products (name, attrs) VALUES ('Broken', '{"brand": "Oops",}');

Output

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 17 in value for column 'products.attrs'.

Invalid JSON is rejected. JSON_OBJECT() and JSON_ARRAY() build documents safely from values, with no hand-written quoting.

Reading values: paths, -> and ->>#

A path starts with $ (the whole document). Use .key for object members and [n] for array elements, counting from 0:

SQL

SELECT name,
       attrs->'$.brand'       AS brand_json,
       attrs->>'$.brand'      AS brand,
       attrs->>'$.ports[0]'   AS first_port,
       attrs->>'$.dims.w'     AS width
FROM products;

Output

+------------+------------+-------+------------+-------+
| name       | brand_json | brand | first_port | width |
+------------+------------+-------+------------+-------+
| Laptop Pro | "Acme"     | Acme  | usb-c      | 31    |
| Phone X    | "Zeta"     | Zeta  | usb-c      | NULL  |
| Desk Lamp  | "Lumo"     | Lumo  | NULL       | NULL  |
+------------+------------+-------+------------+-------+

col->'$.path' is shorthand for JSON_EXTRACT(col, '$.path') and returns a JSON value, so strings keep their quotes.
col->>'$.path' also unquotes the result. It's what you want most of the time.
Missing paths return NULL.

Filter and sort on JSON values like any expression:

SQL

SELECT name, attrs->>'$.ram_gb' AS ram
FROM products
WHERE attrs->>'$.ram_gb' >= 8
ORDER BY CAST(attrs->>'$.ram_gb' AS UNSIGNED) DESC;

Output

+------------+------+
| name       | ram  |
+------------+------+
| Laptop Pro | 16   |
| Phone X    | 8    |
+------------+------+

Values from ->> are strings. CAST them when you need numeric sorting or arithmetic, otherwise '16' < '8' as text.

Searching inside documents#

SQL

SELECT name FROM products WHERE JSON_CONTAINS(attrs->'$.ports', '"hdmi"');
SELECT name FROM products WHERE 'usb-c' MEMBER OF (attrs->'$.ports');
SELECT name, JSON_LENGTH(attrs->'$.ports') AS n_ports, JSON_KEYS(attrs) AS keys_
FROM products WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.colours', '$.watts');

Output

+------------+
| name       |
+------------+
| Laptop Pro |
+------------+
+------------+
| name       |
+------------+
| Laptop Pro |
| Phone X    |
+------------+
+-----------+---------+-----------------------------------------+
| name      | n_ports | keys_                                   |
+-----------+---------+-----------------------------------------+
| Phone X   |       1 | ["brand", "ports", "ram_gb", "colours"] |
| Desk Lamp |       0 | ["brand", "ports", "watts"]             |
+-----------+---------+-----------------------------------------+

Function	Purpose
`JSON_CONTAINS(doc, val[, path])`	does the document contain this JSON value?
`val MEMBER OF (array)`	is the value an element of the array? (8.0.17+)
`JSON_CONTAINS_PATH(doc, 'one'/'all', path, ...)`	do the paths exist?
`JSON_SEARCH(doc, 'one', 'str')`	the path where a string occurs
`JSON_LENGTH`, `JSON_KEYS`, `JSON_TYPE`, `JSON_VALID`	inspect structure

Modifying JSON#

Update parts of a document without rewriting it by hand:

SQL

UPDATE products
SET attrs = JSON_SET(attrs, '$.ram_gb', 32, '$.warranty_years', 2)
WHERE name = 'Laptop Pro';

UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.ports', 'thunderbolt') WHERE name = 'Laptop Pro';
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.dims') WHERE name = 'Laptop Pro';

SELECT attrs FROM products WHERE name = 'Laptop Pro';

Output

+-------------------------------------------------------------------------------------------------+
| attrs                                                                                           |
+-------------------------------------------------------------------------------------------------+
| {"brand": "Acme", "ports": ["usb-c", "hdmi", "thunderbolt"], "ram_gb": 32, "warranty_years": 2} |
+-------------------------------------------------------------------------------------------------+

JSON_SET inserts or replaces, JSON_INSERT only adds missing keys, and JSON_REPLACE only changes existing ones.
JSON_ARRAY_APPEND, JSON_ARRAY_INSERT and JSON_REMOVE edit arrays and remove members.
JSON_MERGE_PATCH(a, b) merges two objects, RFC 7396 style, with b winning on conflicts.

MySQL can apply small JSON_SET/JSON_REPLACE/JSON_REMOVE changes in place, which keeps updates and binary logs small.

JSON_TABLE: JSON to rows#

JSON_TABLE (MySQL 8.0+) turns JSON into a relational table that you can join, filter and aggregate. How many products have each port type?

SQL

SELECT jt.port, COUNT(*) AS products
FROM products p,
     JSON_TABLE(p.attrs, '$.ports[*]' COLUMNS (port VARCHAR(20) PATH '$')) AS jt
GROUP BY jt.port
ORDER BY products DESC, jt.port;

Output

+-------------+----------+
| port        | products |
+-------------+----------+
| usb-c       |        2 |
| hdmi        |        1 |
| thunderbolt |        1 |
+-------------+----------+

It's also perfect for importing an API payload stored as one big document:

SQL

SET @payload = '[{"id": 1, "city": "Pune", "temp": 31.5}, {"id": 2, "city": "Delhi", "temp": 36.0}]';
SELECT * FROM JSON_TABLE(@payload, '$[*]' COLUMNS (
    id   INT          PATH '$.id',
    city VARCHAR(20)  PATH '$.city',
    temp DECIMAL(4,1) PATH '$.temp'
)) AS w;

Output

+------+-------+------+
| id   | city  | temp |
+------+-------+------+
|    1 | Pune  | 31.5 |
|    2 | Delhi | 36.0 |
+------+-------+------+

Rows to JSON#

Going the other way is handy for APIs:

SQL

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) AS products_json
FROM products WHERE id <= 2;

Output

+-----------------------------------------------------------------+
| products_json                                                   |
+-----------------------------------------------------------------+
| [{"id": 1, "name": "Laptop Pro"}, {"id": 2, "name": "Phone X"}] |
+-----------------------------------------------------------------+

JSON_OBJECTAGG(key, value) builds an object from rows instead. JSON_PRETTY(doc) formats a document for humans.

Indexing JSON#

A JSON column can't be indexed directly. Extract the value you search on into a generated column and index that:

SQL

ALTER TABLE products
    ADD COLUMN brand VARCHAR(30) AS (attrs->>'$.brand') VIRTUAL,
    ADD INDEX idx_brand (brand);

EXPLAIN SELECT name FROM products WHERE brand = 'Zeta'\G

Output

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: products
   partitions: NULL
         type: ref
possible_keys: idx_brand
          key: idx_brand
      key_len: 123
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

A VIRTUAL column is computed on the fly and takes no table space, while its index stores the values. MySQL 8 can also use the index when you write the original expression (WHERE attrs->>'$.brand' = 'Zeta'), as long as it matches the generated column's definition exactly.

For arrays, MySQL 8.0.17+ supports multi-valued indexes, which speed up MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS:

SQL

ALTER TABLE products ADD INDEX idx_ports ((CAST(attrs->'$.ports' AS CHAR(20) ARRAY)));

When to use JSON, and when not to#

✅ Good uses:

attributes that genuinely vary per row (product specs by category)
storing external payloads as received (webhooks, API responses)
user settings, feature flags and other "bag of options" data

❌ Poor uses:

data you join on, constrain or aggregate constantly. Use real columns, foreign keys and CHECKs.
avoiding schema design. JSON gives up types, NOT NULL and referential integrity, and every query must know the document's shape.

A common hybrid: real columns for the core, frequently-queried fields, plus one JSON column for the long tail of optional attributes. You can add CHECK (JSON_SCHEMA_VALID('{...schema...}', attrs)) (8.0.17+) to enforce a document shape.

What's next#

JSON helps with flexible data. For searching large amounts of text by words and relevance, MySQL has full-text search. That's next.

Creating and inserting JSON#

Reading values: paths, -> and ->>#

Searching inside documents#

Modifying JSON#

JSON_TABLE: JSON to rows#

Rows to JSON#

Indexing JSON#

When to use JSON, and when not to#

What's next#

Quick quiz