TailTemplate Build stunning websites faster with our pre-designed Tailwind CSS templates

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!