MySQL JSON Path Expressions: $.key, [n], [*], **

A path expression tells a JSON function where to look inside a document. It always starts with $, the whole document.

query.sql

SQL

CREATE TABLE orders (id INT PRIMARY KEY, doc JSON NOT NULL);
INSERT INTO orders VALUES (1, '{
    "customer": {"name": "Maya Shah", "address": {"city": "Pune", "pin": "411001"}},
    "items": [
        {"sku": "NB-01", "qty": 2, "price": 80},
        {"sku": "GP-02", "qty": 10, "price": 15.5},
        {"sku": "BP-03", "qty": 1, "price": 999}
    ],
    "tags": ["gift", "express"],
    "notes": {"internal note": "call before delivery"}
}');

The building blocks

Path	Meaning
`$`	The whole document
`$.key`	A key of an object
`$.a.b`	A nested key
`$[n]`	The n-th element of an array (starting at 0)
`$.items[1]`	The second element of `items`
`$.items[last]`	The last element
`$.items[last-1]`	The one before the last
`$.items[1 to 2]`	A range of elements (MySQL 8.0.2 and later)
`$.items[*]`	All elements of an array
`$.items[*].sku`	The `sku` of every element
`$.*`	All values of an object
`$**.sku`	Every `sku` anywhere in the document (any depth)
`$."internal note"`	A key with a space or special characters, in double quotes

query.sql

SQL

SELECT doc->>'$.customer.name' AS customer,
       doc->>'$.customer.address.city' AS city,
       doc->>'$.items[0].sku' AS first_sku,
       doc->>'$.items[last].sku' AS last_sku,
       doc->>'$.tags[1]' AS second_tag,
       doc->>'$.notes."internal note"' AS note
FROM orders;

Output

OUTPUT

+-----------+------+-----------+----------+------------+----------------------+
| customer  | city | first_sku | last_sku | second_tag | note                 |
+-----------+------+-----------+----------+------------+----------------------+
| Maya Shah | Pune | NB-01     | BP-03    | express    | call before delivery |
+-----------+------+-----------+----------+------------+----------------------+
1 row in set

Wildcards return arrays

A path that can match several values (with * or ) returns them in a JSON array**:

query.sql

SQL

SELECT JSON_EXTRACT(doc, '$.items[*].sku') AS all_skus,
       JSON_EXTRACT(doc, '$.items[*].qty') AS all_quantities,
       JSON_EXTRACT(doc, '$.items[1 to 2].sku') AS skus_in_range,
       JSON_EXTRACT(doc, '$**.pin') AS pin_anywhere
FROM orders;

Output

OUTPUT

+-----------------------------+----------------+--------------------+--------------+
| all_skus                    | all_quantities | skus_in_range      | pin_anywhere |
+-----------------------------+----------------+--------------------+--------------+
| ["NB-01", "GP-02", "BP-03"] | [2, 10, 1]     | ["GP-02", "BP-03"] | ["411001"]   |
+-----------------------------+----------------+--------------------+--------------+
1 row in set

->> does not unquote a whole array (it only unquotes one string value), so for lists use JSON_EXTRACT and read it as JSON, or turn it into rows with JSON_TABLE (JSON_TABLE).

Several paths at once

JSON_EXTRACT accepts several paths and combines the results in one array:

query.sql

SQL

SELECT JSON_EXTRACT(doc, '$.customer.name', '$.tags[0]') AS two_values FROM orders;

Output

OUTPUT

+-----------------------+
| two_values            |
+-----------------------+
| ["Maya Shah", "gift"] |
+-----------------------+
1 row in set

Missing paths and errors

query.sql

SQL

SELECT doc->'$.customer.phone' AS missing_key, doc->'$.items[9]' AS missing_index FROM orders;

Output

OUTPUT

+-------------+---------------+
| missing_key | missing_index |
+-------------+---------------+
| NULL        | NULL          |
+-------------+---------------+
1 row in set

A path that finds nothing returns SQL NULL. This is useful: you do not need to test that the key exists. But a syntax error in the path is an error:

query.sql

SQL

SELECT JSON_EXTRACT(doc, '$.customer..name') FROM orders;

Output

OUTPUT

ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 11.

Keys that need quotes

A key with a space, a hyphen, a dot or a digit at the start must be quoted with double quotes inside the path (and the path itself in single quotes):

query.sql

SQL

SELECT JSON_EXTRACT('{"first-name": "Asha", "2nd": 2, "a.b": 3}', '$."first-name"', '$."2nd"', '$."a.b"') AS values_found;

Output

OUTPUT

+----------------+
| values_found   |
+----------------+
| ["Asha", 2, 3] |
+----------------+
1 row in set

Path expressions and the functions that use them

Function	Use
`JSON_EXTRACT(doc, path, ...)` / `->`	Read
`JSON_CONTAINS_PATH(doc, ‘one’	‘all’, path, …)`	Does a path exist?
`JSON_SET`, `JSON_INSERT`, `JSON_REPLACE`, `JSON_REMOVE`	Change (Modifying JSON)
`JSON_SEARCH(doc, ‘one’	‘all’, text)`	Find the path of a string value
`JSON_TABLE(doc, path COLUMNS (...))`	Turn arrays into rows
`JSON_VALUE(doc, path RETURNING type)`	Read with a type (8.0.21 and later)

query.sql

SQL

SELECT JSON_CONTAINS_PATH(doc, 'one', '$.customer.phone') AS has_phone,
       JSON_CONTAINS_PATH(doc, 'all', '$.customer.name', '$.items') AS has_name_and_items,
       JSON_SEARCH(doc, 'one', 'express') AS path_of_express,
       JSON_VALUE(doc, '$.items[1].price' RETURNING DECIMAL(8,2)) AS second_price
FROM orders;

Output

OUTPUT

+-----------+--------------------+-----------------+--------------+
| has_phone | has_name_and_items | path_of_express | second_price |
+-----------+--------------------+-----------------+--------------+
|         0 |                  1 | "$.tags[1]"     |        15.50 |
+-----------+--------------------+-----------------+--------------+
1 row in set

JSON_VALUE ... RETURNING is the clean way to read a value with an SQL type (number, date, text), so that comparisons and sums work without casts (JSON Functions).

Remember

Array positions start at 0.
$ is required.
Use ->> for a single scalar value as text, and JSON_EXTRACT for arrays and objects.
The path syntax is case-sensitive: $.Color is not $.color.

lightbulb

Test paths on a literal first

SELECT JSON_EXTRACT('{"a": [1, {"b": 2}]}', '$.a[1].b'); is the quickest way to check a path before you put it into a query.