How to Use JSON_EXTRACT in MySQL: Syntax and Examples

Use JSON_EXTRACT(json_doc, path) in MySQL to get data from a JSON document by specifying the JSON path. It returns the value at the given path inside the JSON string stored in a column or variable.

Syntax

The JSON_EXTRACT function takes two arguments: the JSON document and the path expression. The path uses $ to represent the root, and you can navigate keys or array indexes using dot notation or brackets.

• json_doc: The JSON string or column containing JSON data.
• path: The path to the value you want, starting with $.

sql

JSON_EXTRACT(json_doc, path)

Example

This example shows how to extract the city from a JSON column named info in a table users. The JSON stores an address object with a city key.

sql

CREATE TABLE users (id INT, info JSON);

INSERT INTO users VALUES
(1, '{"name": "Alice", "address": {"city": "New York", "zip": "10001"}}'),
(2, '{"name": "Bob", "address": {"city": "Los Angeles", "zip": "90001"}}');

SELECT id, JSON_EXTRACT(info, '$.address.city') AS city FROM users;

Output

id | city
---|---------
1  | "New York"
2  | "Los Angeles"

Common Pitfalls

Common mistakes include:

• Forgetting to use $ at the start of the path.
• Using single quotes inside the JSON path incorrectly.
• Expecting JSON_EXTRACT to return plain text instead of JSON-formatted strings.

To get plain text, use JSON_UNQUOTE(JSON_EXTRACT(...)).

sql

SELECT JSON_EXTRACT('{"a":1}', 'a'); -- Wrong, missing $
SELECT JSON_EXTRACT('{"a":1}', '$.a'); -- Correct

SELECT JSON_EXTRACT('{"a":"text"}', '$.a'); -- Returns '"text"'
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"a":"text"}', '$.a')); -- Returns 'text'

Quick Reference

Function	Description	Example Usage
JSON_EXTRACT(json_doc, path)	Extracts JSON value at path	JSON_EXTRACT(info, '$.address.city')
JSON_UNQUOTE(json_val)	Removes quotes from JSON string	JSON_UNQUOTE(JSON_EXTRACT(info, '$.address.city'))
$	Root of JSON document	'$.key' or '$.array[0]'

Key Takeaways

Always start the JSON path with $ to indicate the root.

Use JSON_EXTRACT to get JSON values; use JSON_UNQUOTE to get plain text.

JSON paths use dot notation for keys and brackets for array indexes.

JSON_EXTRACT returns JSON-formatted strings, not plain text by default.

Check your JSON path syntax carefully to avoid errors.