：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，像建立基礎資料型別 (Elementary Data Type)一樣建立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" |
+-----+