JSON数据类型注意事项,格式要求和示例_云原生数据仓库AnalyticDB MySQL版-阿里云帮助中心

为赋能用户、降低用户处理半结构化数据的难度，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数据时，支持下标取值，暂不支持遍历整个数组。