AnalyticDB for MySQL 支持JSON数据类型。本文主要介绍JSON数据的格式、注意事项及使用示例。
注意事项
-
AnalyticDB MySQL版 支持标准JSON格式，写入JSON串时必须严格符合标准JSON格式规范。
-
JSON类型的数据列，不支持设置Default值。
JSON格式要求
AnalyticDB for MySQL
对JSON数据中的属性键
key
和属性值
value
有如下要求：
-
属性键
key必须使用双引号（
""）将key引起来，例如{"addr":"xyz"}中的"addr"。 -
属性值
value-
属性值
value支持的数据类型为：BOOLEAN、NUMBER、VARCHAR、ARRAY、OBJECT、NULL。说明-
使用JSON索引时，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"。
-
使用示例
创建表
CREATE TABLE json_test(
id int,
vj json
DISTRIBUTED BY HASH(id);写入数据
写入数据时，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 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 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数据时，支持使用指定数据下标取值，序号从0开始递增，暂不支持遍历整个数组。
-
相关文档