Запрос массива целых чисел JSON на наличие значения в MySQL 5.7
У меня есть таблица MySQL 5.7, которая выглядит так:
Я хотел бы запросить строки, которые имеют значение 5 в мета-> Grant_ids. Я попытался
select * from content.banners where meta->>"$.grant_ids" in (5);
но не работает. Я не думаю, что in поддерживается, но есть идеи, как выполнить этот запрос?
Ответы 2
Вы можете написать это так, используя функцию JSON SQL:
select *
from content.banners b
where JSON_EXTRACT(b.meta,"$.grant_ids") in (5)
mysql> create table table_that_looks_like_this (
id int primary key, meta json);
mysql> insert into table_that_looks_like_this values
-> (1, null),
-> (2, '{"grant_ids": [5, 7]}'),
-> (3, '{"grant_ids": [4, 8]}'),
-> (4, '{"no_grant_ids": [5, 7]}');
mysql> select * from table_that_looks_like_this
where json_contains(meta, '[5]', '$.grant_ids');
+----+-----------------------+
| id | meta |
+----+-----------------------+
| 2 | {"grant_ids": [5, 7]} |
+----+-----------------------+
JSON_EXTRACT() вернет только массив JSON по указанному вами пути, а массив не является скалярным значением 5. Раскрытие массива JSON с помощью оператора ->> не имеет никакого эффекта; массив JSON без кавычек - это тот же самый массив.
Сейчас я работаю над книгой с главой о хранении JSON в базе данных SQL. Резюме: не надо.
Звучит интересно, это может быть очень коварно для хранения, и я использую только для денормализации/запросов. Нашли полезное для этого варианта использования, но должны быть очень осторожны и могли понять правило отсутствия JSON в базе данных SQL.
Использование JSON для форматирования наборов результатов в основном нормально, но у людей возникают проблемы, когда они сохраняют JSON, а затем пытаются выполнять поиск в полях документа JSON, как если бы они были обычными столбцами. По сути, если вы видите столбец JSON, на который есть ссылка в предложении WHERE (или любом другом предложении, кроме списка выбора), это красный флаг. Вам также может понравиться моя презентация Как использовать JSON в MySQL неправильно.
Спасибо за ссылку. У меня больше опыта работы с PostgreSQL, в котором есть поддержка индексов для JSONB. Также используется для данных в микросервисах (но опять же сложно), когда сетевой вызов будет намного медленнее. Спасибо
MySQL 8.0 также поддерживает индексы документов JSON, но не индексы GIN, такие как PostgreSQL.
спасибо, Билл, мне очень нравятся ваши книги, и я настоятельно рекомендую их другим людям (и не говорите мне, что в них есть именно этот пример, лол); Я ценю, что вы ответили на этот вопрос - я бы никогда не понял этого сам. Похоже, вам даже не нужны скобки в
where json_contains(meta, '5', '$.grant_ids');