Extracting JSON Values in MySQL

MySQL provides several built-in methods to extract and manipulate JSON data. These techniques vary based on whether you need raw JSON values, unquoted strings, or support for dynamic paths.

Basic Field Access

The -> operator retrieves a value from a JSON document while preserving its JSON type:

SELECT json_col->'$.property' FROM my_table;

This is functionally identical to using JSON_EXTRACT(json_col, '$.property'). It's ideal when the extracted value will be further processed as JSON.

For direct string output without surrounding quotes, use the ->> operator:

SELECT json_col->>'$.property' FROM my_table;

This automatically applies JSON_UNQUOTE, making it suitable for comparisons or display purposes.

Handling Nested Structures and Arrays

To access elements within nested objects or arrays, specify the full path:

SELECT json_col->'$.items[0].name' FROM my_table;

Array indices start at 0. The same logic applies with JSON_EXTRACT:

SELECT JSON_EXTRACT(json_col, '$.items[0].name') FROM my_table;

To extract all values from an array field (e.g., all names), use the wildcard $[*]:

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_col, '$[*].name')) FROM my_table;

Type Handling and Unquoitng

When using -> or JSON_EXTRACT, string results include JSON-style double quotes. To remove them manually:

SELECT JSON_UNQUOTE(json_col->'$.label') FROM my_table;

This yields the same result as json_col->>'$.label'.

Method Comparison

Method	Return Type	Quotes Preserved?	Min Version	Notes
->	JSON	Yes	5.7+	Shorthand for JSON_EXTRACT
->>	TEXT	No	5.7+	Equivalent to JSON_UNQUOTE(JSON_EXTRACT(...))
JSON_EXTRACT	JSON	Yes	All versions	Only method supporting dynamic paths via expressions like CONCAT('$.', @var)

Practical Examples

-- Nested extraction (simplified syntax)
SELECT json_col->'$.metadata.id' AS record_id FROM logs;

-- Equivalent using function nesting (less efficient)
SELECT JSON_EXTRACT(JSON_EXTRACT(json_col, '$.metadata'), '$.id') AS record_id FROM logs;

-- Direct string output for filtering
SELECT * FROM users WHERE profile->>'$.email' = 'user@example.com';

Key Considerations

• If a JSON path does not exist, the result is NULL—no error is thrown.
• Prefer -> over nested JSON_EXTRACT calls for readability and performance.
• Use JSON_EXTRACT when the path must be constructed dynamically at runtime.