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

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.

CodePudding user response：

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.