按照指定的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路径。 |
返回值说明
返回JSON或STRING类型。返回规则如下:
入参类型 | 返回值 |
入参为JSON类型 | 返回JSON类型。
|
入参为JSON格式的STRING字符串 | 返回STRING类型。 若json_path无效或非法,返回NULL。 说明 此时函数行为与GET_JSON_OBJECT中输入为STRING类型时的行为一致。 |
使用示例
入参为JSON类型
示例1:从JSON中获取key为a的value值。
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函数。