云原生大数据计算服务 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\']');