aero aero - 2 months ago 16
JSON Question

MySQL JSON array to used in "IN" clause?

Can I use a JSON array as the parameter for an

IN
clause with MySQL (version 5.7 and greater, as that that supports JSON)?

Below is the code that I can't quite get working...

DROP TABLE IF EXISTS json_table;
CREATE TEMPORARY TABLE json_table(
id INT,
data JSON
);

INSERT INTO json_table (id, data) VALUES (1, "[1,2,3,4,19,20]");

SELECT jt.id, jt.data, REPLACE(REPLACE(jt.data, '[', ''), ']', ''), jt.data->"$[4]"
FROM json_table jt;

SELECT stuff.name
FROM table_stuff stuff
#JOIN json_table jt ON stuff.id IN (1,2,3,4,19,20);
JOIN json_table jt ON stuff.id IN (REPLACE(REPLACE(jt.data, '[', ''), ']', ''));

DROP TABLE IF EXISTS json_table;


Thanks for any help.

Answer

I would expect a JSON function to work, rather than IN. Something like:

SELECT stuff.name
FROM table_stuff stuff JOIN
     json_table jt
     ON JSON_CONTAINS(jt.data, CONVERT(stuff.id, char)) = 1;