Mysql json in where clause

Starting with version 5.7.8, MySQL supports JSON columns. This gives the advantage of storing and querying unstructured data. Here's how you can query a JSON column in MySQL:

-- Getting the params.name string value from events table
SELECT params->>'$.name'
FROM events;

-- Getting rows where the browser.name is Chrome
-- This also shows the difference of using -> vs ->>
-- Using -> will cause strings to be enclosed in quotes
SELECT browser->>'$.name', browser->'$.name'
FROM events
WHERE browser->>'$.name' = 'Chrome';

-- Give me the first index of a JSON array
SELECT properties->>'$.my_array[0]'
FROM events;

-- Going deeper to get the X resolution only
SELECT properties->'$.resolution.x'
FROM events;

I have a json column in MySQL which contains a list of items for eg.

"style": {"style1", "style2", "style3", "style4", "style5"}

I want to search all the rows which contain the styles I specify through an array like IN clause like WHERE style IN(["style1", "style3"])

So I want to get all the rows which will match the string items from array exactly like how the WHERE IN() clause works in MySQL.

Is there any workaround or easy way to get this working?

Please help.. Thanks

• 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

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"

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!