4

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;
dreftymac
  • 31,404
  • 26
  • 119
  • 182
aero
  • 1,654
  • 1
  • 21
  • 31
  • 1
    You can't. This would require dynamically build (string) and executed query. Like you have written it, it is just a single `TEXT` value. Use [these functions](https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html) instead. – Kamil Gosciminski Sep 10 '16 at 15:42
  • @KamilG. Thanks Kamil – aero Sep 10 '16 at 15:44

1 Answers1

5

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;
aero
  • 1,654
  • 1
  • 21
  • 31
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 2
    Gordon, wouldn't `json_contains(...) = 1` be neater and follow logic for 0/1/null return value to avoid confusion? – Kamil Gosciminski Sep 10 '16 at 15:48
  • @KamilG. . . . Yes. I was thinking of `find_in_set()` when I first wrote it, but `json_contains()` only returns 0 and 1 ( and possibly `NULL`). – Gordon Linoff Sep 10 '16 at 15:50
  • 1
    @aero . . . I'm surprised it doesn't do that conversion automatically. – Gordon Linoff Sep 10 '16 at 16:09
  • @GordonLinoff... Yeah, I was interested to see it needed that too. It might add some overhead to the query, but for my specific need, I think it will be minimal. – aero Sep 10 '16 at 16:11