Как правильно фильтровать значения json с помощью whereJsonContains в laravel?

Question

[Denis]

Подскажите, пожалуйста, как мне правильно пользоваться данной функции выбирая вложенные значения в колонке таблицы в json

У меня колонка json выглядит вот так

{
    "area": {
        "name": "Kvadratura",
        "slug": "area",
        "type": "string",
        "view": "string",
        "value": "54"
    },
    "rest": {
        "name": "Ostalo",
        "slug": "rest",
        "type": "select",
        "view": "checkbox",
        "value": "Terasa"
    },
}

Сама метод

Model::whereJsonContains('filters.rooms.value', 1)->get();

Сам запрос который генерирует данный метод выше

select * from `table` where (json_contains(`filters`, "1", '$."rooms"."value"'))

Проблема в том что он просто не выводит ничего, хотя точно запись с таким значением есть и далеко не одна, что через phpmyadmin запрос отправлять sql, что через laravel, выводит просто ничего.

Что в данном случаи я делаю нет?

Comments

[Denis]

Кусочек json с ключом tooms так же присутствует в записях, просто в кусочке json выше его нет

[Дмитрий]

denis-chenykh,
а если

select * from `table` where (json_contains(`filters`, '"1"', '$."rooms"."value"'));

версия мускула какая?

[Denis]

Версия 8.0.30
Действительно так работает
Спасибо большое

[Дмитрий]

denis-chenykh, дык создайте ответ для других людей

[Denis]

а если

select * from `table` where (json_contains(`filters`, '"1"', '$."rooms"."value"'));

версия мускула какая?

[Григорий Васильков]

Вот упрямый а. Совсем как я даже хуже.

[Denis]

а это я вчера отметил, но о том чтобы бд переделать я все же задумался)

Answer 1

[Григорий Васильков]

Для безопасности твоей головушки сообщу - если оно выглядит "как-то сложновато", то лучше не надо, найди способ обойтись. Даже если ты сжав зубы добъешь фильтрацию по JSON-у в РЕЛЯЦИОННОЙ (созданной чтобы всё, что ищется было в РАЗНЫХ полях) - это потом кто-то не сможет прочитать, поредактировать и вообще исправить если захочет изменить.

Всё что ищешь положи рядом в поля (создай колонку, пробежись циклом) и пользуйся обычными селектами. А json храни как будто это не знаю... картинка. Просто куча данных которые как положил, так и достал.

Comments

[Denis]

Если у меня всегда были сохраненные параметры одни и те же в json, тогда соглашусь, а когда они бывают разные относительно категорий и прочих вещий, то колонок будет весьма многовато

[Григорий Васильков]

denis-chenykh, поэтому параметры выносятся в отдельную таблицу и являются там строками а не колонками.

car
// id, name, title, param1, param2

car_params
// id, car_id, param_name, param_value

[Denis]

Григорий Васильков, Хорошо, я тогда подумаю над рациональностью хранения. А если смотреть на производительность в целом, получается лучше потом обращаться к вспомогательной таблице несколько раз со строками, чем к колонку с json ?

[Григорий Васильков]

denis-chenykh, почти правильно, рационально хранить - в json, рационально искать - из полей.

Формально ты не обращаешься много раз, ты вытаскиваешь записи со всеми полями (или с двумя-тремя полями) по критерию, и потом что-то с ними делаешь. Базы данных это про работу с пачкой чаще всего. Ты вытягиваешь пачку, что-то с ней делаешь и обратно кладешь. Можно и по одной, но лучше пытаться сразу пачку.

[Denis]

Григорий Васильков, Понял вас. Большое спасибо за ценную информацию

[Григорий Васильков]

Основная проблема поиска в поле JSON, что чтобы достать эту самую пачку записей базе нужно обойти ВСЕ записи ПО ОДНОЙ распаковать этот json, проверить поле и дать тебе совпавшие. Идея баз данных в том, чтобы запросы обходили не КАЖДУЮ запись, а использовав заранее сделанный ИНДЕКС проверяли только пару десятков записей вместо миллионов.

По факту там ЕСТЬ индексирование поля из JSON которое по сути делает то, что я говорю само для тебя, но не создает при этом целую таблицу, а сохраняет "как бы" в кеш (алфавитный указатель делает, индекс) и поиск в итоге быстро происходит, но сам синтаксис запроса в коде и неочевидность такого процесса сломают тех, кто попытается это изменить или исправить. Тогда как когда есть таблица и поля и ты ищешь по ним, это скажем так большему числу специалистов будет понятно.

Чаще всего когда работают с JSON отталкиваются от того, что у нас есть только `id`, а остальное черный ящик, из которого можно уже потом вывести, чем фильтровать, а для организации поисков нужные данные выдергивают в таблицы, json же лежит для справки, если что-то не так идет можно пробежаться руками и перепроставить поля из json, он типа "контрольные данные".

[Denis]

Понял вас, спасибо большое за разъяснения, расставили мне все по полочкам