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

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";

Code

jdoc

abc

test

1 条回复

fuxkcsdn

2018-12-28 17:09:48 +08:00

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