Search a MySQL column with a JSON string for a specific value

Question

I currently have a MySQL table that contains a column to store category ids. These ids are stored in a JSON string. I am looking for the most efficient method to query these JSON string for a specific id.

For example:

Table: posts

Field: cats

Here are some example values for the JSON string in the cats column:

---

[111, 123, 456]

[123, 345, 999]

[555, 777, 888]

Let's say I want to query for all rows that contain the id: "123" within the JSON string. I know I can accomplish this using a series of LIKE comparisons but I'm sure there is a more efficient way to query the JSON strings. Any other ideas would be much appreciated.

Thanks!

Answer 1

cats must contains: {"ids": [2,4,6,7]}

WHERE JSON_CONTAINS(posts.cats->"$.ids", '[2]');

Answer 2

There is a strong discussion of storing JSON values (Storing Data in MySQL as JSON) but you could use native mysql functions for this:

Mysql official doc: https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

In the

Functions That Search JSON Values

there is :

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');

or with REGEX something like:

SELECT * FROM posts where cats  REGEXP '"id":"[[:<:]]123[[:>:]]"';

or with:

extract_json_value( json_text TEXT CHARSET utf8 xpath TEXT CHARSET utf8 ) RETURNS TEXT CHARSET utf8

Here a good post to deal with JSON on mysql: http://rpbouman.blogspot.com.es/2015/11/mysql-few-observations-on-json-type.html

Hope it helps!

Answer 3

WHERE JSON_CONTAINS(cats, ID_TO_SEARCH) = 1

Should work. MySQL 5.7 supports JSON types.