JSON ARRAY查询对标于Elasticsearch的Object类型，而不是nested类型。

例如，JSON ARRAY数据为 {"addr":[{"city":"beijing", "no":11}, {"city":"shenzhen", "no":0}]} ，如果检索条件是 addr.city=beijing AND addr.no=0 ，虽然上述数组中没有元素为 {"city":"beijing", "no":0} ，但仍然可以成功执行SQL select id, json_test from json_tbl where json_extract(json_test, '$.addr.city') = 'beijing' and json_extract(json_test, '$.addr.no') = 0; 。

create tablegroup par2_group;
CREATE TABLE json_tbl (
  id bigint COMMENT '',
  sid bigint COMMENT '',
  json_test json COMMENT '类型为json，自动构建json index',
  PRIMARY KEY (id,sid)
)
PARTITION BY HASH KEY (sid) PARTITION NUM 8
CLUSTERED BY (sid)
TABLEGROUP par2_group
OPTIONS (UPDATETYPE='realtime')
COMMENT '';

例如， value 为 {"addr":"xyz"ab"c"} 时，如果没有对 xyz"ab"c 中的双引号做转义处理，则不符合JSON格式规范。正确的写法为 {"addr":"xyz\"ab\"c"} 。

例如，同一个JSON类型的字段先写入数据 {"id":0} ，分析型数据库MySQL版推断 id 为数值类型；随后又写入数据 {"id":"1"} ，分析型数据库MySQL版推断 id 为字符串类型，此时前后类型不一致，系统会提示类型不匹配错误。

例如， {"hobby":["basketball", "football"]} ， {"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]} 。

insert into json_tbl (id, sid, json_test) values(0, 0, '{"id":0, "name":"abc", "age":0}');
insert into json_tbl (id, sid, json_test) values(1, 1, '{"id":1, "name":"abc", "age":10, "gender":"femal"}');
insert into json_tbl (id, sid, json_test) values(2, 2, '{}');
insert into json_tbl (id, sid, json_test) values(3, 3, '{"id":3, "name":"xyz", "age":30, "company":{"name":"alibaba", "place":"hangzhou"}}');
insert into json_tbl (id, sid, json_test) values(4, 4, null);
insert into json_tbl (id, sid, json_test) values(5, 5, '{"id":5, "name":"abc", "age":50, "company":{"name":"alibaba", "place":"america"}}');
insert into json_tbl(id, sid, json_test) values (6, 6, '{"a":1, "b":"abc-char", "c":true, "d":null}');
insert into json_tbl(id, sid, json_test) values (7, 7, '{"uname":{"first":"lily", "last":"chen"}, "addr":[{"city":"beijing", "no":1}, {"city":"shenzhen", "no":0}], "age":10, "male":true, "like":"fish", "remark":null, "hobby":["basketball", "football"]}');

除了基本查询以外，分析型数据库MySQL版支持对 key 按条件进行查询。

select * from json_tbl order by id limit 10;

  select id, json_test from json_tbl where json_extract(json_test, '$.uname.first') = 'lily';
  select id, json_test from json_tbl where json_extract(json_test, '$.age') = 10;
  select id, json_test from json_tbl where json_extract(json_test, '$.addr.city') = 'shenzhen';
  select id, json_test from json_tbl where json_extract(json_test, '$.age') != 10;

  select id, json_test from json_tbl where json_extract(json_test, '$.age') > 0;
  select id, json_test from json_tbl where json_extract(json_test, '$.age') < 100;
  select id, json_test from json_tbl where json_extract(json_test, '$.name') > 'a' and json_extract(json_test, '$.name') < 'z';

  select id, json_test from json_tbl where json_extract(json_test, '$.age') between 0 and 100;

  select id, json_test from json_tbl where json_extract(json_test, '$.remark') is null;
  select id, json_test from json_tbl where json_extract(json_test, '$.name') is null;
  select id, json_test from json_tbl where json_extract(json_test, '$.name') is not null;

  select id, json_test from json_tbl where json_extract(json_test, '$.hobby') in ('football');

  select id, json_test from json_tbl where json_extract(json_test, '$.uname.first') like 'li%';
  select id, json_test from json_tbl where json_extract(json_test, '$.uname.first') like '%il%';
  select id, json_test from json_tbl where json_extract(json_test, '$.uname.first') like '%ly';

  select id, json_test from json_tbl where json_extract(json_test, '$.addr.city') = 'shenzhen' and json_extract(json_test, '$.addr.no') = 0;
  select id, json_test from json_tbl where json_extract(json_test, '$.addr.city') = 'beijing' and json_extract(json_test, '$.addr.no') = 0;