JSON_CONTAINS doesn't work in mysql query as expected

I'm trying to query over a table that has a JSON field (array of integers) like this {"1": 45, "2": 46}, I expected the below query returns all the rows that have 46 in their room_category field:

SELECT * FROM rooms WHERE JSON_CONTAINS(`room_category`, 46);

But it won't instead if I save data like this [45,46] which is also a valid JSON format the query works expected. Does anyone have an idea why this is happening?

Solution

JSON_CONTAINS() does not search for a value, it searches for a JSON document.

The following works:

mysql> SELECT * FROM rooms WHERE JSON_CONTAINS(`room_category`, '{"2":46}');
+--------------------+
| room_category      |
+--------------------+
| {"1": 45, "2": 46} |
+--------------------+

Or the following works, searching for the integer value but only at a specific key:

mysql> SELECT * FROM rooms WHERE JSON_CONTAINS(`room_category`, cast(46 as json), '$."2"');
+--------------------+
| room_category      |
+--------------------+
| {"1": 45, "2": 46} |
+--------------------+

This is a problem in your case, because I suppose you don't know the specific key that value is stored in. You want to find the value regardless of which key it's in.

It seems like you are trying to use JSON in lieu of a relational many-to-many table. This is an example where using JSON is not a good idea, because you're trying to search for a specific value of a multi-valued attribute.

The better way to model this is with a dependent table:

CREATE TABLE room_category (
  room_id INT,
  category_id INT,
  PRIMARY KEY (room_id, category_id),
  FOREIGN KEY (room_id) REFERENCES rooms (id),
  FOREIGN KEY (category_id) REFERENCES categories (id)
);

Store one category value per row. Then you can search it easily:

SELECT rooms.*
FROM rooms JOIN room_category USING(room_id)
WHERE category_id = 46;

It is usually true that in a relational database, storing data in a normalized manner is both more efficient and simpler than storing in JSON. Especially for values you want to search.

In other words, if you find yourself referencing a JSON column in your WHERE clause, you should instead use normal rows and columns instead of JSON.