(opens in a new tab)

MySQL JSON Extract: Functions, Paths and Indexes

September 19, 2026 by Chat2DB Team

MySQL has had a native JSON data type since version 5.7.8, and MySQL 8.0 turned it into a genuinely useful tool: path ranges, JSON_TABLE, multi-valued indexes, MEMBER OF, schema validation and in-place partial updates. The feature most people start with is extracting values, which is why "mysql json extract" is such a common search. But extraction is only one part of the picture. To use JSON well you also need to build JSON with JSON_OBJECT, modify it without rewriting whole documents, search inside arrays, and index the fields you filter on.

This guide covers the MySQL JSON functions you will use day to day, using one realistic sample table throughout. Every query shows its expected output so you can run it and compare. Examples target MySQL 8.0 and later; version requirements are called out where a feature is newer.

Sample table: orders with a JSON column

Create a small orders table with a JSON column that holds the order details:

CREATE TABLE orders (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  customer  VARCHAR(50) NOT NULL,
  details   JSON NOT NULL
);
 
INSERT INTO orders (customer, details) VALUES
('alice', '{"status": "shipped", "total": 59.5, "currency": "USD",
            "shipping": {"city": "Berlin", "method": "express"},
            "items": [{"sku": "KB-01", "qty": 1, "price": 49.5},
                      {"sku": "CB-02", "qty": 2, "price": 5}],
            "tags": ["gift", "priority"]}'),
('bob',   '{"status": "pending", "total": 120, "currency": "EUR",
            "shipping": {"city": "Paris", "method": "standard"},
            "items": [{"sku": "MN-27", "qty": 1, "price": 120}],
            "tags": ["b2b"]}'),
('carol', '{"status": "cancelled", "total": 15.5, "currency": "USD",
            "shipping": {"city": "Austin", "method": null},
            "items": [{"sku": "CB-02", "qty": 3, "price": 5},
                      {"sku": "ST-10", "qty": 1, "price": 0.5}],
            "tags": []}');

Note that carol's shipping.method is a JSON null, and her tags array is empty. Both are deliberate: they show up in the pitfalls section.

The JSON data type

A JSON column is not just text with a label on it. When you insert a value, MySQL:

• Validates it. Invalid JSON is rejected with an error such as ERROR 3140 (22032): Invalid JSON text.
• Converts it into an internal binary format that allows looking up a key or array element without parsing the whole document.
• Normalizes it: whitespace is discarded, duplicate keys keep only the last value (in MySQL 8.0), and object keys are stored in sorted order. Do not rely on the key order you inserted.

You can check what was stored with a few helper functions:

SELECT id,
       JSON_TYPE(details)                  AS doc_type,
       JSON_LENGTH(details->'$.items')     AS item_count,
       JSON_KEYS(details->'$.shipping')    AS shipping_keys,
       JSON_VALID('{"a": 1')               AS valid_check
FROM orders;

+----+----------+------------+--------------------+-------------+
| id | doc_type | item_count | shipping_keys      | valid_check |
+----+----------+------------+--------------------+-------------+
|  1 | OBJECT   |          2 | ["city", "method"] |           0 |
|  2 | OBJECT   |          1 | ["city", "method"] |           0 |
|  3 | OBJECT   |          2 | ["city", "method"] |           0 |
+----+----------+------------+--------------------+-------------+

JSON_PRETTY(details) prints a document with indentation, which is handy in a terminal, and JSON_STORAGE_SIZE(details) returns the number of bytes used to store it.

JSON_EXTRACT and path syntax

JSON_EXTRACT(json_doc, path[, path] ...) returns the value at a path, or NULL if the path does not exist. Paths always start with $, which means "the document root".

Keys and nested objects

Use .key to step into an object:

SELECT id,
       JSON_EXTRACT(details, '$.status')        AS status,
       JSON_EXTRACT(details, '$.total')         AS total,
       JSON_EXTRACT(details, '$.shipping.city') AS city
FROM orders;

+----+-------------+-------+----------+
| id | status      | total | city     |
+----+-------------+-------+----------+
|  1 | "shipped"   | 59.5  | "Berlin" |
|  2 | "pending"   | 120   | "Paris"  |
|  3 | "cancelled" | 15.5  | "Austin" |
+----+-------------+-------+----------+

The double quotes are not a display glitch. JSON_EXTRACT returns a JSON value, and a JSON string is quoted. How to get plain text back is covered in the next section.

If a key contains spaces or special characters, quote it inside the path: '$."delivery window"'.

Array elements, ranges and last

Array elements are addressed with zero-based indexes in square brackets:

SELECT id,
       JSON_EXTRACT(details, '$.items[0].sku')     AS first_sku,
       JSON_EXTRACT(details, '$.items[last].sku')  AS last_sku,
       JSON_EXTRACT(details, '$.tags[0]')          AS first_tag
FROM orders;

+----+-----------+----------+-----------+
| id | first_sku | last_sku | first_tag |
+----+-----------+----------+-----------+
|  1 | "KB-01"   | "CB-02"  | "gift"    |
|  2 | "MN-27"   | "MN-27"  | "b2b"     |
|  3 | "CB-02"   | "ST-10"  | NULL      |
+----+-----------+----------+-----------+

Carol's first_tag is NULL because her tags array is empty, so there is no element 0. last always refers to the final element, whatever the array length. You can also select a slice with the M to N range syntax:

SELECT JSON_EXTRACT(details, '$.items[0 to 1].sku') AS first_two_skus
FROM orders
WHERE id = 1;

+--------------------+
| first_two_skus     |
+--------------------+
| ["KB-01", "CB-02"] |
+--------------------+

last and ranges were added in MySQL 8.0.2. Because a range can match several elements, the result is an array.

Wildcards: [*], .* and **

Wildcards match several locations at once. When a path can match more than one value, the result is always wrapped in a JSON array:

• $.items[*].sku matches the sku of every element of items.
• $.shipping.* matches every member value of the shipping object.
• $**.sku matches a sku key at any depth below the root.

SELECT id,
       JSON_EXTRACT(details, '$.items[*].sku') AS skus,
       JSON_EXTRACT(details, '$**.price')      AS prices
FROM orders;

+----+--------------------+-----------+
| id | skus               | prices    |
+----+--------------------+-----------+
|  1 | ["KB-01", "CB-02"] | [49.5, 5] |
|  2 | ["MN-27"]          | [120]     |
|  3 | ["CB-02", "ST-10"] | [5, 0.5]  |
+----+--------------------+-----------+

Note that even bob's single SKU comes back as ["MN-27"], because a wildcard path always produces an array. A path of the form $** alone is not allowed; the double asterisk must be followed by a path leg.

Multiple paths in one call

Passing several paths returns an array with one entry per path that matched:

SELECT JSON_EXTRACT(details, '$.status', '$.total') AS status_and_total
FROM orders
WHERE id = 1;

+--------------------+
| status_and_total   |
+--------------------+
| ["shipped", 59.5]  |
+--------------------+

The -> and ->> operators

Writing JSON_EXTRACT everywhere gets verbose, so MySQL provides two shorthand operators:

• column->'path' is exactly JSON_EXTRACT(column, 'path').
• column->>'path' is JSON_UNQUOTE(JSON_EXTRACT(column, 'path')): it removes the surrounding quotes and unescapes the string, returning a regular SQL string.

SELECT id,
       details->'$.shipping.city'  AS city_json,
       details->>'$.shipping.city' AS city_text,
       JSON_UNQUOTE(JSON_EXTRACT(details, '$.shipping.city')) AS city_long_form
FROM orders;

+----+-----------+-----------+----------------+
| id | city_json | city_text | city_long_form |
+----+-----------+-----------+----------------+
|  1 | "Berlin"  | Berlin    | Berlin         |
|  2 | "Paris"   | Paris     | Paris          |
|  3 | "Austin"  | Austin    | Austin         |
+----+-----------+-----------+----------------+

In practice, use ->> whenever the value leaves the database or is used as text (in CONCAT, GROUP BY labels, joins against VARCHAR columns), and use -> when you want to keep working with a JSON value (passing it to another JSON function, or comparing numbers).

Two restrictions to remember: the left side of -> and ->> must be a column name, not an arbitrary expression, and the path must be a string literal. For anything more dynamic, call JSON_EXTRACT directly.

JSON_VALUE for typed extraction

MySQL 8.0.21 added JSON_VALUE, which extracts a scalar and casts it in one step:

SELECT id,
       JSON_VALUE(details, '$.total' RETURNING DECIMAL(10,2)) AS total
FROM orders;

+----+--------+
| id | total  |
+----+--------+
|  1 |  59.50 |
|  2 | 120.00 |
|  3 |  15.50 |
+----+--------+

It is a cleaner alternative to CAST(details->>'$.total' AS DECIMAL(10,2)).

Filtering and sorting on JSON values

Extracted values work anywhere an expression is allowed:

SELECT id, customer, details->>'$.status' AS status
FROM orders
WHERE details->>'$.currency' = 'USD'
  AND details->'$.total' > 20
ORDER BY details->'$.total' DESC;

+----+----------+---------+
| id | customer | status  |
+----+----------+---------+
|  1 | alice    | shipped |
+----+----------+---------+

Carol also pays in USD, but her total of 15.5 fails the second condition. The comparison details->'$.total' > 20 works numerically because the stored value is a JSON number; see the pitfalls section for what happens when it is not.

Building JSON: JSON_OBJECT, JSON_ARRAY and aggregates

JSON_OBJECT and JSON_ARRAY

JSON_OBJECT(key, value, ...) builds an object from alternating keys and values, and JSON_ARRAY(value, ...) builds an array. SQL NULL becomes JSON null, and SQL strings are properly escaped:

SELECT JSON_OBJECT('id', id,
                   'customer', customer,
                   'city', details->>'$.shipping.city') AS summary
FROM orders
WHERE id = 1;

+--------------------------------------------------+
| summary                                          |
+--------------------------------------------------+
| {"id": 1, "city": "Berlin", "customer": "alice"} |
+--------------------------------------------------+

The key order differs from the order in the call because MySQL normalizes objects. This is harmless for any JSON consumer, but it can surprise people comparing output as strings.

SELECT JSON_ARRAY(1, 'two', NULL, TRUE, JSON_OBJECT('k', 'v')) AS arr;

+------------------------------------+
| arr                                |
+------------------------------------+
| [1, "two", null, true, {"k": "v"}] |
+------------------------------------+

Nesting JSON_OBJECT and JSON_ARRAY calls is the safe way to build JSON in SQL. Concatenating strings to produce JSON breaks as soon as a value contains a quote or a backslash.

JSON_ARRAYAGG and JSON_OBJECTAGG

The aggregate versions collapse a group of rows into one JSON value, which is very convenient for APIs that return nested data:

SELECT details->>'$.currency'    AS currency,
       JSON_ARRAYAGG(customer)   AS customers,
       JSON_OBJECTAGG(customer, details->'$.total') AS totals
FROM orders
GROUP BY details->>'$.currency';

+----------+--------------------+--------------------------------+
| currency | customers          | totals                         |
+----------+--------------------+--------------------------------+
| EUR      | ["bob"]            | {"bob": 120}                   |
| USD      | ["alice", "carol"] | {"alice": 59.5, "carol": 15.5} |
+----------+--------------------+--------------------------------+

MySQL does not accept an ORDER BY inside JSON_ARRAYAGG, so the element order is not guaranteed. If order matters, sort in the application or build the array from an ordered subquery and verify the behavior on your version. Also note that JSON_OBJECTAGG raises an error if a key is NULL, and with duplicate keys only one value survives.

Modifying JSON: JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE

The four modification functions differ only in how they treat paths that already exist and paths that do not. Literal documents make the difference easiest to see:

SELECT JSON_SET    ('{"a": 1}', '$.a', 10, '$.b', 20) AS set_result,
       JSON_INSERT ('{"a": 1}', '$.a', 10, '$.b', 20) AS insert_result,
       JSON_REPLACE('{"a": 1}', '$.a', 10, '$.b', 20) AS replace_result,
       JSON_REMOVE ('{"a": 1, "b": 2}', '$.b')         AS remove_result;

+--------------------+-------------------+----------------+---------------+
| set_result         | insert_result     | replace_result | remove_result |
+--------------------+-------------------+----------------+---------------+
| {"a": 10, "b": 20} | {"a": 1, "b": 20} | {"a": 10}      | {"a": 1}      |
+--------------------+-------------------+----------------+---------------+

• JSON_SET replaces existing values and adds missing ones (an upsert).
• JSON_INSERT only adds missing paths; existing values are left alone.
• JSON_REPLACE only changes existing paths; missing ones are ignored.
• JSON_REMOVE deletes the value at each path.

Applied to the table, a typical status update looks like this:

UPDATE orders
SET details = JSON_SET(details,
                       '$.status', 'delivered',
                       '$.delivered_at', '2026-09-18')
WHERE id = 1;
 
UPDATE orders
SET details = JSON_ARRAY_APPEND(details, '$.tags', 'vip')
WHERE id = 2;
 
SELECT id, details->>'$.status' AS status, details->'$.tags' AS tags
FROM orders
WHERE id IN (1, 2);

+----+-----------+----------------------+
| id | status    | tags                 |
+----+-----------+----------------------+
|  1 | delivered | ["gift", "priority"] |
|  2 | pending   | ["b2b", "vip"]       |
+----+-----------+----------------------+

JSON_ARRAY_APPEND adds to the end of an array, and JSON_ARRAY_INSERT inserts at a given index, such as '$.tags[0]'.

One trap here: passing a string that looks like JSON stores a string, not an object. JSON_SET(details, '$.gift', '{"wrap": true}') stores the text "{\"wrap\": true}". To store a real object, pass JSON_OBJECT('wrap', TRUE) or CAST('{"wrap": true}' AS JSON).

In MySQL 8.0, an UPDATE that assigns JSON_SET, JSON_REPLACE or JSON_REMOVE of a column back to the same column can be performed as a partial, in-place update instead of rewriting the whole document, as long as the new value does not need more space than the old one. With binlog_row_value_options=PARTIAL_JSON, row-based binary logs record only the change as well. That is a good reason to prefer these functions over reading the document into the application and writing it back.

The rest of this guide assumes the original data. If you ran the updates above, recreate the table before continuing.

Searching inside JSON

JSON_CONTAINS and JSON_CONTAINS_PATH

JSON_CONTAINS(target, candidate[, path]) returns 1 if the candidate JSON is contained in the target. The candidate is itself JSON, so a string must be written with its quotes:

-- Orders tagged "gift"
SELECT id, customer
FROM orders
WHERE JSON_CONTAINS(details, '"gift"', '$.tags');
 
-- Orders with at least one line for SKU CB-02
SELECT id, customer
FROM orders
WHERE JSON_CONTAINS(details->'$.items', '{"sku": "CB-02"}');

+----+----------+
| id | customer |
+----+----------+
|  1 | alice    |
+----+----------+

+----+----------+
| id | customer |
+----+----------+
|  1 | alice    |
|  3 | carol    |
+----+----------+

The second query shows a useful property: an object candidate is contained in an array if it is contained in any element, and an object is contained in another object if every key in the candidate matches. The line items have extra keys (qty, price), and they still match.

JSON_CONTAINS_PATH(doc, 'one' or 'all', path, ...) checks whether paths exist, regardless of their values:

SELECT id,
       JSON_CONTAINS_PATH(details, 'one', '$.shipping.method', '$.coupon') AS any_path,
       JSON_CONTAINS_PATH(details, 'all', '$.shipping.method', '$.coupon') AS all_paths
FROM orders;

+----+----------+-----------+
| id | any_path | all_paths |
+----+----------+-----------+
|  1 |        1 |         0 |
|  2 |        1 |         0 |
|  3 |        1 |         0 |
+----+----------+-----------+

Carol's shipping.method is null, but the path exists, so it counts.

MEMBER OF and JSON_OVERLAPS

MySQL 8.0.17 added value MEMBER OF (json_array) and JSON_OVERLAPS(a, b). Both read more naturally than JSON_CONTAINS for array membership, and both can use multi-valued indexes:

SELECT id, customer
FROM orders
WHERE 'priority' MEMBER OF (details->'$.tags');
 
SELECT id, customer
FROM orders
WHERE JSON_OVERLAPS(details->'$.tags', '["b2b", "gift"]');

The first query returns alice; the second returns alice and bob, because each of them has at least one of the listed tags.

JSON_SEARCH

JSON_SEARCH returns the path of a string value, which is useful when you know the value but not where it lives:

SELECT id, JSON_SEARCH(details, 'one', 'CB-02') AS path
FROM orders;

+----+-------------------+
| id | path              |
+----+-------------------+
|  1 | "$.items[1].sku"  |
|  2 | NULL              |
|  3 | "$.items[0].sku"  |
+----+-------------------+

Pass 'all' instead of 'one' to get every matching path. JSON_SEARCH supports % and _ wildcards like LIKE, and it only matches string values.

JSON_TABLE: turning JSON arrays into rows

JSON_TABLE (MySQL 8.0.4 and later) is the most powerful JSON function in MySQL. It maps a JSON document to a relational table that you can join, filter and aggregate with ordinary SQL:

SELECT o.id, o.customer, jt.*
FROM orders AS o,
     JSON_TABLE(
       o.details, '$.items[*]'
       COLUMNS (
         line_no FOR ORDINALITY,
         sku     VARCHAR(20)   PATH '$.sku',
         qty     INT           PATH '$.qty',
         price   DECIMAL(10,2) PATH '$.price'
       )
     ) AS jt;

+----+----------+---------+-------+------+--------+
| id | customer | line_no | sku   | qty  | price  |
+----+----------+---------+-------+------+--------+
|  1 | alice    |       1 | KB-01 |    1 |  49.50 |
|  1 | alice    |       2 | CB-02 |    2 |   5.00 |
|  2 | bob      |       1 | MN-27 |    1 | 120.00 |
|  3 | carol    |       1 | CB-02 |    3 |   5.00 |
|  3 | carol    |       2 | ST-10 |    1 |   0.50 |
+----+----------+---------+-------+------+--------+

Now line-item reporting is plain SQL. Revenue per SKU, excluding cancelled orders:

SELECT jt.sku,
       SUM(jt.qty)            AS units,
       SUM(jt.qty * jt.price) AS revenue
FROM orders AS o,
     JSON_TABLE(o.details, '$.items[*]'
       COLUMNS (sku   VARCHAR(20)   PATH '$.sku',
                qty   INT           PATH '$.qty',
                price DECIMAL(10,2) PATH '$.price')) AS jt
WHERE o.details->>'$.status' <> 'cancelled'
GROUP BY jt.sku
ORDER BY revenue DESC;

+-------+-------+---------+
| sku   | units | revenue |
+-------+-------+---------+
| MN-27 |     1 |  120.00 |
| KB-01 |     1 |   49.50 |
| CB-02 |     2 |   10.00 |
+-------+-------+---------+

Useful JSON_TABLE column options:

• FOR ORDINALITY numbers the rows produced from each document, starting at 1.
• EXISTS PATH '$.gift_wrap' returns 1 or 0 depending on whether the path exists.
• DEFAULT 'n/a' ON EMPTY and NULL ON ERROR control what happens when a value is missing or cannot be converted.
• NESTED PATH '$.subarray[*]' COLUMNS (...) unnests arrays inside arrays.

Rows without any items produce no output with the comma (inner) join. Use LEFT JOIN JSON_TABLE(...) AS jt ON TRUE to keep them.

Indexing JSON columns

A JSON column cannot be indexed directly. Without an index, every WHERE details->>'$.status' = ... reads and evaluates every row. There are three ways to fix that.

Generated columns

Extract the field into a generated column and index that column:

ALTER TABLE orders
  ADD COLUMN status VARCHAR(20)
    GENERATED ALWAYS AS (details->>'$.status') VIRTUAL,
  ADD INDEX idx_status (status);
 
EXPLAIN SELECT id FROM orders WHERE status = 'shipped';

In the EXPLAIN output, key shows idx_status. A VIRTUAL column takes no space in the table row; the value is computed on read and materialized only in the index. Use STORED if you want the value physically saved, for example when it is expensive to compute.

The optimizer can also match an expression in a query to an indexed generated column that uses the same expression, but only if the types and collations line up. The most reliable approach is to query the generated column by name, as above.

Functional indexes

MySQL 8.0.13 and later can index an expression directly, which creates a hidden generated column for you. Because ->> returns a string with the utf8mb4_bin collation, cast it and set the collation explicitly so the index is usable from the natural query:

ALTER TABLE orders
  ADD INDEX idx_city ((CAST(details->>'$.shipping.city' AS CHAR(40)) COLLATE utf8mb4_bin));
 
EXPLAIN SELECT id FROM orders WHERE details->>'$.shipping.city' = 'Berlin';

Without the COLLATE utf8mb4_bin, the cast uses the default collation and the optimizer may not use the index for a query written with ->>. Always confirm with EXPLAIN.

Multi-valued indexes for arrays

Generated columns hold one value per row, which does not work for arrays like tags or all the SKUs in items. MySQL 8.0.17 introduced multi-valued indexes, which store one index entry per array element:

ALTER TABLE orders
  ADD INDEX idx_tags ((CAST(details->'$.tags' AS CHAR(20) ARRAY))),
  ADD INDEX idx_skus ((CAST(details->'$.items[*].sku' AS CHAR(20) ARRAY)));
 
EXPLAIN SELECT id FROM orders WHERE 'gift' MEMBER OF (details->'$.tags');
EXPLAIN SELECT id FROM orders WHERE JSON_CONTAINS(details->'$.items[*].sku', '"CB-02"');

Multi-valued indexes are used by MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS when the expression in the query matches the indexed expression. They have restrictions worth knowing: only one multi-valued key part per index, they cannot be covering indexes, and the cast target type must suit the element values (use UNSIGNED ARRAY for integer IDs, for example).

On a three-row table, EXPLAIN may prefer a full scan anyway. Test index usage on realistic data volumes.

Pitfalls: quotes, types and NULL

Quoted strings

-> and JSON_EXTRACT return JSON, so strings come back quoted. That causes two classic bugs:

-- Returns no rows: the JSON value is compared to a string that includes quotes
SELECT id FROM orders WHERE JSON_EXTRACT(details, '$.status') = '"shipped"';
 
-- Joins against VARCHAR columns or CONCAT output include the quotes
SELECT CONCAT('City: ', details->'$.shipping.city') FROM orders WHERE id = 1;
-- City: "Berlin"

Compare with the plain value (= 'shipped') or use ->>, and use ->> whenever you produce text.

Also note that JSON string comparisons are case-sensitive, because JSON strings use a binary collation. 'Shipped' does not match "shipped". Normalize the case when writing, or compare with LOWER(details->>'$.status').

Numbers stored as strings

If one application writes "total": 120 and another writes "total": "120", you have two different JSON types in the same column. Comparisons between different JSON types are decided by type precedence, not by numeric value, so details->'$.total' > 100 can give surprising answers for string-typed rows. Keep types consistent on write, and when you cannot, cast explicitly:

SELECT id
FROM orders
WHERE CAST(details->>'$.total' AS DECIMAL(10,2)) > 100;

A CHECK constraint with JSON_SCHEMA_VALID (MySQL 8.0.17 and later) can enforce types at the database level:

ALTER TABLE orders ADD CONSTRAINT chk_details CHECK (
  JSON_SCHEMA_VALID('{
    "type": "object",
    "required": ["status", "total", "items"],
    "properties": {
      "status": {"type": "string"},
      "total":  {"type": "number"},
      "items":  {"type": "array"}
    }
  }', details)
);

SQL NULL versus JSON null

A missing path and a JSON null are different things:

SELECT id,
       details->'$.shipping.method'              AS method_json,
       details->>'$.shipping.method'             AS method_text,
       details->'$.shipping.method' IS NULL      AS is_sql_null,
       JSON_TYPE(details->'$.shipping.method')   AS json_type,
       details->'$.coupon' IS NULL               AS coupon_missing
FROM orders
WHERE id = 3;

+----+-------------+-------------+-------------+-----------+----------------+
| id | method_json | method_text | is_sql_null | json_type | coupon_missing |
+----+-------------+-------------+-------------+-----------+----------------+
|  3 | null        | null        |           0 | NULL      |              1 |
+----+-------------+-------------+-------------+-----------+----------------+

Carol's method exists and holds JSON null, so IS NULL is false, and ->> returns the four-character string null, not SQL NULL. The missing coupon path, on the other hand, returns SQL NULL. To treat both the same way, test JSON_TYPE(...) = 'NULL' together with IS NULL, for example WHERE details->'$.shipping.method' IS NULL OR JSON_TYPE(details->'$.shipping.method') = 'NULL'.

Performance tips

• Index what you filter on. Generated columns, functional indexes and multi-valued indexes turn full scans into index lookups. Nothing else on this list matters as much.
• Promote hot fields to real columns. If every query filters on status and customer_id, those belong in regular columns with proper types and constraints; keep JSON for the variable, sparse part of the data.
• Select only what you need. Returning a whole document to extract one field in the application wastes network and parsing time. Extract in SQL with ->> or JSON_VALUE.
• Update in place. Use JSON_SET, JSON_REPLACE and JSON_REMOVE on the same column so MySQL can apply partial updates instead of rewriting the document.
• Watch document size. Large documents make every read and write more expensive, and a single value cannot exceed max_allowed_packet. JSON_STORAGE_SIZE helps find outliers.
• Use JSON_TABLE for reporting over arrays rather than looping in the application, and check the plan with EXPLAIN.

When you are exploring an unfamiliar JSON column, a SQL client that renders documents readably saves time. Chat2DB (opens in a new tab) shows JSON values in a formatted viewer and can help draft JSON_TABLE or ->> queries from a plain-language description, which you can then refine and verify with EXPLAIN.

MariaDB differences

MariaDB supports many of the same function names, but the implementation is different, so do not assume portability:

• In MariaDB, JSON is an alias for LONGTEXT with a JSON_VALID check constraint. Documents are stored as text, not in a binary format, so key order and whitespace are preserved as written.
• The -> and ->> operators are MySQL-specific; in MariaDB, use JSON_EXTRACT, JSON_VALUE and JSON_UNQUOTE instead.
• JSON_TABLE is available from MariaDB 10.6. MEMBER OF and multi-valued indexes are MySQL features; in MariaDB, index JSON fields through virtual columns.
• Output formatting and edge cases (such as duplicate keys) can differ. Check the MariaDB documentation for your version before migrating queries.

FAQ

How do I extract a value from JSON in MySQL?

Use JSON_EXTRACT(column, '$.path') or the shorthand column->'$.path'. To get the value without JSON quotes, use column->>'$.path', which equals JSON_UNQUOTE(JSON_EXTRACT(...)).

Why does JSON_EXTRACT return values with double quotes?

It returns a JSON value, and JSON strings are quoted. Use ->>, JSON_UNQUOTE or JSON_VALUE to get a plain SQL string.

How do I create a JSON object in a MySQL query?

Use JSON_OBJECT('key1', value1, 'key2', value2). For arrays use JSON_ARRAY, and to aggregate rows into JSON use JSON_ARRAYAGG or JSON_OBJECTAGG.

Can I index a JSON column in MySQL?

Not directly. Index a generated column that extracts the field, create a functional index on the expression (MySQL 8.0.13 and later), or use a multi-valued index for arrays (MySQL 8.0.17 and later).

How do I query elements of a JSON array?

Use MEMBER OF or JSON_CONTAINS to test membership, $.arr[*] paths to extract all elements, and JSON_TABLE to turn the array into rows you can join and aggregate.

What is the difference between JSON_SET, JSON_INSERT and JSON_REPLACE?

JSON_SET updates existing paths and adds missing ones, JSON_INSERT only adds missing paths, and JSON_REPLACE only updates existing paths.