Mysql – Filter rows on data saved as JSON arrays

jsonMySQL

My data is saved as

_________________________________
id | category    | other data etc
--------------------------------
 1 | ["2","3"]   |bla bla
--------------------------------
 2 | ["254","34"]|bla bla
--------------------------------
 3 | ["11","25"] |bla bla
_________________________________

I want to select rows that have category=11. What SQL query should I use?

Best Answer

You can't really do this unless you are using MySQL 5.7 and the category column is using the JSON datatype. If it is, you can use the JSON_CONTAINS function:

SELECT * FROM `yourtable`
WHERE JSON_CONTAINS(category, '"11"')

Pay attention to the rules of containment:

  • A candidate scalar is contained in a target scalar if and only if they are comparable and are equal. Two scalar values are comparable if they have the same JSON_TYPE() types, with the exception that values of types INTEGER and DECIMAL are also comparable to each other.

  • A candidate array is contained in a target array if and only if every element in the candidate is contained in some element of the target.

  • A candidate nonarray is contained in a target array if and only if the candidate is contained in some element of the target.

  • A candidate object is contained in a target object if and only if for each key in the candidate there is a key with the same name in the target and the value associated with the candidate key is contained in the value associated with the target key.

We can see the issue with datatypes in this dataset:

CREATE TABLE myjson (
  id tinyint unsigned primary key auto_increment,
  category JSON
);

mysql> SELECT * FROM myjson;
+----+---------------+
| id | category      |
+----+---------------+
|  1 | ["2", "3"]    |
|  2 | ["254", "34"] |
|  3 | ["11", "25"]  |
|  4 | [2, 11]       |
+----+---------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM `myjson` WHERE JSON_CONTAINS(category, "11");
+----+----------+
| id | category |
+----+----------+
|  4 | [2, 11]  |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `myjson` WHERE JSON_CONTAINS(category, '"11"');
+----+--------------+
| id | category     |
+----+--------------+
|  3 | ["11", "25"] |
+----+--------------+
1 row in set (0.00 sec)
Related Question