Можно ли организовать фильтр/поиск товаров посредством JSON_EXTRACT?

Question

[ventacom]

Есть мысль организовать поиск товаров по их характеристикам через JSON_EXTRACT. В таблицу product добавить поле params (JSON) и хранить в нем информацию по хар-кам в следующем виде:

INSERT INTO product (`params`)
 VALUES ('{
	"group1": [10, 12, 15, 16],
	"group2": [21, 27],
	"group3": [36],
	"group5": [52]
}'),
('{
	"group1": [11, 12, 17, 19],
	"group2": [22],
	"group3": [38,39],
	"group4": [48]
}'),
('{
	"group1": [12, 16],
	"group2": [20],
	"group3": [33],
	"group4": [44]
}'),
('{
	"group1": [11, 18],
	"group2": [20, 27, 29],
	"group3": [36],
	"group5": [52]
}');

где group1 - это ИД группы фильтров, а [11, 18] - это ИД значений фильтров.
При этом значения хранятся в виде массива, поскольку в рамках одной группы у товара может быть выбрано несколько значений. Например, цвет вещи (group1): красный (11) и зеленый (12); размеры (group2): S (20), M (27) и L (29) и т.п.

При этом когда фильтруются товары по данным хар-кам, то в рамках группы поиск идет по условию ИЛИ, а между группами И. То есть: (Красный ИЛИ Зеленый ИЛИ Синий) И (S ИЛИ M)

Вопрос: можно ли организовать такую выборку с помощью JSON_EXTRACT или иной функции для работы с полем JSON через sql запрос?

Comments

[Модератор]

Не надо ставить как можно больше тэгов. Лучше оставить один, но конкретный, с которым проблема.
См.п.3.1 Регламента. Также обратите внимание на п.3.4

[Akina]

Есть мысль организовать поиск товаров по их характеристикам через JSON_EXTRACT.

Крайне неудачная мысль. Впрочем, это следствие. неудачной мыслью было хранить характеристики в JSON. Даже EAV был бы намного более оправданным решением.

можно ли организовать такую выборку с помощью JSON_EXTRACT или иной функции для работы с полем JSON через sql запрос?

Можно. И даже будет работать. Но работать будет прям-таки ппц как медленно... Ситуацию немного спасает то, что в JSON хранится массив, а значения целочисленные, и можно использовать multivalued index, если версия позволит... но спасает несильно.

[ventacom]

Akina, вот как раз сейчас реализовано по модели EAV и не нравится как организован поиск через:

foreach ($data['params'] as $params) {
$sql .= " AND EXISTS (SELECT * FROM product_param pp WHERE p.product_id = pp.product_id AND pp.param_id IN (" . implode(', ', array_map('intval', $params)) . "))";
}

И если в каждой группе выбрано хотя бы одному значению, то прям много проверок на EXISTS

Есть идеи для оптимизации?

[Akina]

Есть идеи для оптимизации?

Конечно. Переделать схему хранения данных.

PS. Других идей лично у меня нет.

[Dmitry Bay]

ventacom, чет тяжело так. Надо запросы оптимизировать в первую очередь. думаю, так будет быстрее -

SELECT p.id, p.name, COUNT(`pp`.`id`) FROM product p 
INNER JOIN product_param pp ON p.id = pp.product.id
GROUP BY p.id, p.name

Потом, если данные нужны дополнительные, то предыдущий запрос можно обернуть в JOIN.

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

Если mysql для вас кажется медленным, то попробуйте кэшировать результаты. С помощью REDIS к примеру. Лет 8 назад видел на хабре статью, там подобный EAV фильтр делали на bitmaps. Вот нашел кстати -
https://habr.com/ru/articles/261137/

[smilingcheater]

Ещё предложу рассмотреть вариант перенести фильтрацию на ElasticSearch - если проект большой, товаров и свойств много, фильтр сложный - это может быть оправдано.

[Ипатьев]

Dmitry Bay, использовать редис и битмапы для поиска по параметрам - глупость. Для этого есть эластик и мантикора

[Ипатьев]

Akina, скорость работы будет зависеть только от количества товаров. И если их немного, то будет работать "ппц" как быстро.
А если товаров много, то все равно надо делать фасетный поиск через соответствующий движок. И в него выливать из джейсон поля не проблема.

[Akina]

Ипатьев, имхо скорость в первую голову будет зависеть от количества условий в фасетке. Проблема в том, что на каждый JSON_EXTRACT функция будет с самого начала парсить значение. То есть "размер S и цвет зелёный либо синий" - это два парсинга на запись. Ну и фуллскан... так что если что и спасёт, так это 100% кэшированные таблицы, эдакий "магазин одного товара".

если товаров много, то все равно надо делать фасетный поиск через соответствующий движок.

Угу.

Answer 1

[mayton2019]

Я-бы переделал табличку. Пускай мета-данные по продуктам лежат в виде битовых полей.

create table product(
  ....
  params_group1 BINARY(100),
  params_group2 BINARY(100),
  params_group3 BINARY(100)
)

И дальше булевыми (bitwise) операциями как тут пишут https://dev.mysql.com/doc/refman/8.0/en/bit-functi... делать поиски продуктов по маске свойств.

Comments

[Ипатьев]

Это жутко неудобно. И потом выливать в эластик задолбаешься

[mayton2019]

Ипатьев, это - brainstorm. Можно и обсудить эластик отдельно но кажется автор хочет сэкономить.

Answer 2

[batyrmastyr]

Сделать можно, но здесь вас ждёт множество граблей:
1. Крайне неэффективное хранение: объём в разы больше, чем если раскидать по таблицам.
2. Эффективность индексов по JSON полю: если в постгресе она не особо радует, но можно выбирать между простотой и скоростью работы, то в мускуле есть только убогие костыли с индексированием частей объекта через генерируемые столбцы.
3. OR = JSON_OVERLAPS, IN = JSON_CONTAINS или разворачивать вручную. И чёрт знает, сработает ли индекс потому, что это функции, а не операторы.
4. Типизация значений: в JSON можно пихнуть и '3', и 3 и 3.0. Нужно внимательно следить за типами и при сохранении, и при поиске, чтобы всё правильно находилось.

В общем, если хотите навернуть такое, то стоит крепко задуматься о переходе на постгрес, в нём хотя бы 2 и 3 проблемы не будут выглядеть неразрешимыми.