2

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?

  • 2
    Even though data may come as JSON to your application, that doesn't mean you should store them as such if you later want to work with its individual pieces in SQL. Consider creating and using a junction table. – Andriy M Apr 6 '16 at 20:04
5

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.