如何使用JSON索引功能-阿里云帮助中心

背景信息

大数据时代结构化数据检索已经逐渐有了多元化的、丰富的解决方案。但是，事实上大多数的大数据都是半结构化的，并且半结构化数据的数据量仍旧急剧增长。理解和分析半结构化数据的难度比结构化数据大很多，急需成熟的解决方案来处理半结构化数据。为了赋能用户、降低用户处理半结构化数据的难度，分析型数据库MySQL版提供了半结构化数据检索功能即JSON检索。

注意事项

分析型数据库MySQL版JSON索引有以下功能限制需要您注意。

不支持更改索引。表创建成功后不支持通过ALTER TABLE ADD增加索引，也不支持通过DROP JSON INDEX <idx_name> (col_name)删除索引列。
创建表时指定某一列类型为JSON之后，分析型数据库MySQL版自动构建JSON INDEX，系统不再支持普通的倒排索引操作。例如，where json_column = '{"id":123}'类似的字符串等值、不等值、范围过滤以及LIKE操作等。
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}，但仍然可以成功执行SQLselect id, json_test from json_tbl where json_extract(json_test, '$.addr.city') = 'beijing' and json_extract(json_test, '$.addr.no') = 0;。

建表

在建表时，将某一列指定为JSON类型即可为该列自动构建JSON索引。例如，以下示例中的json_test字段类型为JSON类型，建表成功后分析型数据库MySQL版自动为json_test列构建JSON索引。

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

JSON格式要求

在写入数据时，分析型数据库MySQL版对JSON类型数据中的属性键key和属性值value有以下要求。

属性键key：
- 必须使用双引号（""）将key引起来。
- key中不能包含点号（.），例如{"a.b":"value"}是不合法的JSON格式。
属性值value：
- 如果value是字符串类型，必须使用双引号将value引起来。
- 如果value是字符串类型，且value中包含双引号，需要做转义处理。
  例如，value为{"addr":"xyz"ab"c"}时，如果没有对xyz"ab"c中的双引号做转义处理，则不符合JSON格式规范。正确的写法为{"addr":"xyz\"ab\"c"}。
- 如果value是数值类型，直接写数据，无需使用双引号将value引起来。
- 如果value是BOOLEAN类型，直接写TRUE或者FALSE，不能写成1或者0。
- 如果value是NULL，直接写NULL。
- 分析型数据库MySQL版采用隐式类型推断来判断各个value的类型。同一个属性键key对应的value，前后必须为同一种类型。
  例如，同一个JSON类型的字段先写入数据{"id":0}，分析型数据库MySQL版推断id为数值类型；随后又写入数据{"id":"1"}，分析型数据库MySQL版推断id为字符串类型，此时前后类型不一致，系统会提示类型不匹配错误。
分析型数据库MySQL版支持JSON数组写入，包括PLAIN ARRAY及嵌套ARRAY。
例如，{"hobby":["basketball", "football"]}，{"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]}。
JSON类型字段的长度限制与VARCHAR相同。

写入数据

向表中写入数据时，JSON类型字段的写入方式与VARCHAR类型字段的写入方式相同，在JSON串两端使用单引号引起来即可。

注意

分析型数据库MySQL版只支持标准JSON格式，写入的JSON串必须严格符合标准JSON格式规范。在JSON串{key:value, key:value}中，每个key:value为一个属性对，其中key为属性键，value为属性值。

示例

以下SQL示例包含多种JSON数据格式，供您参考使用。

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版支持使用函数json_extract进行查询。

语法：json_extract(json_col, 'json-path')
参数：
- json_col，JSON列的列名。
- json-path，通过点号（.）进行分割的JSON属性键key的路径，其中$表示最外层的路径。

除了基本查询以外，分析型数据库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';

BETWEEN AND查询

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

IS NULL/IS NOT NULL查询

  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;

IN查询

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

LIKE查询

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

ARRAY查询

  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;