Поиск по JSON в MySQL не хочет работать?

Question

[Вячеслав]

привет всем!
есть у меня mysql 5.7

есть табллица, в ней поле items типа JSON с данными:

{"2464": {"price": "122.97", "status": "10", "quantity": "1", "articleid": "2464", "warehouseid": "3"}, "2469": {"price": "165.61", "status": "10", "quantity": "1", "articleid": "2469", "warehouseid": "3"}, "2522": {"price": "64.18", "status": "10", "quantity": "1", "articleid": "2522", "warehouseid": "3"}, "2524": {"price": "198.73", "status": "10", "quantity": "1", "articleid": "2524", "warehouseid": "3"}}

Вопрос почему у меня в MySQL хранится JSON - прошу опустить

пытаюсь сделать поиск самым обычнейшим запросом:

SELECT * FROM `table` WHERE `items`->'$.*.status' = ':status'

и о чудо, не работает! :)
притом что если поставить в условии вместо знака = знак > то работать будет, но не правильно. он будет игнорировать условие и просто выводить весь список (`items`->'$.*.status' > ':status')

Подскажите что я делаю не так и как быть чтобы было так?
Спасибо

Answer 1

[Rsa97]

SELECT * FROM `table` WHERE `items`->'$.*.status' = :status

Comments

[Вячеслав]

к сожалению эффекта ноль.
пробовал уже по всякому )

[Rsa97]

> SELECT `items`->'$.*.status' FROM `table`;
'["10", "10", "10", "10"]'

[Вячеслав]

Rsa97: да, SELECT работает. а вот в условии WHERE - нивкакую

[Вячеслав]

Rsa97: Хотя документация говорит о том, что все должно работать.
к тому же со знаком > работает, но криво)

[Rsa97]

Вячеслав: А у вас в :status какое значение? Для того, чтобы была найдена эта строка там должно быть '["10", "10", "10", "10"]'

[Вячеслав]

Rsa97: естественно там 10, что вы думаете, я совсем ку-ку чтоли =) даже если ручками вместо :status подставить 10, то эффект ровно такой же - никакой :)

[Rsa97]

Вячеслав: Не '10', а именно '["10", "10", "10", "10"]'. Вы же сравниваете на полное равенство

[Вячеслав]

Rsa97: только что проверил чито ради интереса

$status = '["10", "10", "10", "10"]';
		
$sql = "SELECT * FROM `scl_orders` o WHERE o.items->'$.status' = :status";
			
$db = Yii::$app->db->createCommand($sql)->bindValue(':status', $status)->queryAll();

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

[Rsa97]

Вячеслав: Прошу прощения, возвращается таки не строка, а данные с типом JSON. Так что либо

CAST(`id`->'$.*.status' AS CHAR) = '["10", "10", "10", "10"]'

либо

`id`->'$.*.status' = JSON_ARRAY ("10", "10", "10", "10")

А вообще, -> или JSON_EXTRACT не проверяет совпадение, она возвращает выбранные по path элементы JSON. Для поиска и проверки есть JSON_SEARCH, которая возвращает пути к найденным элементам или NULL, если элементы не найдены.

> SELECT JSON_SEARCH(`id`, 'all', 10, null, '$.*.status') FROM `test`
'["$.\"2464\".status", "$.\"2469\".status", "$.\"2522\".status", "$.\"2524\".status"]'

Или же JSON_CONTAINS, которая просто проверяет наличие нужного значения по указанному пути, но она не допускает использование в пути *.

[Вячеслав]

Rsa97: https://phptoday.ru/post/ispytyvaem-novye-json-voz...

SELECT * FROM features 
WHERE feature->"$.properties.STREET" = 'MARKET'

[Вячеслав]

Rsa97: а вот оцициальный блог:
https://www.percona.com/blog/2016/03/07/json-docum...

mysql:
https://dev.mysql.com/doc/refman/5.7/en/json-searc...
там если ниже глянуть, то json_extract заменяется именно кострукцией ->

[Rsa97]

Вячеслав: Именно, заменяется. И выбирает все элементы, соответствующие запросу. Если указан точный путь, (как в первом примере) то вернётся строка, если путь содержит маску - то массив.

> SELECT `items`->'$."2464".status' FROM `table`;
"10"

[Вячеслав]

Rsa97: дааа. но мне не нужен SELECT =) мне нужно условие WHERE =)

что-то типа такого, что есть в официальное документации

SELECT * FROM features 
WHERE feature->"$.properties.STREET" = 'MARKET'

Понимаете? )))

[Rsa97]

Вячеслав:

WHERE JSON_SEARCH(`items`, 'one', 10, null, '$.*.status') IS NOT NULL

Найдёт строку, в которой хотя бы один элемент с путём '$.*.status' имеет значение 10.

[Вячеслав]

Rsa97: Очень не хочется Вас расстраивать, но я так тоже уже пробовал))

$sql = "SELECT * FROM `scl_orders` WHERE JSON_SEARCH(`items`, 'one', :status, null, '$.*.status') IS NOT NULL";
$res = Yii::$app->db->createCommand($sql)->bindValue(':status', $status)->queryAll();

print_r($res); выдает Array ( )
пусто =)
ни в какую не хочет работать.
я уже и индексы убрал из JSON и сделал обращение $.status вместо $.*.status
никак :(

[Вячеслав]

Rsa97: похоже придется все таки этот JSON выносить в отдельную таблицу в реляционном виде, эх

[Rsa97]

Вячеслав: У меня работает:

> SELECT * FROM `table` WHERE JSON_SEARCH(`items`, 'one', 10, null, '$.*.status') IS NOT NULL
{"2464": {"price": "122.97", "status": "10", "quantity": "1", "articleid": "2464", "warehouseid": "3"}, "2469": {"price": "165.61", "status": "10", "quantity": "1", "articleid": "2469", "warehouseid": "3"}, "2522": {"price": "64.18", "status": "10", "quantity": "1", "articleid": "2522", "warehouseid": "3"}, "2524": {"price": "198.73", "status": "10", "quantity": "1", "articleid": "2524", "warehouseid": "3"}}

[Вячеслав]

Rsa97: фух! вроде сработало :) только я one поменял на all. строк может быть много :) спасибо большой за помощь!!! :)

[Rsa97]

Вячеслав: В данном случае между all и one разницы нету. all вернёт все пути, one - только первый. Для поиска это безразлично.