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

Question

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?

score 1 · Accepted Answer · answered Dec 09 '22 at 05:32

1

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.

answered Dec 09 '22 at 05:32

Bill Karwin

538,548
86
673
828

1

thx Bill, I really enjoy your books and strongly recommend them to other people (and don't tell me this exact example is in them lol); I appreciate you answering this question - I never would've figure this out myself. It looks as if you don't even need the brackets in the `where json_contains(meta, '5', '$.grant_ids');` – timpone Dec 09 '22 at 17:41
1

I'm working on a book now with a chapter about storing JSON in an SQL database. The summary is: _don't._ – Bill Karwin Dec 09 '22 at 17:48
Sounds interesting, it can be very treacherous for storing and I only use for denormalization / querying. Have found beneficial for that use case but have to be very careful and could understand a no-JSON in SQL db rule. – timpone Dec 09 '22 at 18:24
1

Using JSON to format result sets is mostly okay, but people get into trouble when they store JSON and then try to search within the fields of a JSON document as if they are normal columns. Basically if you see a JSON column referenced in the WHERE clause (or really any clause other than the select-list), it's a red flag. You might also like my presentation [How to Use JSON in MySQL Wrong](https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong). – Bill Karwin Dec 09 '22 at 18:42
Thx for link. I have more experience with PostgreSQL which does have index support for JSONB. Have also used for data in microservices (but again tricky) when a network call would be much slower. Thx – timpone Dec 09 '22 at 19:43
MySQL 8.0 also has support for indexes on JSON documents, but not GIN indexes like PostgreSQL. – Bill Karwin Dec 09 '22 at 20:12

score 0 · Answer 2 · edited Dec 13 '22 at 18:12

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)

edited Dec 13 '22 at 18:12

Jishan Shaikh

1,572
2
13
31

answered Dec 09 '22 at 02:21

Neagu hu

1

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

2 Answers2