mysql json 查询如何匹配所有数组元素的属性？

[fuxkcsdn]

mysql 版本 5.7.23

期望的结果是查找 rsp.data.codes 数组中, object.code 值为 abc 的行

是我的 json path 有误还是 mysql 不支持这样的查询呢？

create table test(
  id int(1) unsigned auto_increment primary key,
  jdoc json
)engine=InnoDB charset=utf8mb4;

insert into test(jdoc) values
('{"req":["xyz"],"rsp":{"data":{"codes":[{"code":"abc"},{"code":"def"},{"code":"xyz"}]}}}'),
('{"req":["222"],"rsp":{"data":{"codes":[{"code":"ijk"},{"code":"opq"},{"code":"123"}]}}}');

-- 记录为空
select * from test where json_extract(jdoc, "$.rsp.data.codes[*].code") = "abc";

-- 有记录
select * from test where json_extract(jdoc, "$.rsp.data.codes[0].code") = "abc";

[fuxkcsdn]

select * from test where JSON_CONTAINS(jdoc->"$.rsp.data.codes[*].code", JSON_ARRAY('abc'));

or

select * from test where JSON_CONTAINS(jdoc->"$.rsp.data.codes", JSON_OBJECT('code', 'abc'));

v.a. https://stackoverflow.com/questions/38797627/query-a-json-column-with-an-array-of-object-in-mysql