querying a JSON array of integers for the presence of a value in MySQL 5.7-CodePudding

I have a MySQL 5.7 table that looks like this:

id	meta
1	null
2	{"grant_ids": [5, 7]}

I'd like to query for rows that have the presence of the value of 5 in meta-> grant_ids. I tried

select *   from content.banners where meta->>"$.grant_ids" in (5);

but not working. I don't think in is supported but any ideas how to achieve this query?

CodePudding user response：

You can write it like this, using the json function of sql.

select * from content.banners b where JSON_EXTRACT(b.meta,"$.grant_ids") in (5)

CodePudding user response：

mysql> create table table_that_looks_like_this ( 
  id int primary key, meta json);

mysql> insert into table_that_looks_like_this values
    -> (1, null),
    -> (2, '{"grant_ids": [5, 7]}'),
    -> (3, '{"grant_ids": [4, 8]}'),
    -> (4, '{"no_grant_ids": [5, 7]}');

mysql> select * from table_that_looks_like_this 
 where json_contains(meta, '[5]', '$.grant_ids');
 ---- ----------------------- 
| id | meta                  |
 ---- ----------------------- 
|  2 | {"grant_ids": [5, 7]} |
 ---- -----------------------

DBFiddle

JSON_EXTRACT() will only return the JSON array at the path you give, and an array isn't the scalar value 5. Unquoting a JSON array with the ->> operator has no effect; an "unquoted" JSON array is the exact same array.