MySQL JSON: Store, Query, and Update JSON Columns

Create a table with a JSON column

Declaring a JSON column uses the JSON keyword as the column type in CREATE TABLE. MySQL validates the document on every INSERT and UPDATE, rejecting invalid JSON with error 3140 before the row is written. This guarantees that only well-formed documents reach your application — a meaningful advantage over a plain TEXT column. The JSON column has a maximum size of 1 GB, matching the LONGBLOB limit. In MySQL 5.7, you cannot set a non-NULL DEFAULT value on a JSON column; MySQL 8.0 lifts that restriction and allows expression defaults such as DEFAULT (JSON_OBJECT()). Use validate your JSON before insert to catch issues early.

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

-- MySQL 8.0: expression default
-- meta JSON DEFAULT (JSON_OBJECT())

INSERT INTO products (name, meta) VALUES
  ('Widget A', '{"price": 9.99, "category": "tools", "tags": ["sale", "new"]}'),
  ('Widget B', '{"price": 24.99, "category": "electronics", "tags": ["premium"]}');

-- Verify JSON validity before inserting
SELECT JSON_VALID('{"price": 9.99}');  -- 1 (valid)
SELECT JSON_VALID('{bad json}');        -- 0 (invalid)

The column stores data in an optimized internal binary format — not plain text — so individual path reads are faster than parsing a raw TEXT column. You can insert NULL (absent value) or the JSON literal 'null' (present null value); these are distinct. Add NOT NULL to enforce mandatory metadata on every row.

Query JSON fields: -> and ->> operators

MySQL provides two shorthand operators for reading JSON paths. The -> operator returns the value as a JSON type (quoted strings, nested objects, arrays). The ->> operator returns the value as an unquoted SQL string, equivalent to JSON_UNQUOTE(JSON_EXTRACT()). Path expressions always start with $ (the document root): $.name reads a top-level key, $.address.city reads a nested key, and $.tags[0] reads the first array element. The table below compares all four equivalent forms.

-- Read a top-level key as a JSON value
SELECT name, meta -> '$.price' AS price FROM products;

-- Filter using ->> for string comparison
SELECT name FROM products
WHERE meta ->> '$.category' = 'electronics';

-- Access first element of an array
SELECT meta -> '$.tags' -> '$[0]' AS first_tag FROM products;

-- Nested path shorthand
SELECT meta -> '$.address' -> '$.city' AS city FROM products;

Use ->> for WHERE clause string comparisons — -> returns a JSON string that includes surrounding quotes, causing comparisons against plain SQL strings to fail silently. For numeric comparisons, cast explicitly: CAST(meta ->> '$.price' AS DECIMAL(10,2)) > 10.

JSON_EXTRACT() and path expressions

JSON_EXTRACT(doc, path) is the canonical function behind the -> operator — they are strictly equivalent. JSON_UNQUOTE(JSON_EXTRACT(doc, path)) is equivalent to ->>. Path expressions follow a consistent grammar: $ is the root of the document; .key selects an object field; [n] selects the nth array element (zero-indexed); [*] selects all array elements; and **.key performs a recursive descent search for any field named key at any nesting level. MySQL also accepts multiple paths in a single call, returning a JSON array of results.

-- Equivalent: -> and JSON_EXTRACT
SELECT meta -> '$.price'              FROM products;
SELECT JSON_EXTRACT(meta, '$.price')  FROM products;

-- Wildcard: all array elements
SELECT meta -> '$.tags[*]' FROM products;

-- Recursive descent: find 'name' anywhere in the document
SELECT JSON_EXTRACT(meta, '$**.name') FROM products;

-- Multiple paths at once (returns JSON array)
SELECT JSON_EXTRACT(meta, '$.price', '$.category') FROM products;

-- Check for path existence
SELECT name FROM products
WHERE JSON_EXTRACT(meta, '$.discount') IS NOT NULL;

Wildcard paths such as $.tags[*] return a JSON array of all matched values — useful for checking membership or counting elements. To test whether a specific value exists in a JSON array, combine JSON_CONTAINS(): WHERE JSON_CONTAINS(meta -> '$.tags', '"sale"'). Note that the search value must itself be valid JSON — string values must be double-quoted within the argument.

Update JSON: JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_REMOVE()

MySQL provides four functions for modifying JSON values. They all return a new JSON document — MySQL does not support in-place mutation — so you must assign the result back to the column inside an UPDATE statement. The functions differ only in how they handle paths that already exist or are missing, as summarized below.

-- JSON_SET: upsert a key
UPDATE products
SET meta = JSON_SET(meta, '$.price', 19.99)
WHERE id = 1;

-- JSON_INSERT: add only if key is absent
UPDATE products
SET meta = JSON_INSERT(meta, '$.discount', 0.1)
WHERE id = 1;

-- JSON_REPLACE: update only if key exists
UPDATE products
SET meta = JSON_REPLACE(meta, '$.price', 14.99)
WHERE id = 1;

-- JSON_REMOVE: delete a key
UPDATE products
SET meta = JSON_REMOVE(meta, '$.discount')
WHERE id = 1;

-- Nested update
UPDATE products
SET meta = JSON_SET(meta, '$.address.city', 'Austin')
WHERE id = 1;

-- Append to array
UPDATE products
SET meta = JSON_ARRAY_APPEND(meta, '$.tags', 'clearance')
WHERE id = 1;

You can chain multiple path-value pairs in a single JSON_SET() call: JSON_SET(meta, '$.price', 9.99, '$.stock', 100). For bulk updates across many rows, wrap UPDATE in a transaction and process in batches to avoid long-running table locks.

JSON_MERGE_PATCH() and JSON_MERGE_PRESERVE()

MySQL provides two functions for merging JSON objects. JSON_MERGE_PATCH() implements RFC 7396 merge patch semantics: keys from the right-hand document overwrite matching keys in the left-hand document, and keys with a null value in the patch are removed from the result. This makes it ideal for partial updates where you only want to specify the fields that changed. JSON_MERGE_PRESERVE() (formerly JSON_MERGE()) uses a different strategy: when both documents have the same key, the values are combined into a JSON array rather than one overwriting the other — no data is discarded.

-- JSON_MERGE_PATCH: RFC 7396 — right side overwrites, null removes key
SELECT JSON_MERGE_PATCH(
  '{"price": 9.99, "color": "red", "stock": 5}',
  '{"price": 14.99, "color": null}'
);
-- Result: {"price": 14.99, "stock": 5}
-- "color" removed (null in patch), "price" overwritten, "stock" kept

-- JSON_MERGE_PRESERVE: conflicting keys merged into arrays
SELECT JSON_MERGE_PRESERVE(
  '{"tags": ["sale"], "price": 9.99}',
  '{"tags": ["new"],  "price": 14.99}'
);
-- Result: {"tags": ["sale", "new"], "price": [9.99, 14.99]}

-- Practical: apply a partial update patch from application layer
UPDATE products
SET meta = JSON_MERGE_PATCH(meta, '{"price": 19.99, "on_sale": true}')
WHERE id = 1;

JSON_MERGE_PATCH() is usually the right choice for API-style partial updates because its behavior matches the HTTP PATCH convention. Use JSON_MERGE_PRESERVE() when you need to accumulate values rather than replace them — for example, merging tag lists from multiple sources.

Index JSON columns

MySQL does not allow creating an index directly on a JSON column. For queries that filter on a specific JSON path, the most compatible approach (works in 5.7+) is to add a generated column that extracts the path value, then index that column. A virtual generated column adds no storage overhead; a stored column takes space but queries faster because the value is pre-computed on write. In MySQL 8.0.17+, multi-valued indexes enable fast MEMBER OF and JSON_OVERLAPS() queries against JSON arrays without any generated column.

-- 5.7+ approach: generated column + regular index
ALTER TABLE products
  ADD COLUMN price DECIMAL(10,2)
    GENERATED ALWAYS AS (meta->>'$.price') STORED,
  ADD INDEX idx_price (price);

-- Query uses idx_price automatically
SELECT name FROM products WHERE price > 10.00;

-- MySQL 8.0.17+: multi-valued index for JSON arrays
CREATE INDEX idx_tags
  ON products ((CAST(meta->'$.tags' AS CHAR(50) ARRAY)));

-- Queries that use the multi-valued index
SELECT name FROM products
WHERE 'sale' MEMBER OF (meta->'$.tags');

SELECT name FROM products
WHERE JSON_OVERLAPS(meta->'$.tags', '["sale", "new"]');

Without an index, every JSON path query performs a full table scan. On tables with more than a few thousand rows this causes unacceptable query times in production. Always add a generated column index for your most-filtered JSON paths, and use multi-valued indexes for array membership queries on MySQL 8.0.17+. For a comparison with PostgreSQL's GIN indexing approach, see the PostgreSQL JSON and JSONB guide.

JSON_TABLE(): convert JSON to relational rows

JSON_TABLE() is a MySQL 8.0 table-valued function that shreds a JSON document or array into a relational result set with named, typed columns. It appears in the FROM clause and is cross-joined with the source table (or used standalone). The COLUMNS clause maps each JSON path to a column name and SQL type. If a path is absent for a given row, the column returns NULL unless you add a DEFAULT ... ON EMPTY clause. This is the cleanest way to unnest a JSON array into individual rows — far more robust than the numbers-table workaround needed in 5.7. You can also use convert JSON to SQL INSERT statements to populate tables from JSON files directly.

-- Expand $.tags array into rows alongside the product name
SELECT p.name, jt.tag
FROM products p,
JSON_TABLE(
  p.meta,
  '$.tags[*]'
  COLUMNS (
    tag VARCHAR(50) PATH '$'
  )
) AS jt;
-- Returns one row per tag per product

-- Multiple columns from a nested object array
SELECT p.name, jt.size, jt.qty
FROM products p,
JSON_TABLE(
  p.meta,
  '$.variants[*]'
  COLUMNS (
    size VARCHAR(10) PATH '$.size',
    qty  INT         PATH '$.qty' DEFAULT '0' ON EMPTY
  )
) AS jt;

-- Standalone: parse a JSON literal
SELECT jt.tag
FROM JSON_TABLE(
  '["mysql", "json", "database"]',
  '$[*]'
  COLUMNS (tag VARCHAR(50) PATH '$')
) AS jt;

JSON_TABLE() supports FOR ORDINALITY to generate a sequential row number column, which is useful for preserving array order. Supported column types include VARCHAR, INT, DECIMAL, DATETIME, and JSON. For document-oriented storage patterns across multiple databases, also see JSON to MongoDB.

JSON_ARRAYAGG() and JSON_OBJECTAGG()

MySQL 5.7.22+ provides two JSON aggregate functions that build JSON values from grouped rows. JSON_ARRAYAGG(expr) collects all non-NULL expression values within a group into a JSON array — equivalent to GROUP_CONCAT but returning a proper JSON array instead of a comma-separated string. JSON_OBJECTAGG(key, value) collects key-value pairs into a JSON object; if the same key appears more than once in a group, only the last value is kept (behavior is undefined in MySQL docs, but in practice last write wins). Both functions ignore NULL values.

-- Aggregate product names into a JSON array per category
SELECT
  meta ->> '$.category' AS category,
  JSON_ARRAYAGG(name)   AS products
FROM products
GROUP BY meta ->> '$.category';
-- Result: {"category": "electronics", "products": ["Widget B"]}

-- Build a price lookup object: {name: price}
SELECT JSON_OBJECTAGG(name, meta ->> '$.price') AS price_map
FROM products;
-- Result: {"Widget A": "9.99", "Widget B": "24.99"}

-- Combine with JSON_TABLE to re-aggregate unnested data
SELECT
  p.name,
  JSON_ARRAYAGG(jt.tag ORDER BY jt.tag) AS sorted_tags
FROM products p,
JSON_TABLE(p.meta, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) AS jt
GROUP BY p.id, p.name;

JSON_ARRAYAGG() accepts an ORDER BY clause (MySQL 8.0) to control the order of elements in the output array. This is useful when the original array order matters to the consuming application. Use JSON mock data generator to create sample datasets for testing these aggregate queries before running against real data.

Frequently asked questions

How do I create a JSON column in MySQL?

Use JSON as the column type in CREATE TABLE. MySQL automatically validates JSON on every INSERT and UPDATE and rejects invalid documents with error 3140. Example: CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, meta JSON). You cannot set a non-NULL DEFAULT on a JSON column in MySQL 5.7, but MySQL 8.0 supports expression defaults: meta JSON DEFAULT (JSON_OBJECT()). Insert rows with JSON string literals: INSERT INTO products (meta) VALUES ('{"name": "Widget", "price": 9.99}'). The column stores data in an optimized binary format — not plain text — so reads are faster than a TEXT column containing JSON. You can confirm validity before inserting using the JSON_VALID() function: SELECT JSON_VALID('{"a": 1}') returns 1; invalid JSON returns 0.

What is the difference between -> and ->> in MySQL?

The -> operator is shorthand for JSON_EXTRACT() and returns the value as a JSON type. The ->> operator is shorthand for JSON_UNQUOTE(JSON_EXTRACT()) and returns the value as a plain string, with surrounding quotes removed. For example, if meta contains {"price": 9.99, "name": "Widget"}: meta -> '$.name' returns "Widget" (JSON string with quotes); meta ->> '$.name' returns Widget (SQL string without quotes). This distinction matters for comparisons: meta ->> '$.name' = 'Widget' works correctly; meta -> '$.name' = 'Widget' compares a JSON string to an SQL string, which fails. For numeric comparisons, cast the result: CAST(meta ->> '$.price' AS DECIMAL(10,2)) > 10. Both operators were added in MySQL 5.7.9.

How do I update a JSON field in MySQL?

Use JSON_SET() for the most common case: it updates a key if it exists and adds it if it does not. UPDATE products SET meta = JSON_SET(meta, '$.price', 19.99) WHERE id = 1. To add a key only if it does not exist (skip if present), use JSON_INSERT(). To update a key only if it exists (skip if absent), use JSON_REPLACE(). To remove a key, use JSON_REMOVE(): UPDATE products SET meta = JSON_REMOVE(meta, '$.price') WHERE id = 1. To update nested paths: JSON_SET(meta, '$.address.city', 'Austin'). To append to an array: JSON_ARRAY_APPEND(meta, '$.tags', 'new-tag'). These functions always return a new JSON value — assign the result back to the column in an UPDATE statement.

How do I index a JSON column in MySQL?

MySQL does not support indexes directly on JSON columns; you must use a workaround. The most portable approach (5.7+) is a generated column: add a virtual or stored column that extracts the JSON path, then index that column. ALTER TABLE products ADD COLUMN price DECIMAL(10,2) GENERATED ALWAYS AS (meta->>'$.price') STORED, ADD INDEX idx_price (price). Virtual generated columns do not take storage space; stored columns do but are faster to query. MySQL 8.0.17+ adds multi-valued indexes for JSON arrays: CREATE INDEX idx_tags ON products ((CAST(meta->'$.tags' AS CHAR(100) ARRAY))). This enables fast lookups like WHERE 'sale' MEMBER OF (meta->'$.tags'). Without any index, JSON queries perform full table scans — avoid this in production on tables larger than a few thousand rows.

What is JSON_TABLE() in MySQL?

JSON_TABLE() is a table-valued function (MySQL 8.0+) that converts a JSON document or array into a relational result set with typed columns. It is used in the FROM clause alongside a regular table or as a standalone query. Example: SELECT jt.tag FROM JSON_TABLE('[{"tag":"a"}, {"tag":"b"}]', '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')) AS jt returns two rows: a and b. A common use case is unnesting a JSON array stored in a column: SELECT p.name, jt.tag FROM products p, JSON_TABLE(p.meta, '$.tags[*]' COLUMNS (tag VARCHAR(50) PATH '$')) AS jt. The COLUMNS clause maps JSON paths to SQL column names and types. Supported types include VARCHAR, INT, DECIMAL, DATETIME, and JSON. If a path is missing, the column returns NULL unless you specify a DEFAULT ... ON EMPTY clause.

How do I convert a JSON array to rows in MySQL?

In MySQL 8.0+, use JSON_TABLE() in the FROM clause to expand a JSON array into rows (see the JSON_TABLE section above). In MySQL 5.7, there is no built-in function, but you can work around it using a numbers table or a recursive CTE (MySQL 8.0 only) to generate row numbers and JSON_EXTRACT() each element by index:

SELECT JSON_EXTRACT(meta, CONCAT('$.tags[', n.n, ']')) AS tag
FROM products
JOIN (
  SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
) AS n
WHERE JSON_EXTRACT(meta, CONCAT('$.tags[', n.n, ']')) IS NOT NULL;

For production use on 8.0, always prefer JSON_TABLE() over the numbers-table workaround — it handles any array length dynamically and returns properly typed columns without manual casting.