7

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!

VerySeriousSoftwareEndeavours
  • 1,713
  • 3
  • 31
  • 57
  • 1
    There's no efficient way to do this type of search. That's why storing your data this way is an incredibly bad idea. – Barmar Mar 30 '16 at 22:41
  • Hi @McWayWeb, did you solve your problem? Any answer helped you? If yes, try to mark as solved to help others & keep S.O clean. Thanks! – JP. Aulet Oct 26 '19 at 11:58

4 Answers4

12

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

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

Sachith Muhandiram
  • 2,819
  • 10
  • 45
  • 94
nikolay.hristov
  • 739
  • 6
  • 7
6

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!

Community
  • 1
  • 1
JP. Aulet
  • 4,375
  • 4
  • 26
  • 39
3

WHERE JSON_CONTAINS(cats, ID_TO_SEARCH) = 1

Should work. MySQL 5.7 supports JSON types.

csn
  • 39
  • 4
0

If you really need to query specific values in your JSON string you are much better off to try and store the individual values in the database. In the example above you could just create a field table that joins to the posts table. Then a simple join statement will get you all of the right values.

Gremash
  • 8,158
  • 6
  • 30
  • 44