Как искать в массиве json некоторые элементы с определенным ключом?
Таблица: some_table, Записи:
id|data (json array of json object)
1|[{"a": 1, "b": 2}, {"a": 3, "b": 4}]
2|[{"a": 5, "b": 6}, {"a": 3, "b": 4}]
Не могли бы вы показать мне SQL (MySQL), чтобы найти любую запись, данные которой содержат элемент (с ключом "a" и значением 5). Будет найдена только запись №2. Я попробовал следующий SQL, но потерпел неудачу, потому что я новичок в использовании SQL для JSON.
select * from some_table where json_contains(data, '5', '$.a') = 1;
Ответы 2
У вас есть два фундаментальных недостатка в вашем текущем операторе SQL:
- Для проверки наличия ключей в документе JSON вы можете использовать
json_contains_path()вместоjson_contains(), что позволит вам указать конкретный путь к ключу, фактически не указывая значение, для которого вы ищете указанный ключ. path, который вы используете для поиска ключаa, не учитывает, что этот ключ будет содержаться в структуре массива — вам нужно изменитьpath, чтобы искать ключaповсюду в документе. (от H/T до MySQL :: Справочное руководство по MySQL 8.0 :: 11.6 Тип данных JSON)
С этим знанием ваш запрос теперь будет выглядеть так:
select * from some_table where json_contains_path(data, 'one', '$**.a')
который, используя ваш образец набора данных, возвращает:
+----+--------------------------------------+
| id | data |
+----+--------------------------------------+
| 1 | [{"a": 1, "b": 2}, {"a": 3, "b": 4}] |
| 2 | [{"a": 5, "b": 6}, {"a": 3, "b": 4}] |
+----+--------------------------------------+
Моя ошибка - похоже, что реализация JSONPath в MySQL 8.0 не поддерживает селектор с двойной точкой ... См. обновленный селектор пути в моем ответе выше (**).
На этот раз это сработало, и я получил результат выше. Теперь у меня есть любая запись, данные которой содержат элемент с ключом «a», что мне делать дальше, чтобы отфильтровать их и получить только это значение w.r.t. ключ "а" это 5? Я попробовал select * from some_table where json_contains(data, '5', '$**.a') = 1; и получил ERROR 3149 (42000): In this situation, path expressions may not contain the * and ** tokens or an array range..
Спасибо чувак! Я использовал json_extract и json_contains, и мне это удалось. select * from some_table where json_contains(data->"$**.a", '5');
@painterdown Рад слышать!
select id, data
from
(
select 1 as id, '[{"a": 1, "b": 2}, {"a": 3, "b": 4}]' as data
union select 2 as id, '[{"a": 5, "b": 6}, {"a": 3, "b": 4}]' as data
) as tmp
where json_contains(data -> '$[*].a','5')
Большое спасибо, и я только что понял то же решение, что и ваше. :)
Спасибо за вашу помощь. Я только что попробовал ваш SQL выше, но получил ошибку:
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2.У вас есть идеи?