MYSQL - using array in WHERE IN clause

Question

Having a json array column in a table e.g. ["A1", "A2", "B1"]. I want to reference that array in WHERE IN clause. I could not evaluate the json array to ... WHERE tbl2.refID IN ("A1", "A2", "B1").

SET @ref = replace(replace('["A1", "A2", "BI"]','[', ''), ']', ''); SELECT @ref; returns "A1", "A2", "B1" as I want it but not working in ... WHERE tbl2.refID IN (@ref)

So how can I evaluate array to be used as "WHERE IN" values?

Table 1

id	array of ids	other cols
1	["A1", "A2", "B1"]

Table 2

id	refID	col 3
1	A1	[ ]
2	A2	[ ]

Using elements of table1.col2 I want to select and group col3 from table2.

Wished I could illustrate it better!

I have tried to evaluate the array column passed to WHERE IN () but not returning any value. The evaluation is broken somehow.

WHERE tbl2.refID IN (replace(replace('["A1", "A2", "B1"]','[', ''), ']', '')); not evaluating

find_In_set() maybe? stackoverflow.com/questions/45767566/…. — xQbert
– xQbert, Commented Mar 29, 2022 at 22:31

Joel · Accepted Answer · 2022-03-29 23:01:50Z

2

You could search in the JSON if the value exists with JSON_CONTAINS

Example

Beware, JSON_CONTAINS needs a valid JSON on the two parameters, so JSON_CONTAINS('["A1"]', 'A1') we be invalid as A1 is not a valid JSON string representation.

For the where, you can simply do

WHERE JSON_CONTAINS('["A1", "A2", "BI"]', JSON_QUOTE(tbl2.refID))

It will add quotes around strings and test it against your array.

answered Mar 29, 2022 at 23:01

Joel

1,2491 gold badge7 silver badges18 bronze badges

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

1 Comment

Dare Over a year ago

Thank you @Joel, works as expected, even in expanded query where I need it

Collectives™ on Stack Overflow

MYSQL - using array in WHERE IN clause

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related