MySQL JSON array to used in "IN" clause?

Question

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;

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 instead. — Kamil Gosciminski
– Kamil Gosciminski, Commented Sep 10, 2016 at 15:42

aero · Accepted Answer · 2016-09-10 16:04:26Z

6

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;

edited Sep 10, 2016 at 16:04

aero

1,7341 gold badge21 silver badges34 bronze badges

answered Sep 10, 2016 at 15:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

4 Comments

Kamil Gosciminski Over a year ago

Gordon, wouldn't json_contains(...) = 1 be neater and follow logic for 0/1/null return value to avoid confusion?

Gordon Linoff Over a year ago

@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 Over a year ago

@aero . . . I'm surprised it doesn't do that conversion automatically.

aero Over a year ago

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

Collectives™ on Stack Overflow

MySQL JSON array to used in "IN" clause?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related