Question 21838 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search