首页 / JSON
GET_JSON_OBJECT 提取 JSON 字段
GET_JSON_OBJECT 按 JSON 路径从字符串中取值。各引擎的函数名、返回类型和路径规则差别较大，是迁移时最容易出错的函数之一。
各引擎写法
| 引擎 | 支持 | 写法 | 说明 | 依据 |
|---|---|---|---|---|
| MaxCompute | 支持 | GET_JSON_OBJECT(json, json_path) |
返回 STRING，路径以 $ 开头，不支持正则。转义行为受 odps.sql.udf.getjsonobj.new 控制。 | 官方文档 |
| Hologres | 支持 | GET_JSON_OBJECT(json, json_path) |
属于 hive_compatible 扩展，使用前需要先执行 CREATE EXTENSION。 | 官方文档 |
| StarRocks | 支持 | get_json_object(json_str, json_path) |
文档中 get_json_string 和 get_json_object 是同一条目，返回字符串。需要保留 JSON 类型时用 json_query 或箭头操作符。 | 官方文档 |
| Doris | 需改写 | GET_JSON_STRING(json_str, json_path) |
路径以 $ 开头，不能包含双引号和方括号字符，含点号的键要用双引号括起来。官方建议改用 jsonb 类型和 jsonb_extract 系列函数。 | 官方文档 |
| Hive | 支持 | get_json_object(json_string, path) |
官方文档 | |
| Spark SQL | 支持 | get_json_object(json_txt, path) |
从 Spark 1.5.0 起提供。 | 官方文档 |
| Flink SQL | 需改写 | JSON_VALUE(json, path) |
提取 JSON 中的标量值，可以用 RETURNING 子句指定返回类型。 | 官方文档 |
迁移注意事项
- MaxCompute 中，2021 年 1 月 21 日之前创建的项目默认会转义 JSON 保留字符，之后创建的项目保留原始字符串。新旧项目的结果可能不同，官方推荐把 odps.sql.udf.getjsonobj.new 设为 true。
- MaxCompute 每次调用都会重新解析整个 JSON。同一个字段取多个值时，频繁调用会明显变慢，可以考虑 UDTF。
- Hologres 报错 function get_json_object (text, unknown) does not exist 时，常见原因有两个：RAM 用户没有扩展所在 schema 的查询权限，或者第一个参数不是 TEXT 类型。前者可以把扩展建在 pg_catalog 下，后者需要先 CAST 为 text。
- StarRocks 和 Doris 返回字符串，取出的数字需要再做 CAST。Doris 中值无法无损转换为字符串时返回 NULL。
示例
从订单扩展字段中取城市
SELECT GET_JSON_OBJECT(ext, '$.city') AS city FROM orders;CREATE EXTENSION IF NOT EXISTS hive_compatible SCHEMA public;
SELECT GET_JSON_OBJECT(ext, '$.city') AS city FROM orders;SELECT get_json_string(ext, '$.city') AS city FROM orders;SELECT GET_JSON_STRING(ext, '$.city') AS city FROM orders;SELECT JSON_VALUE(ext, '$.city') AS city FROM orders;