Postgres从JSON对象最后条目提取KPI周度指标及解决SQL报错

报错根因拆解 #

原有SQL执行触发语法错误，核心问题有4个：

• 类型转换优先级错误：kpi->'details'::json的实际执行逻辑是将单引号包裹的字符串'details'强制转为json类型，由于单字符串不是合法JSON结构，直接触发「Token details is invalid」的语法报错
• 数据类型适配错误：json_array_length仅支持数组类型的JSON入参，但details节点下是键值对形式的JSON对象，并非数组结构，无法用数组长度计算、数字索引的逻辑访问
• 操作符逻辑错位：#>>操作符的右操作数必须是文本格式的访问路径数组，无法将计算得到的数字索引直接作为路径参数
• 字段引用混乱：SQL中同时出现kpi和col两个未对齐的字段引用，存在逻辑错误

正确实现逻辑 #

需求为提取detailsJSON对象中存储顺序最靠后的条目（不依赖具体周的键名），利用Postgres中JSON对象原生保留键插入顺序的特性，先遍历拿到该节点下的所有周键名，取最后一个键作为访问路径，再提取对应指标值即可。

通用兼容写法（适配json/jsonb类型） #

SELECT
  -- 提取最后一个周的销售目标
  (kpi->'details'->last_week_key)->>'sales-goal'::numeric AS "Sales Goal",
  -- 提取最后一个周的实际销售额
  (kpi->'details'->last_week_key)->>'sales'::numeric AS Sales
FROM areas
-- 关联 lateral 子查询动态计算最后一个周的键名
CROSS JOIN LATERAL (
  SELECT (array_agg(week_key))[array_length(array_agg(week_key), 1)] AS last_week_key
  FROM json_object_keys(kpi->'details') AS week_key
) AS current_week_info;

jsonb类型优化写法 #

如果KPI字段采用Postgres推荐的jsonb存储格式，可借助json_path函数直接取最后一个键，无需遍历聚合，执行效率更高：

SELECT
  (kpi->'details'->jsonb_path_query_last(kpi->'details', '$.keyvalue().key'))->>'sales-goal'::numeric AS "Sales Goal",
  (kpi->'details'->jsonb_path_query_last(kpi->'details', '$.keyvalue().key'))->>'sales'::numeric AS Sales
FROM areas;

执行效果 #

上述SQL运行后返回结果与预期一致：

Sales Goal      |           Sales                       
-----------------+------------------------
 96.0            | 86.6

内容的提问来源于stack exchange，提问作者Malyk