Since MySQL 5.7, the JSON data type lets you store documents in a column, validated and in an efficient binary format, with functions to query and modify them. It’s ideal for flexible attributes, settings, API payloads and metadata, without giving up the relational model for everything else.

Create and insert

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

INSERT INTO products (name, attrs) VALUES
  ('VPS Plan A', '{"cpu": 2, "ram_gb": 4, "regions": ["us", "hk"], "features": {"ddos": false}}'),
  ('VPS Plan B', JSON_OBJECT('cpu', 4, 'ram_gb', 8, 'regions', JSON_ARRAY('jp')));

Invalid JSON is rejected with an error, unlike a JSON string in a TEXT column.

Read values

SELECT name,
       attrs->'$.cpu'            AS cpu,        -- JSON value
       attrs->>'$.regions[0]'    AS region,     -- unquoted text
       attrs->>'$.features.ddos' AS ddos
FROM products;
Operator / function Returns
col->'$.path' JSON value (strings keep their quotes)
col->>'$.path' Unquoted scalar (same as JSON_UNQUOTE(JSON_EXTRACT(…)))
JSON_EXTRACT(col, '$.a', '$.b') One or more values
JSON_KEYS(col) Array of top-level keys
JSON_LENGTH(col, '$.regions') Number of elements

Filter rows

SELECT name FROM products WHERE attrs->>'$.cpu' >= 4;
SELECT name FROM products WHERE JSON_CONTAINS(attrs->'$.regions', '"hk"');
SELECT name FROM products WHERE 'jp' MEMBER OF (attrs->'$.regions');   -- MySQL 8.0.17+
SELECT name FROM products WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.features.ddos');

Note that ->> returns a string, so cast it for numeric comparisons when it matters: CAST(attrs->>'$.cpu' AS UNSIGNED).

Update JSON

Change parts of a document without rewriting it in the application:

UPDATE products SET attrs = JSON_SET(attrs, '$.ram_gb', 16) WHERE id = 1;          -- insert or replace
UPDATE products SET attrs = JSON_INSERT(attrs, '$.ipv4', 1) WHERE id = 1;           -- only if missing
UPDATE products SET attrs = JSON_REPLACE(attrs, '$.cpu', 8) WHERE id = 1;           -- only if present
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.features.ddos') WHERE id = 1;
UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.regions', 'sg') WHERE id = 2;

MySQL 8 can perform partial in-place updates for JSON_SET, JSON_REPLACE and JSON_REMOVE, which reduces write and replication overhead for large documents.

Index JSON data

You can’t index a JSON column directly. Two options:

Generated columns (index one scalar value):

ALTER TABLE products
  ADD COLUMN cpu INT GENERATED ALWAYS AS (attrs->>'$.cpu') VIRTUAL,
  ADD INDEX idx_cpu (cpu);

SELECT name FROM products WHERE cpu >= 4;     -- uses idx_cpu

Functional indexes (MySQL 8.0.13+), without an explicit column:

CREATE INDEX idx_cpu2 ON products ((CAST(attrs->>'$.cpu' AS UNSIGNED)));

Multi-valued indexes (MySQL 8.0.17+), for arrays:

CREATE INDEX idx_regions ON products ((CAST(attrs->'$.regions' AS CHAR(8) ARRAY)));
SELECT name FROM products WHERE 'hk' MEMBER OF (attrs->'$.regions');   -- uses the index

Check with EXPLAIN that the index is used. See MySQL EXPLAIN.

Turn JSON into rows

JSON_TABLE (MySQL 8) turns arrays into a relational result, to join and aggregate:

SELECT p.name, r.region
FROM products p,
     JSON_TABLE(p.attrs, '$.regions[*]' COLUMNS (region VARCHAR(8) PATH '$')) AS r;

Build JSON from rows with JSON_OBJECT and JSON_ARRAYAGG, which is handy for API responses:

SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'name', name)) FROM products;

Design advice

  • Use real columns for data you filter, join or sort by often, and for anything needing foreign keys or constraints. Use JSON for genuinely variable attributes.
  • Keep documents reasonably small. Huge documents make every read and update expensive.
  • Validate shape in the application, or with CHECK constraints and JSON_SCHEMA_VALID() (MySQL 8.0.17+).
  • MariaDB’s JSON is an alias for LONGTEXT with validation. Most functions exist, but ->>, multi-valued indexes and some optimizations differ. See MariaDB vs MySQL.

For heavy JSON querying, PostgreSQL’s jsonb with GIN indexes is more powerful. See PostgreSQL JSONB.

Quick reference

Task SQL
Extract value col->>'$.key'
Array contains 'x' MEMBER OF (col->'$.arr')
Set / remove JSON_SET, JSON_REMOVE
Index scalar Generated column + index, or functional index
Index array CAST(col->'$.arr' AS CHAR(n) ARRAY)
JSON to rows JSON_TABLE

Related: MySQL vs PostgreSQL and MySQL partitioning.

  • MySQL
  • Database
  • JSON