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.
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:
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"
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
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
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;
We hope you find this tutorial helpful!