文档

GET_JSON_OBJECT

更新时间:

在一个标准JSON字符串中,按照指定方式抽取指定的字符串。

使用说明

GET_JSON_OBJECT函数的作用是在一个标准JSON字符串中,按照JSON PATH抽取指定的字符串。当前函数的入参支持两种类型:

  • 入参为JSON类型:基于最新支持的JSON数据类型,采用更为规范的JSON PATH。

  • 入参为STRING类型:原有的JSON PATH解析方式。

入参类型不同时函数的使用方式和注意事项不同,本文为您展示入参分别为JSON和STRING类型时,GET_JSON_OBJECT函数的使用方法。

说明
  • 新JSON类型所使用的JSON PATH与原有的JSON PATH规范不同,可能存在兼容性问题。

  • GET_JSON_OBJECT不支持JSON PATH的正则语法。

入参为JSON类型

命令格式

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 |
    +-----+

入参为STRING类型

背景信息

在一个标准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\']');

相关函数

GET_JSON_OBJECT函数属于复杂类型函数或字符串函数。

  • 更多对复杂类型数据(例如ARRAY、MAP、STRUCT、JSON数据)的处理函数请参见复杂类型函数

  • 更多查找字符串、转换字符串格式的相关函数请参见字符串函数