为赋能用户、降低用户处理半结构化数据的难度， AnalyticDB MySQL版 提供了半结构化数据检索功能即JSON索引。

背景信息

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

注意事项

  • 如果在创建表时指定了某一列的数据类型为JSON，内核版本为3.1.5.10以下的 AnalyticDB MySQL版 集群会自动创建JSON索引。

    JSON索引创建后，您可以通过ALTER语句来新增或删除索引。更多详情，请参见 ALTER TABLE 。

  • 如果在创建表时指定了某一列的数据类型为JSON，内核版本为3.1.5.10及以上的 AnalyticDB MySQL版 集群不再默认添加JSON的索引，需要手动DDL添加。 
    ALTER table <table_name> ADD key|index <index_name>(json_column);
  • AnalyticDB MySQL版 支持标准JSON格式，写入JSON串时必须严格符合标准JSON格式规范。
  • JSON类型的数据列，不支持设置Default值。

创建表

例如，以下示例中的 vj 列的数据类型为JSON，建表成功后 AnalyticDB MySQL版 自动为 vj 列构建JSON索引。 

CREATE TABLE json_test(
  id int,
  vj json COMMENT 'json类型，自动创建索引'
DISTRIBUTED BY HASH(id);
JSON索引创建后，您可以通过ALTER语句来删除或新增索引。语句如下：
  • 删除索引 
    ALTER TABLE db_name.table_name DROP KEY index_name;
    说明 您可以使用SHOW INDEXES命令查看目标表中的所有索引信息，包括索引名称（即 key_name ）。更多详情，请参见 SHOW 。
  • 新增索引 
    ALTER TABLE db_name.table_name ADD KEY index_name(column_name);

JSON格式要求

写入数据时， AnalyticDB MySQL版 对JSON数据中的属性键 key 和属性值 value 有如下要求：
  • 属性键 key

    必须使用双引号（ "" ）将 key 引起来，例如 {"addr":"xyz"} 中的 "addr" 。

  • 属性值 value
    • 属性值 value 支持的数据类型为：BOOLEAN、NUMBER、VARCHAR、ARRAY、OBJECT、NULL。
      说明
      • NUMBER不能超过DOUBLE的取值范围，否则会写入报错。
      • ARRAY类型可以为PLAIN ARRAY或嵌套ARRAY。例如， {"hobby":["basketball", "football"]} 和 {"addr":[{"city":"beijing", "no":0}, {"city":"shenzhen", "no":0}]} 。
    • 如果 value 是字符串类型，必须使用双引号（ "" ）将 value 引起来。
      说明 如果 value 是字符串类型，且 value 中包含双引号，需要做转义处理。例如， {"addr":"xyz"ab"c"} 中的 value ，即 "xyz"ab"c" 部分，需转义为 "xyz\"ab\"c" ，但由于写入过程中 \ 会被转义，因此写入的数据应为 {"addr":"xyz\\"ab\\"c"} 。
    • 如果 value 是数值类型，直接写数据，不能使用双引号（ "" ）将 value 引起来。
    • 如果 value 是 Boolean 类型，直接写 true 或者 false ，不能写成 1 或者 0 ，且 true 和 false 不能大写。
    • 如果 value 是 Null ，直接写 Null 。
    • 同一个 key 支持不同类型的 value ，查询时会返回指定类型的结果。

      例如，执行 INSERT INTO test_tb1 VALUES ({"id": 1}) 语句时，表示插入的 id 值是数字 1 ；而执行 INSERT INTO test_tb1 VALUES ({"id": "1"}) 语句时，表示插入的 id 值是字符串 "1" 。

      如果执行 SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= 1; 语句进行查询时，将返回数字 "id": 1 ；而执行 SELECT id FROM test_tb1 WHERE json_extract(col, '$.id')= '1'; 语句进行查询时，将返回字符串 "id": "1" 。

写入数据

向表中写入数据时，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":"f"}');
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":"a\\"b\\"c", "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 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 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;
      说明 查询ARRAY数据时，支持下标取值，暂不支持遍历整个数组。