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.

JSON_EXTRACT(data, "$.Gear") in ("Automatic","Manuel") this code not working — skyverxnica
– skyverxnica, Commented Dec 6, 2020 at 19:09
You need to describe your goal in detail before we can help you figure out where you're going wrong. — Ro Achterberg
– Ro Achterberg, Commented Dec 6, 2020 at 19:18
Of course sorry my english, i have json data from mysql column, when i select checkbox "Automatic" and "Manual" i want listing all this records — skyverxnica
– skyverxnica, Commented Dec 6, 2020 at 19:43

miken32 · Accepted Answer · 2020-12-06 19:44:42Z

11

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

answered Dec 6, 2020 at 19:44

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

skyverxnica Over a year ago

Thank you!! I'm really happy right now, its working! really thank you again!

miken32 Over a year ago

Happy to help, welcome to Stack Overflow.

Jorhel Reyes Over a year ago

This not work from array json ["2010","2014", ...] :(

W. Zhong Over a year ago

@JorhelReyes get the same issue, you found something else?

GMB · Accepted Answer · 2020-12-06 20:09:45Z

4

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.

answered Dec 6, 2020 at 20:09

GMB

224k25 gold badges103 silver badges151 bronze badges

2 Comments

miken32 Over a year ago

Nice one, I knew about the -> operator but not ->>

kentuckyss Over a year ago

Warning: this method with operators "->" and "->>" not work in MariaDB 10.7. It's only work with MySQL at this moment.

Collectives™ on Stack Overflow

Mysql extract json data and search multiple values

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related