• How to search JSON data in MySQL
  • Sample data
  • Select a JSON field
    • Remove double quotes from selection result
  • Use dot notation in the selection path
  • Use the selected field as a condition
  • The end

#How to search JSON data in MySQL

As of MySQL 5.7.8, MySQL supports a native JSON data type. In this tutorial, we will learn how to search for JSON data in MySQL.

#Sample data

For demonstration purpose, suppose we have created a database table with the following data:

+-------------------------------+
| data                          |
+-------------------------------+
| {"id": "4", "name": "Betty","mobile_no.":"921213"}  |
+-------------------------------+

The column "data" is a JSON type. It currently holds a user JSON data with three fields:

• id
• name
• mobile_no.

#Select a JSON field

To select a particular field from JSON, we can use the JSON_EXTRACT function. For example, to select the name field:

SELECT JSON_EXTRACT(data,'$.name') AS  name FROM users;

This will output

"Betty"

#Remove double quotes from selection result

You may have noticed double quotes in the previous sample. To remove the double quotes from the selection result, we can use JSON_UNQUOTE function:

SELECT JSON_UNQUOTE(JSON_EXTRACT(data,'$.name')) AS  name FROM users;

This will output

Betty

#Use dot notation in the selection path

In our sample data, it contains a JSON field called "mobile_no.", pay attention to the ending dot notation. You cannot use the dot notation directly in the selection field, because it will be treated as a denominator.

To use dot notation in the selection path, we can wrap it with double quotes:

SELECT JSON_UNQUOTE(JSON_EXTRACT(data,'$."mobile_no."')) AS  mobile FROM users;

This will output:

921213

#Use the selected field as a condition

It is common to use the selected JSON field as a condition. To do this, we can use the HAVING clause:

SELECT JSON_UNQUOTE(JSON_EXTRACT(data,'$.id')) AS  id FROM users HAVING id = 1;

#The end

We hope you find this tutorial helpful!