How to Query JSON Data in MySQL: Syntax and Examples

In MySQL, you can query JSON data using functions like JSON_EXTRACT() or the shorthand operator -> to access values inside JSON columns. Use JSON_EXTRACT(json_column, '$.key') or json_column->'$.key' to retrieve specific data from JSON objects stored in your tables.

Syntax

MySQL provides functions and operators to extract data from JSON columns:

• JSON_EXTRACT(json_doc, path): Extracts data from JSON document using a path expression.
• json_column->'$.key': Shorthand operator to extract a value at the specified path.
• json_column->>'$.key': Extracts the value as plain text (unquoted).

The path uses $ to represent the root of the JSON document and dot notation to access keys.

sql

SELECT JSON_EXTRACT(json_column, '$.key') AS value FROM table_name;

-- or using shorthand operator
SELECT json_column->'$.key' AS value FROM table_name;

Example

This example shows how to create a table with a JSON column, insert data, and query a value inside the JSON document.

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  info JSON
);

INSERT INTO users (id, info) VALUES
(1, '{"name": "Alice", "age": 30}'),
(2, '{"name": "Bob", "age": 25}');

SELECT id, info->>'$.name' AS name, JSON_EXTRACT(info, '$.age') AS age FROM users;

Output

id | name  | age
---|-------|-----
1  | Alice | 30
2  | Bob   | 25

Common Pitfalls

Common mistakes when querying JSON data in MySQL include:

• Using -> instead of ->> when you want plain text instead of JSON-formatted strings.
• Incorrect JSON path syntax, such as missing $ or wrong key names.
• Not storing valid JSON in the column, which causes errors when querying.

sql

/* Wrong: returns JSON string with quotes */
SELECT info->'$.name' AS name FROM users;

/* Right: returns plain text without quotes */
SELECT info->>'$.name' AS name FROM users;

Quick Reference

Function/Operator	Description	Example
JSON_EXTRACT(json_doc, path)	Extracts JSON value at path	JSON_EXTRACT(info, '$.name')
->	Shorthand to extract JSON value (returns JSON)	info->'$.age'
->>	Shorthand to extract JSON value as text	info->>'$.name'
JSON_UNQUOTE(json_val)	Removes quotes from JSON string	JSON_UNQUOTE(info->'$.name')

Key Takeaways

Use JSON_EXTRACT() or -> operator to query JSON data in MySQL.

Use ->> operator to get JSON values as plain text without quotes.

Always use correct JSON path syntax starting with $.

Ensure JSON data is valid before querying to avoid errors.

MySQL JSON functions help you treat JSON columns like normal data.