Published: 2026-04-21

MySQL JSON Columns: A Practical Guide for SQL Developers

MySQL has shipped a native JSON type — a binary-encoded form of JSON — since 5.7 in 2015, and 8.0 added multi-valued indexes, JSON_TABLE, and JSON_VALUE. As of May 2026, MySQL 8.4 LTS and the 9.x innovation releases are current; every feature in this guide is available in 8.0.21+. Here's how to use it well.

Quick Answers

The MySQL JSON Type at a Glance

The JSON column type validates documents on insert and stores them in an optimised binary format that supports lookup-by-key without re-parsing. A few facts that matter in practice: See the MySQL JSON Data Type reference for the authoritative specification.

• Validation is on by default. Inserting '{"foo":}' into a JSON column fails — the type rejects malformed documents.
• Document size is bounded by max_allowed_packet (default 64 MB in MySQL 8.0; can be raised up to the 1 GB hard limit).
• Key order is not preserved. MySQL sorts keys for fast lookup. JSON_PRETTY output may differ from what you inserted.
• Comparisons are JSON-aware. CAST('{"a":1,"b":2}' AS JSON) = CAST('{"b":2,"a":1}' AS JSON) evaluates to true — equality compares document structure, not raw text. (Compare raw strings without casting and you get plain string equality.)

Querying JSON: The Operators You Will Use

MySQL's JSON access splits into two camps: operator shortcuts and explicit functions. The full set of JSON search functions is in the MySQL manual.

-- Extract a JSON value
SELECT data->'$.email' FROM customers;

-- Extract and unquote (the version you almost always want)
SELECT data->>'$.email' FROM customers;

-- Comparison in WHERE
SELECT * FROM customers WHERE data->>'$.email' = '[email protected]';

-- JSON_VALUE with explicit type (8.0.21+, faster, indexable)
SELECT * FROM customers
WHERE JSON_VALUE(data, '$.age' RETURNING UNSIGNED) > 18;

-- Path existence
SELECT * FROM customers
WHERE JSON_CONTAINS_PATH(data, 'one', '$.phone');

-- Containment
SELECT * FROM orders
WHERE JSON_CONTAINS(items, '{"sku":"ABC-123"}');

Rule of thumb: if you're building a WHERE clause, prefer JSON_VALUE with a RETURNING type clause. It returns a typed SQL scalar, which pairs cleanly with functional indexes (8.0.13+) and avoids the JSON-vs-string comparison quirks of -> / ->>.

Indexing JSON in MySQL

MySQL doesn't have a generic GIN-style index over an entire document like PostgreSQL. Instead, you index specific paths through one of three patterns. Both patterns are documented in CREATE INDEX — look for the multi-valued index and functional-index sections.

1. Virtual generated column + index

The classic pattern. The generated column has zero storage overhead because it's computed on read:

ALTER TABLE customers
  ADD COLUMN email VARCHAR(255)
    GENERATED ALWAYS AS (JSON_VALUE(data, '$.email')) VIRTUAL,
  ADD INDEX idx_customers_email (email);

2. Multi-valued index on an array path

Since 8.0.17 (2019), MySQL can index every element inside a JSON array, so MEMBER OF and JSON_CONTAINS can use the index:

CREATE INDEX idx_orders_skus
  ON orders ((CAST(items->'$[*].sku' AS CHAR(64) ARRAY)));

-- Now this hits the index:
SELECT * FROM orders WHERE 'ABC-123' MEMBER OF (items->'$[*].sku');

3. Functional index (8.0.13+)

Index a function expression directly, no generated column required:

CREATE INDEX idx_customers_email
  ON customers ((JSON_VALUE(data, '$.email' RETURNING CHAR(255))));

JSON_TABLE: Turn Documents Into Rows

One of the most underused features in 8.0. JSON_TABLE projects a JSON array into a regular SQL rowset, which means you can join, group, and aggregate against array elements:

SELECT o.id, sku, qty
FROM orders o,
     JSON_TABLE(
       o.items,
       '$[*]' COLUMNS (
         sku VARCHAR(64) PATH '$.sku',
         qty INT         PATH '$.qty'
       )
     ) AS j
WHERE qty > 1;

This was the missing piece that made MySQL viable for genuinely document-shaped data. Before 8.0, you had to extract arrays in application code or stored procedures.

MySQL JSON vs MongoDB

MongoDB and MySQL JSON occupy overlapping but distinct sweet spots. The trade-offs:

Pick MySQL JSON when you want one database for everything, your data has any relational shape, or your team already runs MySQL well.

Pick MongoDB when you have genuinely document-shaped data at very high write-scale that needs horizontal sharding out of the box, the aggregation pipeline is core to your domain logic, or your team is more comfortable in BSON than SQL.

The often-overlooked point: MySQL JSON columns combine with relational tables. You don't have to pick one paradigm for the whole database. A single MySQL instance can run a normalised orders table, a JSON events firehose, and a relational users table side by side.

A Realistic Schema Pattern

Same advice as in PostgreSQL: keep query / join / FK columns relational, push the optional payload into JSON.

CREATE TABLE events (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT NOT NULL,
  event_type  VARCHAR(64) NOT NULL,
  occurred_at DATETIME(6) NOT NULL,
  properties  JSON NOT NULL,
  event_name  VARCHAR(128)
    GENERATED ALWAYS AS (JSON_VALUE(properties, '$.name')) VIRTUAL,
  INDEX idx_events_user_time (user_id, occurred_at DESC),
  INDEX idx_events_name (event_name)
);

Working with MySQL JSON in a SQL IDE

JSON columns rendered as a single line of escaped text are nearly unreadable. Most SQL IDEs leave you copy-pasting documents into a separate viewer just to see the shape.

Jam SQL Studio renders MySQL JSON columns as collapsible trees inline in the grid. The filter chip exposes a json operator with sub-operators (has property, property =, property contains) that compile down to JSON_EXTRACT, JSON_CONTAINS_PATH, and JSON_VALUE. A Peek popover scans the loaded rows to show every JSON path that exists, with the prevalence of each — the schema-discovery tool MongoDB Compass made famous, applied directly to MySQL JSON columns.

Last verified against the official MySQL 8.0 reference on 2026-05-13.