全部产品
Search
文档中心

云原生数据仓库AnalyticDB:JSON

更新时间:Jun 21, 2024

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引起来。

    • 如果valueBoolean类型,直接写true或者false,不能写成1或者0,且truefalse不能大写。

    • 如果valueNull,直接写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开始递增,暂不支持遍历整个数组。

相关文档

JSON索引