MySQL JSON find multiple values

Question

I have a table and in one column I have weekdays as JSON data. I'm trying a query to get results if it has one or more than one day that matches the user selection.

My Table:

CREATE TABLE test (id SERIAL, label VARCHAR(16), data VARCHAR(80));

INSERT INTO test VALUES
    (null, 'test 1', '["Monday", "Tuesday", "Friday"]'),
    (null, 'test 2', '["Thursday", "Friday"]'),
    (null, 'test 3', '["Sunday", "Saturday", "Monday"]');

My Query:

SELECT * FROM test
WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$[*]')) IN ('Monday', 'Tuesday');

The query was supposed to give me test 1 and test 2 as result.

Here is the DB Fiddle.

flyingfox · Accepted Answer · 2021-07-26 10:07:59Z

1

You can use OR to do it

SELECT * FROM test
where JSON_CONTAINS(data,'"Monday"')=1 or JSON_CONTAINS(data,'"Tuesday"')

Working demo

If you do not want to use OR,then you can try json-overlaps like below:

   SELECT * FROM test WHERE 
      JSON_OVERLAPS(data,'["Monday","Tuesday"]')=1

But be careful that it only support starting with MySQL 8.0.17

edited Jul 26, 2021 at 10:07

answered Jul 26, 2021 at 9:32

flyingfox

13.5k4 gold badges30 silver badges43 bronze badges

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

8 Comments

Omer Over a year ago

These days will be entered by the user. This way I need to add OR dynamically in the query. Any other option?

flyingfox Over a year ago

@Omer you can use json-overlaps to do it

Omer Over a year ago

json-overlaps only display one result. SELECT * FROM test WHERE JSON_CONTAINS(data,'["Monday","Tuesday"]')=1

Omer Over a year ago

its not working on DB Fiddle - db-fiddle.com/f/fC48mHEM1nuLUZCTP8CLfs/45

flyingfox Over a year ago

@Omer see the last part of my answer,But be careful that it only support starting with MySQL 8.0.17

|

Collectives™ on Stack Overflow

MySQL JSON find multiple values

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