Using JSON_CONTAINS in MySQL query gets not result

Question

I want a query to return all the fields for all the entries in the table minto_regsix_session that include a value of 2 in the 'day' field. Following is a screenshot from phpMyAdmin of the table including the day field which contains a JSON array.

This is the mysql query I'm using:

SELECT * 
FROM `minto_regsix_session` 
WHERE JSON_CONTAINS(`day`, '2') = 1

The query returns an empty result. I would expect it to return the data from lines 2, 4, and 5. Why doesn't this work?

Akina · Accepted Answer · 2020-05-18 11:41:14Z

2

SELECT * 
FROM `minto_regsix_session` 
WHERE JSON_CONTAINS(`day`, '"2"');

answered May 18, 2020 at 11:41

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

8 Comments

user3765165 Over a year ago

Thank you so much. It's so obvious when I see it but it never occurred to me at the time. I tried double quotes and single quotes and no quotes but I'm new to JSON and it didn't occur to me that I need to include the double quotes inside the single quotes. Much appreciated.

Akina Over a year ago

@user3765165 As documentation claims both arguments must be "valid JSON document". '2' is NOT valid JSON...

Curious Developer Over a year ago

how to search for multiple values here?

Akina Over a year ago

@CuriousDeveloper Depends on the task. It can be "at least one is present" or "none is absent".

Curious Developer Over a year ago

@Akina I have similar table as above in question. I want to search the value in any array for example if input is 1,2 then all row which contain both 1 & 2 or any of them.

|

Collectives™ on Stack Overflow

Using JSON_CONTAINS in MySQL query gets not result

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related