GET_JSON_OBJECT

背景信息

在一个标准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;时，函数返回行为采用了保留原始字符串的方式进行输出。
    推荐您使用此配置，函数返回行为更标准，处理数据更方便，性能更好。如果MaxCompute项目有使用JSON保留字符转义行为的存量作业，建议保留原有行为方式，避免因未验证而直接使用该行为产生错误或正确性问题。函数返回行为规则如下：

    • 返回值仍是一个JSON字符串，可以继续当做JSON来解析，而不再需要额外使用replace或regexp_replace等函数替换反斜线。
    • 一个JSON对象中可以出现相同的Key，可以成功解析。

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

    • 支持Emoji表情符号对应的编码字符串。但DataWorks暂不支持输入Emoji表情符号，仅支持通过数据集成等工具直接将Emoji表情符号对应的编码字符串写入MaxCompute，再用get_json_object函数处理。

      --返回Emoji符号。
      select get_json_object('{"a":"<Emoji符号>"}', '$.a');

    • 输出结果按照JSON字符串的原始排序方式输出。

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

  • 当设置set odps.sql.udf.getjsonobj.new=false;时，函数返回行为采用了JSON保留字符转义的方式进行输出。函数返回行为规则如下：
    • 换行符（\n）、引号（"）等JSON保留字符使用字符串'\n'、'\"'显示。
    • 一个JSON对象中不可以出现相同的Key，可能导致无法解析。

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

    • 不支持解析Emoji表情符号编码的字符串。

      --返回NULL。
      select get_json_object('{"a":"<Emoji符号>"}', '$.a');

    • 输出结果按照字典排序方式输出。

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

  说明 自2021年1月21日及之后新创建的MaxCompute项目中，get_json_object函数的返回行为默认为保留原始字符串。2021年1月21日之前创建的MaxCompute项目中，get_json_object函数的返回行为默认为JSON保留字符转义。您可以通过以下示例判断MaxCompute项目中get_json_object函数采用了哪种行为，执行命令如下：

  select get_json_object('{"a":"[\\"1\\"]"}', '$.a');
  --JSON保留字符转义的行为返回：
  [\"1\"]
  
  --保留原始字符串的行为返回：
  ["1"]

  请通过申请链接或搜索（钉钉群号：11782920）加入MaxCompute开发者社区钉群联系MaxCompute技术支持团队，将您的项目中的get_json_object函数返回行为切换为保留原始字符串的行为，避免在Session级别频繁设置属性。

使用示例

• 示例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');