本文介绍如何在AnalyticDB for MySQL中使用JSON。

背景信息

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

注意事项

  • 创建表时指定某一列的数据类型为JSON之后,AnalyticDB for MySQL自动创建JSON索引,且暂不支持更改JSON索引。
  • JSON支持的数据类型有Boolean、Number、Varchar、Array、Object、Null,Number不能超过Double取值范围,否则会写入报错。
  • AnalyticDB for MySQL支持标准JSON格式,写入JSON串时必须严格符合标准JSON格式规范。
  • 增加列时不支持设置非Null Default值。

创建表

例如,以下示例中的vj字段为JSON类型,建表成功后AnalyticDB for MySQL自动为vj列构建JSON索引。

CREATE TABLE json_test(
  id int,
  vj json COMMENT 'json类型,自动创建索引'
)
DISTRIBUTED BY HASH(id);

JSON格式要求

写入数据时,AnalyticDB for MySQL对JSON数据中的属性键key和属性值value有以下要求。

  • 属性键key:必须使用双引号("")将key引起来。
  • 属性值value
    • 如果value是字符串类型,必须使用双引号("")将value引起来。

      如果value是字符串类型,且value中包含双引号,需要做转义处理。例如,value{"addr":"xyz"ab"c"}时,正确的写法为{"addr":"xyz\"ab\"c"}

    • 如果value是数值类型,直接写数据,不能使用双引号("")将value引起来。
    • 如果valueBoolean类型,直接写TRUE或者FALSE,不能写成1或者0
    • 如果valueNull,直接写Null
  • AnalyticDB for MySQL支持JSON数组写入,包括PLAIN ARRAY及嵌套ARRAY。

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

写入数据

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

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

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

查询数据

查询数据时,AnalyticDB for MySQL支持使用函数json_extract

  • 语法
    json_extract(json, jsonpath)
  • 命令说明:从json中返回jsonpath指定的值。
  • 参数说明
    • json为JSON列的列名。
    • jsonpath,通过点号(.)进行分割的JSON属性键key的路径,其中$表示最外层的路径。

    更多JSON函数用法请参见JSON函数

  • 示例
    • 基本查询
      select json_extract(vj,'$.name') from json_test where id=1;
    • 等值查询
      select id, vj from json_test where json_extract(vj, '$.name') = 'abc';
      select id, vj from json_test where json_extract(vj, '$.c') = true;
      select id, vj from json_test where json_extract(vj, '$.age') = 30;
      select id, vj from json_test where json_extract(vj, '$.company.name') = 'alibaba';
    • 范围查询
      select id, vj from json_test where json_extract(vj, '$.age') > 0;
      select id, vj from json_test where json_extract(vj, '$.age') < 100;
      select id, vj from json_test where json_extract(vj, '$.name') > 'a' and json_extract(vj, '$.name') < 'z';
    • IS NULL/IS NOT NULL查询
      select id, vj from json_test where json_extract(vj, '$.remark') is null;
      select id, vj from json_test where json_extract(vj, '$.name') is null;
      select id, vj from json_test where json_extract(vj, '$.name') is not null;
      							
    • IN 查询
      select id, vj from json_test where json_extract(vj, '$.name') in ('abc','xyz');
      select id, vj from json_test where json_extract(vj, '$.age') in (10,20);
    • LIKE查询
      select id, vj from json_test where json_extract(vj, '$.name') like 'ab%';
      select id, vj from json_test where json_extract(vj, '$.name') like '%bc%';
      select id, vj from json_test where json_extract(vj, '$.name') like '%bc';
    • ARRAY查询
      select id, vj from json_test where json_extract(vj, '$.addr[0].city') = 'beijing' and json_extract(vj, '$.addr[1].no') = 0;
      select id, vj from json_test where json_extract(vj, '$.addr[1].city') = 'shenzhen' and json_extract(json_test, '$.addr.no') = 0;
      说明 查询ARRAY数据时,支持下标取值,暂不支持遍历整个数组。
说明 同一个key可以对应不同类型的value

例如示例数据"key": 1"key": "1"json_extract(col, '$.key')=1将返回数字"key": 1json_extract(col, '$.key')='1'将返回字符串"key": "1"