背景信息

大数据时代结构化数据检索已经逐渐有了多元化的、丰富的解决方案。但是,事实上大多数的大数据都是半结构化的,并且半结构化数据的数据量仍旧急剧增长。理解和分析半结构化数据的难度比结构化数据大很多,急需成熟的解决方案来处理半结构化数据。为了赋能用户、降低用户处理半结构化数据的难度,分析型数据库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引起来。
    • 如果valueBOOLEAN类型,直接写TRUE或者FALSE,不能写成1或者0
    • 如果valueNULL,直接写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;