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
CodePudding user response：
You could search in the JSON if the value exists with JSON_CONTAINS
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.