JSON_EXTRACT
Extracts a value from a JSON document by a path.
MySQL 8.1
JSON_EXTRACT(json_doc, path[, ...])Parameters
- json_docJSON
- JSON document or a string with JSON
- pathVARCHAR
- Path to the value: $ is the whole document, $.key is a key, $[0] is an array element
Return value
A JSON value; NULL if the path is not in the document or an argument is NULL; an array of found values for several paths
Examples
Nested array element and a missing key
MySQL 8.1
SELECT JSON_EXTRACT('{"a": {"b": [10, 20]}}', '$.a.b[1]') AS second_item,
JSON_EXTRACT('{"a": 1}', '$.x') AS missing_key;Promo code from event metadata
MySQL 8.1
SELECT event_id,
JSON_EXTRACT(metadata, '$.promo_code') AS promo_json,
metadata->>'$.promo_code' AS promo
FROM events
WHERE JSON_EXTRACT(metadata, '$.promo_code') IS NOT NULL
LIMIT 5;JSON null and SQL NULL
MySQL 8.1
SELECT event_id,
metadata,
JSON_EXTRACT(metadata, '$.error_msg') IS NULL AS is_sql_null,
metadata->>'$.error_msg' AS error_msg
FROM events
WHERE event_id IN (1, 2);Details
The result is a JSON value: strings keep their quotes, "SUMMER20". The short form for a column is column->'$.key', and column->>'$.key' also removes the quotes, like JSON_UNQUOTE. These operators work only with a column, not with a literal.
A missing key gives NULL, and a key with the value null gives JSON null, which is not equal to SQL NULL: IS NULL is false for it, and ->> returns the string null. A string with invalid JSON causes an error. In PostgreSQL, the -> and ->> operators with the jsonb type are used for this, and the path is written differently.