MaxCompute：GET_JSON_OBJECT

命令格式

string get_json_object(json <json>, string <json_path>)

參數說明

• json：必填，待處理的JSON資料。

• json_path：必填，需要返回的值的JSON路徑。

傳回值說明

返回STRING類型。

使用樣本

• 樣本1：從JSON中擷取key為a的value值。

  select get_json_object(json '{"a":1, "b":2}', '$.a');

  返回結果：

  +-----+
  | _c0 |
  +-----+
  | 1   |
  +-----+

• 樣本2：從JSON中擷取key為c的value值。

  select get_json_object(json '{"a":1, "b":2}', '$.c');

  返回結果：

  +-----+
  | _c0 |
  +-----+
  | NULL |
  +-----+

• 樣本3：JSON Path非法時，返回NULL。

  select get_json_object(json '{"a":1, "b":2}', '$invalid_json_path');

  返回結果：

  +-----+
  | _c0 |
  +-----+
  | NULL |
  +-----+

背景資訊

在一個標準JSON字串中，按照path抽取指定的字串。每次調用該函數時，都會讀一次未經處理資料，因此反覆調用可能影響效能和產生費用。您可以通過get_json_object，結合UDTF，輕鬆轉換JSON格式日誌資料，避免多次調用函數，詳情請參見利用MaxCompute內建函數及UDTF轉換JSON格式日誌資料。

參數說明

• json：必填。STRING類型。標準的JSON格式對象，格式為{Key:Value, Key:Value,...}。如果遇到英文雙引號（"），需要用兩個反斜線（\\）進行轉義。如果遇到英文單引號（'），需要用一個反斜線（\）進行轉義。

• path：必填。STRING類型。表示在json中的path，以$開頭。更多path資訊，請參見LanguageManual UDF。相關最佳實務案例，請參見JSON資料從OSS遷移至MaxCompute。不同字元的含義如下：

  • $：表示根節點。

  • .或['']：表示子節點。MaxCompute支援用這兩種字元解析JSON對象，當JSON的Key本身包含.時，可以用['']來替代。

  • []：[number]表示數組下標，從0開始。

  • *：Wildcard for []，返回整個數組。*不支援轉義。

限制條件

用['']取數只在新版本中支援，您需要添加設定Flag的語句set odps.sql.udf.getjsonobj.new=true;。

命令格式

string get_json_object(string <json>, string <path>)

傳回值說明

• 如果json為空白或非法的json格式，返回NULL。

• 如果json合法，path也存在，則返回對應字串。

• 您可以通過在Session層級設定odps.sql.udf.getjsonobj.new屬性來控制函數的返回方式：

  • 當設定set odps.sql.udf.getjsonobj.new=true;時，函數返回行為更標準，處理資料更方便，效能更好，推薦您使用此配置，函數返回行為規則如下：

    • 傳回值仍是一個JSON字串，可以繼續當作JSON來解析，而不再需要額外使用replace或regexp_replace等函數替換反斜線。

    • 一個JSON對象中可以出現相同的Key，可以成功解析，返回第一個Value值。

      --返回1。
      select get_json_object('{"a":"1","a":"2"}', '$.a');

    • 輸出結果按照JSON字串的原始排序方式輸出。

      --返回{"b":"1","a":"2"}。
      select get_json_object('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');

  • 當設定set odps.sql.udf.getjsonobj.new=false;時，函數返回行為規則如下：

    說明

    Hive相容模式，get_json_object使用的是新版本的行為。

    • 分行符號（\n）、引號（"）等JSON保留字元使用字串'\n'、'\"'顯示。

    • 一個JSON對象中不可以出現相同的Key，可能導致無法解析。

      --返回NULL。
      select get_json_object('{"a":"1","a":"2"}', '$.a');

    • 輸出結果按照字典排序方式輸出。

      --返回{"a":"2","b":"1"}。
      select get_json_object('{"b":{"b":"1","a":"2"},"a":"2"}', '$.b');

使用樣本

• 樣本1：提取JSON對象src_json.json中的資訊。命令樣本如下。

  --JSON對象src_json.json的內容。
  +----+
  json
  +----+
  {"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;
  --提取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]');
  --返回["h0","h1","h2"]。
  set odps.sql.udf.getjsonobj.new=true;
  select get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]');
  --返回["h0","h1","h2"]。
  set odps.sql.udf.getjsonobj.new=false;
  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 mf_json (id string, json string);
  --向表中插入資料，Key帶.。
  insert into table mf_json (id, json) values ("1", "{
  \"China.beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\",
  \"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}");
  --向表中插入資料，Key不帶.。
  insert into table mf_json (id, json) values ("2", "{
  \"China_beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\",
  \"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}");
  --取id的值，查詢key為China.beijing，返回0。由於包含.，只能用['']來解析。
  select get_json_object(json, "$['China.beijing'].school['id']") from mf_json where id =1;
  --取id的值，查詢key為China_beijing，返回0。查詢方法有如下兩種。
  select get_json_object(json, "$['China_beijing'].school['id']") from mf_json where id =2;
  select get_json_object(json, "$.China_beijing.school['id']") from mf_json where id =2;

• 樣本4：JSON輸入為空白或非法格式。命令樣本如下。

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

• 樣本5：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'); 

• 樣本6：當JSON的Key本身包含.時，可以用['']來替代。命令樣本如下。

  set odps.sql.udf.getjsonobj.new=true;
  --返回"1"。
  SELECT get_json_object('{"a.1":"1","a":"2"}', '$[\'a.1\']');