Is there any performant way to run a JSON_CONTAINS query in mysql 5.7 on multiple values

Question

I have an array of objects inside a mysql json column, and I'd like to query all rows where one of these objects contains a member within a list of values.

continuing from this question

SELECT * 
FROM   sometable 
WHERE  (   JSON_CONTAINS(json, JSON_OBJECT('id', '1'), '$') 
        OR JSON_CONTAINS(json, JSON_OBJECT('id', '2'), '$') 
        OR JSON_CONTAINS(json, JSON_OBJECT('id', '3'), '$') 
       )

is 3 times as slow as doing three queries. Is there a more performant approach?

Answer 1

You can gain some time by directly targeting property as comparing objects is way more longer because Mysql have to parse both :

SELECT * FROM sometable
WHERE  (   JSON_CONTAINS(jsondata, '1', '$.id') 
        OR JSON_CONTAINS(jsondata, '2', '$.id') 
        OR JSON_CONTAINS(jsondata, '3', '$.id') 
       );

You can also use a more concise way with IN operator and JSON_EXTRACT shortcut :

SELECT * FROM sometable WHERE jsondata->"$.id" IN (1,2,3)

I've made a quick fiddle to illustrate and it seems to be twice as fast on average (db-fiddle is not always consistent in processing time)

Anyway, searching a non indexed column in Mysql will always be a pain. If you can, you may consider using a generated column from your json that can be indexed.

I've made an other fiddle you can check.