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.

Table Of Content

  1. Sample data
  2. Select a JSON field
  3. Remove double quotes from selection result
  4. Use dot notation in the selection path
  5. Use the selected field as a condition
  6. The end
Sample data

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

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:

This will output

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:

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:

This will output:

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:

The end

We hope you find this tutorial helpful!