How to Use ->> Operator for JSON in MySQL

In MySQL, the ->> operator extracts a value from a JSON column and returns it as plain text. Use it by specifying the JSON column followed by ->> '$.key' to get the value of the key as a string.

Syntax

The ->> operator extracts a JSON value as text from a JSON column or expression. The syntax is:

• json_column ->> '$.key': Extracts the value of key as plain text.
• The path $.key specifies the key inside the JSON object.

sql

json_column ->> '$.key'

Example

This example shows how to extract the name field from a JSON column called data in a table users. The ->> operator returns the value as text.

sql

CREATE TABLE users (id INT, data JSON);

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

SELECT id, data->> '$.name' AS name_text FROM users;

Output

id | name_text
---|----------
1  | Alice
2  | Bob

Common Pitfalls

Common mistakes when using ->> include:

• Using ->> without quotes around the JSON path, which causes syntax errors.
• Confusing ->> with ->: -> returns JSON type, ->> returns text.
• Trying to extract nested keys without the correct JSON path syntax.

sql

SELECT data->> $.name FROM users; -- Wrong: missing quotes around path

SELECT data-> '$.name' FROM users; -- Returns JSON, not text

SELECT data->> '$.address.city' FROM users; -- Correct for nested keys

Quick Reference

Operator	Description	Returns
->	Extracts JSON value	JSON type
->>	Extracts JSON value as text	String (plain text)

Key Takeaways

Use ->> to extract JSON values as plain text in MySQL.

Always put the JSON path in single quotes, like '$.key'.

-> returns JSON type; ->> returns text.

For nested keys, use dot notation inside the JSON path, e.g., '$.address.city'.

Check your JSON path syntax carefully to avoid errors.