MaxCompute JSON类型使用指南

背景信息

半结构化数据介于结构和非结构化数据之间，数据中有一定的Schema，但是Schema灵活，没有强约束，通常数据的Schema是自描述的。典型的例子就是JSON数据。MaxCompute中已经支持Schema Evolution、JSON STRING或复杂类型内置函数、Lambda等工作来增强SQL对半结构化数据的支持，在这种模型下，系统仍然要求用户将半结构化数据通过规范化的处理后，导入到有Schema的结构化表中。当业务数据变化时，需要用户显式执行Schema Evolution DDL语句对表结构进行修改。

上述模式存在强Schema约束，无法将半结构化数据快速导入到系统中，数据导入时不符合Schema规范的数据只能丢弃，无法全量保存。针对以上问题，我们设计了一种新的数据类型JSON，既可以支持无强Schema约束的半结构化数据，又能够充分利用列存储的优化，同时满足高灵活性和高性能的要求。

基本原理

JSON数据类型作为一种新的数据类型，使用方法和其他类型相似。我们无需管理Schema信息，插入JSON数据后，由MaxCompute自动进行公共Schema提取并进行优化，尽可能列存以提高性能。以下面测试数据为例：

CREATE TABLE json_table
(
    json_val  json
);

CREATE TABLE string_table
(
    string_val  STRING
);

INSERT INTO string_table VALUES
        ('{"a":1, "b":2}')
        ,('{"a":"key", "b":2}')
        ,('{"c":3}');

INSERT INTO json_table
SELECT  json_parse(string_val)
FROM    string_table;

在写入数据时MaxCompute会自动提取出公共Schema<"a":binary, "b":bigint, "c":bigint>，当读取数据时可以根据Schema进行列裁剪，减少读的数据，提高效率。例如：

SELECT  json_val["b"]
        ,json_val["c"]
FROM    json_table
;  
-- 在读表时进行列裁剪只保留b, c变量
+-----+-----+
| _c0 | _c1 |
+-----+-----+
| 2   | NULL |
| 2   | NULL |
| NULL | 3   |
+-----+-----+

对于非公共Schema部分，MaxCompute采用BINARY进行存储，相较于原始STRING可以减少存储空间。同时相比于用户自定义UDF，新的JSON数据类型对于STRING和JSON相互转换效率也有较大提高。

使用限制

• 目前支持的开发工具包括odpscmd客户端，Studio和DataWorks，暂不支持Dataphin等外围生态。如果需要跟外部系统做组合使用时，请先确认后再使用。使用odpscmd客户端和Studio时需要关注以下内容。

  使用odpscmd客户端	使用Studio
  需要将客户端升级到V0.46.5及以上版本，否则无法使用desc json_table命令且无法通过Tunnel下载JSON类型数据。 需要将客户端安装路径下的conf\odps_config.ini文件中的参数use_instance_tunnel设置为false，否则查询会报错。	Studio只支持查询JSON类型的操作，不支持上传、下载JSON类型数据。

• 如果表存在其他引擎读取情况，比如Hologres等，目前不支持读取JSON数据类型。

• 暂不支持对一张表新增JSON列。

• 暂不支持对JSON类型的比较操作，也不支持对JSON类型进行ORDER BY、GROUP BY或作为JOIN的key等。

• 目前JSON NUMBER的整数和小数分别使用BIGINT和DOUBLE类型进行存储。当整数部分超出BIGINT范围时会溢出，小数转为DOUBLE时会损失精度。

• 生成JSON类型数据所用的字符串里不支持UNICODE\u0000。

• Java UDF和Python UDF暂不支持JSON类型。

• 目前JSON类型不支持Cluster表。

• Java SDK V0.44.0以下版本和PyODPS V0.11.4.1以下版本均不支持JSON数据类型。

• Delta Table类型的表暂不支持JSON类型。

• JSON数据类型可以被嵌套使用，最多支持不超过20层的嵌套。

LITERAL常量

JSON类型完全按照JSON标准定义，支持BOOLEAN、NUMBER、STRING、NULL、ARRAY、OBJECT。其中NUMBER采用BIGINT和DOUBLE存储，超过限制会有精度损失，同时注意json 'null'和sql null是不同的。

JSON 'null'
JSON '123'
JSON '123.34'
JSON 'true'
JSON '{"id":123,"name":"MaxCompute"}'
JSON '[12, 34]'

常量必须符合JSON标准定义，比如JSON '{id:123,"name":"MaxCompute"}'为非法JSON STRING，id必须包含在""中。

JSON类型定义

无需指定Schema，像创建基本数据类型一样创建JSON即可。

CREATE TABLE mf_json_table (json_val JSON);

JSON类型数据生成

有多种方式可以生成JSON类型数据：

• JSON Literal

  insert into mf_json_table values (json '123');

• JSON函数

  --json_object和json_array是MaxCompute的内置函数
  insert into mf_json_table select json_object("key",123, "value", "abc");
  
  select * from mf_json_table;
  
  --返回结果
  +----------+
  | json_val |
  +----------+
  | 123      |
  | {"key":123,"value":"abc"} |
  +----------+
  
  
  insert into mf_json_table select json_array("key",234, "value", "abc");
  
  select * from mf_json_table;
  
  --返回结果
  +----------+
  | json_val |
  +----------+
  | 123      |
  | ["key",234,"value","abc"] |
  | {"key":123,"value":"abc"} |
  +----------+

• 类型转换

  cast转换需要留意与json_parse的区别，具体参见复杂类型函数说明：

  insert into mf_json_table select cast("abc" as json);
  select * from mf_json_table;
  --返回
  +----------+
  | json_val |
  +----------+
  | 123      |
  | "abc"    |
  | ["key",234,"value","abc"] |
  | {"key":123,"value":"abc"} |
  +----------+

JSON访问

JSON类型数据可以通过索引方式、json_extract、get_json_object函数访问，返回JSON类型。

--返回123
SELECT v['id'] 
  FROM VALUES (JSON '{"id":123}') as t(v);
  
--返回12
SELECT v[0] 
  FROM VALUES (JSON '[12, 34]') as t(v);
  
--返回1
select v['x']['a']  from values (json '{"x": {"a": 1, "b": 2}}') as t(v);

--返回NULL
SELECT v[0] 
FROM VALUES (JSON '{"id":123}') as t(v);

--返回NULL
SELECT v['not_exists'] 
FROM VALUES (JSON '{"id":123}') as t(v);

--通过get_json_object函数访问，返回'MaxCompute'
SELECT GET_JSON_OBJECT(v, '$.x.name')
  FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') as t(v);
  
--返回结果
+-----+
| _c0 |
+-----+
| MaxCompute |
+-----+

--通过json_extract函数访问，返回JSON 'MaxCompute'
SELECT JSON_EXTRACT(v, '$.x.name') 
  FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') as t(v);
  
--返回结果
+-----+
| _c0 |
+-----+
| "MaxCompute" |
+-----+