MaxCompute：GET_JSON_OBJECT函數

樣本1：從JSON類型資料中，分別擷取指定 key值為a、key值為c時，對應value值。

-- 返回1。
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.a');

-- 返回NULL。
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$.c');

樣本2：當json_path非法時，返回NULL。

-- 返回NULL。
SELECT GET_JSON_OBJECT(JSON '{"a":1, "b":2}', '$invalid_json_path');

樣本1：提取JSON對象src_json.json中的資訊

-- 準備測試資料。
CREATE TABLE IF NOT EXISTS src_json (
    json STRING
);

INSERT OVERWRITE TABLE src_json
VALUES
('{"store":
  {"fruit":[{"weight":8,"type":"apple"},
    {"weight":9,"type":"pear"}],
     "bicycle":{"price":19.95,
     "color":"red"}},
   "email":"amy@only_for_json_udf_test.net",
   "owner":"amy"}');

-- 提取owner欄位資訊，返回amy。
SELECT GET_JSON_OBJECT(src_json.json, '$.owner') FROM src_json;

-- 可選，採用保留原始字串的方式輸出。
SET odps.sql.udf.getjsonobj.new=true;
-- 提取store.fruit欄位第一個數組資訊，返回{"weight":8,"type":"apple"}。
SELECT GET_JSON_OBJECT(src_json.json, '$.store.fruit[0]') FROM src_json;

-- 提取不存在的欄位資訊，返回NULL。
SELECT GET_JSON_OBJECT(src_json.json, '$.non_exist_key') FROM src_json;

樣本2：提取數組型JSON資料資訊

-- 返回2222。
SELECT GET_JSON_OBJECT('{"array":[["aaaa",1111],["bbbb",2222],["cccc",3333]]}','$.array[1][1]');

-- 採用保留原始字串的方式輸出。
SET odps.sql.udf.getjsonobj.new=true;
-- 返回["h0","h1","h2"]。
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

-- 採用JSON保留字元轉義的方式輸出。
SET odps.sql.udf.getjsonobj.new=false;
-- 返回["h0","h1","h2"]。
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');

-- 返回h1。
SELECT GET_JSON_OBJECT('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]');

樣本3：提取帶有.的JSON資料中的資訊

-- 準備測試資料。
CREATE TABLE json_test (id STRING, json STRING);

-- 插入Key帶有.的資料。
INSERT INTO TABLE json_test (id, json) VALUES 
("1", 
  "{
    \"China.beijing\":
      {\"school\":
        {\"id\":0,\"book\":
          [{\"title\": \"A\",\"price\": 8.95},
           {\"title\": \"B\",\"price\": 10.2}]
        }
      }
  }"
);

-- 插入Key不帶有.的資料。
INSERT INTO TABLE json_test (id, json) VALUES 
("2", 
  "{
    \"China_beijing\":
      {\"school\":
        {\"id\":0,\"book\":
          [{\"title\": \"A\",\"price\": 8.95},
           {\"title\": \"B\",\"price\": 10.2}]
        }
      }
  }"
);

-- 使用方括弧['']解析包含'.'的資料。
-- 此處提取 'China.beijing' 下的 'id' 值，返回0。
SELECT GET_JSON_OBJECT(json, "$['China.beijing'].school['id']") FROM json_test WHERE id =1;

-- 不含特殊字元的資料，使用'.'和['']均有效且等效。
-- 此處提取 'China.beijing' 下的 'id' 值，返回0。
SELECT GET_JSON_OBJECT(json, "$['China_beijing'].school['id']") FROM json_test WHERE id =2;
SELECT GET_JSON_OBJECT(json, "$.China_beijing.school['id']") FROM json_test WHERE id =2;

樣本4：當JSON的Key本身包含.時，可以用['']來替代

SET odps.sql.udf.getjsonobj.new=true;

-- 返回1。
SELECT GET_JSON_OBJECT('{"a.1":"1","a":"2"}', '$[\'a.1\']');

樣本5：JSON輸入為空白或非法格式

-- 返回NULL。
SELECT GET_JSON_OBJECT('','$.array[1][1]');

-- 返回NULL。
SELECT GET_JSON_OBJECT('"array":["aaaa",1111],"bbbb":["cccc",3333]','$.array[1][1]');

樣本6：JSON字串涉及轉義

SET odps.sql.udf.getjsonobj.new=true;

--返回"1"。
SELECT GET_JSON_OBJECT('{"a":"\\"1\\"","b":"2"}', '$.a'); 

--返回'1'。
SELECT GET_JSON_OBJECT('{"a":"\'1\'","b":"2"}', '$.a');

樣本7：支援EmojiEmoji對應的編碼字串

-- 返回Emoji符號。
SELECT GET_JSON_OBJECT('{"a":"<Emoji符號>"}', '$.a');

注意：DataWorks 暫不支援輸入EmojiEmoji，僅支援通過Data Integration等工具直接將EmojiEmoji對應的編碼字串寫入MaxCompute，再用GET_JSON_OBJECT函數處理。