mysqlwhere-injson-arrayagg

MYSQL - using array in WHERE IN clause

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

Solution

  • 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.