How to search by exact hash in Rails ActiveRecord for JSON MySql field?

Question

I have a field with json type in MySQL database. The field contains values like

{
    "city_eq": "NewYork",
    "rent_true": false,
    "estate_type_eq": 0
}

What is the proper way to search records by incoming hash? Like this:

Link.where(json_fields: {
    "city_eq": "NewYork",
    "rent_true": false,
    "estate_type_eq": 0
})

Query should return a record only if all values are the same and presented in field. Order of hash keys might differs.

You need to check the documentation for your database and use an SQL string. ActiveRecord does not automatically create queries for types like JSON/JSONB from hashes. Or you can reconsider if you really want to use a JSON column instead of a table... — max, Sep 21 '21 at 22:28
Yes. If you can't create a given query with the ActiveRecord query interface you use SQL strings or Arel. The query interface works for most simple queries but does not cover database specific features like JSON columns which are not standardized. — max, Sep 21 '21 at 22:49
@max I mean it's not a big deal to switch from AA to raw SQL. The problem is I can't build the right SQL :) — Meliborn, Sep 21 '21 at 22:52
https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html — max, Sep 21 '21 at 22:52

score 2 · Accepted Answer · answered Sep 21 '21 at 23:02

Here's a demo of an SQL search that matches the JSON data you show:

mysql> create table mytable (json_fields json);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into mytable values ('{
    '>     "city_eq": "NewYork",
    '>     "rent_true": false,
    '>     "estate_type_eq": 0
    '> }');
Query OK, 1 row affected (0.02 sec)

mysql> select * from mytable where json_contains(json_fields,
       json_object('city_eq', 'NewYork', 'rent_true', false, 'estate_type_eq', 0));
+-----------------------------------------------------------------+
| json_fields                                                     |
+-----------------------------------------------------------------+
| {"city_eq": "NewYork", "rent_true": false, "estate_type_eq": 0} |
+-----------------------------------------------------------------+

However, searching for JSON this way is bound to cause a table-scan. I would recommend you do not store data in JSON if you want to search it in an optimized way.

That's what I'm looking for. What an interesting way to pass arguments to the `json_object` method. Thanks. — Meliborn, Sep 21 '21 at 23:13

How to search by exact hash in Rails ActiveRecord for JSON MySql field?

1 Answers1