Как выполнить поиск по точному хэшу в поле Rails ActiveRecord для JSON MySQL?

#mysql #sql #ruby-on-rails #mysql-json

Вопрос:

У меня есть поле с json типом в базе данных MySQL. Поле содержит такие значения, как

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

Каков правильный способ поиска записей по входящему хэшу? Подобный этому:

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

Запрос должен возвращать запись только в том случае, если все значения одинаковы и представлены в поле. Порядок хэш-ключей может отличаться.

1. Вам нужно проверить документацию для вашей базы данных и использовать строку SQL. ActiveRecord не создает автоматически запросы для таких типов, как JSON/JSONB, из хэшей. Или вы можете пересмотреть, действительно ли вы хотите использовать столбец JSON вместо таблицы…

2. @max можно использовать необработанный sql

3. ДА. Если вы не можете создать данный запрос с помощью интерфейса запроса ActiveRecord, вы используете строки SQL или Arel. Интерфейс запросов работает для большинства простых запросов, но не охватывает специфические функции базы данных, такие как столбцы JSON, которые не стандартизированы.

4. @max Я имею в виду, что нет ничего сложного в том, чтобы переключиться с AA на необработанный SQL. Проблема в том, что я не могу создать правильный SQL 🙂

5. dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

Ответ №1:

Вот демонстрационная версия поиска SQL, которая соответствует данным JSON, которые вы показываете:

 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} |
 -----------------------------------------------------------------

Однако поиск JSON таким образом обязательно приведет к сканированию таблицы. Я бы рекомендовал вам не хранить данные в JSON, если вы хотите искать их оптимизированным способом.