JSON_EXTRACT

按照指定的json_path,从JSON格式的字符串或JSON类型数据中提取对应的字符串或JSON数据。

命令格式

JSON_EXTRACT函数支持以下两种签名格式,请根据不同的使用需求选择不同的函数签名。

  • 签名1:输入JSON类型数据,输出JSON类型。

    JSON JSON_EXTRACT(JSON <json_expr>, STRING <json_path>)
  • 签名2:执行SET odps.sql.bigquery.compatible=true;命令打开BigQuery兼容模式,此时支持JSON格式的STRING字符串作为输入,输出STRING类型。

    说明

    此时函数行为与GET_JSON_OBJECT中输入为STRING类型时的行为一致。

    STRING JSON_EXTRACT(STRING <json_string_expr>, STRING <json_path>)

参数说明

参数

描述

json_expr

必填,JSON类型。待处理的JSON类型数据。

json_string_expr

必填,STRING类型。标准的JSON格式字符串。

json_path

必填,STRING类型。需要返回的值的JSON路径。

返回值说明

返回JSONSTRING类型。返回规则如下:

入参类型

返回值

入参为JSON类型

返回JSON类型。

  • json_path无效,返回NULL。

  • json_path非法,返回报错。

入参为JSON格式的STRING字符串

返回STRING类型。

json_path无效或非法,返回NULL。

说明

此时函数行为与GET_JSON_OBJECT中输入为STRING类型时的行为一致。

使用示例

入参为JSON类型

  • 示例1:JSON中获取keyavalue值。

    SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$.a');

    返回结果:

    +-----+
    | _c0 |
    +-----+
    | 1   |
    +-----+
  • 示例2:当指定的JSON路径无效或目标键不存在时,返回NULL。

    SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$.c');

    返回结果:

    +-----+
    | _c0 |
    +-----+
    | NULL |
    +-----+
  • 示例3:JSON Path格式非法,返回报错信息。

    SELECT JSON_EXTRACT(JSON '{"a":1, "b":2}', '$a');

    返回结果:

    Invalid argument - Param json path $invalid_json_path is invalid

入参为STRING类型

入参为STRING类型时,需要执行SET odps.sql.bigquery.compatible=true;命令打开BigQuery兼容模式。

  • 示例1:BigQuery兼容模式下,支持输入标准的JSON格式字符串,返回STRING类型数据。

    SET odps.sql.bigquery.compatible=true;
    SELECT JSON_EXTRACT(
      '{"class": {"students": [{"name": "Jane"}]}}',
      '$') AS json_text_string;

    返回结果:

    +------------------------------------------+
    |          json_text_string                |
    +------------------------------------------+
    | {"class":{"students":[{"name":"Jane"}]}} |
    +------------------------------------------+
  • 示例2:BigQuery兼容模式下,输入JSON格式字符串,当json_path无效或非法时,返回NULL。

    SET odps.sql.bigquery.compatible=true;
    SELECT JSON_EXTRACT('{"a": null}', "$.b");

    返回结果:

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

相关函数

JSON_EXTRACT函数属于JSON函数,更多JSON类型函数请参见JSON函数