Mysql extract json data and search multiple values

Question

Hi I have car listing website and I want to filter multiple values from a JSON column data

My database table looks like this:

id | label    | data
---|----------|---------------------------------
 1 | "test 1" | {"Year":"2014","Gear":"Auto"}
 2 | "test 2" | {"Year":"2010","Gear":"Manual"}
 3 | "test 3" | {"Year":"2009","Gear":"None"}

For example I want to filter by Gear, either "Auto" or "Manual":

SELECT * FROM test WHERE 
JSON_EXTRACT(data, "$.Year") in (2010,2014) AND
JSON_EXTRACT(data, "$.Gear") in ("Auto","Manual")

The query returns zero results when I add the filter for Gear, but the filter for Year works fine.

user_1255289 · Answer

JSON_EXTRACT returns a JSON string, which includes quotation marks. You can either include this in your search:

SELECT * FROM test
WHERE JSON_EXTRACT(data, "$.Year") in (2010,2014)
    AND JSON_EXTRACT(data, '$.Gear') IN ('"Manual"','"Auto"');

Or use the JSON_UNQUOTE function:

SELECT * FROM test
WHERE JSON_EXTRACT(data, "$.Year") in (2010,2014)
    AND JSON_UNQUOTE(JSON_EXTRACT(data, '$.Gear')) IN ('Manual','Auto');

See fiddle here: https://www.db-fiddle.com/f/fC48mHEM1nuLUZCTP8CLfs/0

user_10676716 · Answer

I would recommend json operator ->>, which combines JSON_EXTRACT() and JSON_UNQUOTE():

select * 
from test
where data ->> '$.Year' in (2010, 2014) and data ->> '$.Gear' in ('Manual', 'Auto');

->> was added in MySQL 5.7.13.

Mysql extract json data and search multiple values

2 Answers