Postgres中jsonb对象数组的行内计算优化方案问询

问题背景 #

我有一张Postgres表，包含jsonb类型的列array_obj，数据结构如下：

id   | array_obj
---    ---
id_0 | [ {"Key": "k1", "Value": v1 }, {"Key": "k2", "Value": v2 }, ... ]

需求是计算指定Key对应的Value之和（例如k1的Value + k3的Value），同时要支持多组聚合结果（如result_1 = value(k1)+value(k3)、result_2=value(k7)+value(k124)等），避免独立计算各组结果。另外需要校验指定键的存在数量（比如统计k1和k2中存在的键个数，处理部分键缺失的情况）。

目前通过jsonb_to_recordset展开数组再分组聚合实现，但希望改为行内计算模式，现有查询如下：

SELECT
  id,
  sum(val) filter (where k in ('k1', 'k3')) as result,
  sum(case when k in ('k1', 'k2') then 1 else 0 end) as countkeys
FROM 
  my_table, jsonb_to_recordset(array_obj) as expanded(k text, val int)
GROUP BY
  id

对应的执行计划（LIMIT 50）：

Limit  (cost=0.56..256.99 rows=50 width=104) (actual time=0.217..4.965 rows=50 loops=1)
  Buffers: shared hit=166
  ->  GroupAggregate  (cost=0.56..17512338.31 rows=3414645 width=104) (actual time=0.216..4.959 rows=50 loops=1)
        Group Key: my_table.id
        Buffers: shared hit=166
        ->  Nested Loop  (cost=0.56..11485489.89 rows=341464500 width=72) (actual time=0.104..3.396 rows=5301 loops=1)
              Buffers: shared hit=166
              ->  Index Scan using xxx on my_table (cost=0.56..4656199.88 rows=3414645 width=498) (actual time=0.011..0.159 rows=51 loops=1)
                    Filter: (col = 0)
                    Rows Removed by Filter: 64
                    Buffers: shared hit=119
              ->  Function Scan on jsonb_to_recordset expanded  (cost=0.00..1.00 rows=100 width=40) (actual time=0.042..0.047 rows=104 loops=51)
                    Buffers: shared hit=47
Planning:
  Buffers: shared hit=198
Planning Time: 0.552 ms
Execution Time: 5.099 ms

补充示例数据：

id | [{"Key":"af_m1","Value":9772},{"Key":"af_m2","Value":7413},{"Key":"af_m3","Value":2359}]

需计算af_m1 + af_m3 = 9772 + 2359 = 12131，部分行可能缺少指定Key，需自行处理缺失情况。

解决方案 #

方案1：自定义函数实现键值快速查找 #

创建一个SQL函数，直接从jsonb数组中根据Key提取对应的Value：

CREATE OR REPLACE FUNCTION get_jsonb_array_value(arr jsonb, target_key text)
RETURNS int AS $$
SELECT (elem->>'Value')::int
FROM jsonb_array_elements(arr) elem
WHERE elem->>'Key' = target_key
LIMIT 1;
$$ LANGUAGE sql STABLE;

然后直接在查询中调用函数完成行内计算，用COALESCE处理键缺失的情况：

SELECT
  id,
  -- 计算多组结果
  COALESCE(get_jsonb_array_value(array_obj, 'af_m1'), 0) + COALESCE(get_jsonb_array_value(array_obj, 'af_m3'), 0) AS result_1,
  COALESCE(get_jsonb_array_value(array_obj, 'af_m7'), 0) + COALESCE(get_jsonb_array_value(array_obj, 'af_m124'), 0) AS result_2,
  -- 统计指定键的存在数量
  (CASE WHEN get_jsonb_array_value(array_obj, 'af_m1') IS NOT NULL THEN 1 ELSE 0 END) +
  (CASE WHEN get_jsonb_array_value(array_obj, 'af_m2') IS NOT NULL THEN 1 ELSE 0 END) AS countkeys
FROM my_table
WHERE col = 0;

方案2：使用JSONB路径表达式（无需自定义函数） #

利用PostgreSQL内置的jsonb_path_query_first和jsonb_path_exists函数，通过JSON路径直接定位目标键值：

SELECT
  id,
  -- 计算多组结果
  COALESCE((jsonb_path_query_first(array_obj, '$[*] ? (@.Key == "af_m1")')->>'Value')::int, 0) +
  COALESCE((jsonb_path_query_first(array_obj, '$[*] ? (@.Key == "af_m3")')->>'Value')::int, 0) AS result_1,
  COALESCE((jsonb_path_query_first(array_obj, '$[*] ? (@.Key == "af_m7")')->>'Value')::int, 0) +
  COALESCE((jsonb_path_query_first(array_obj, '$[*] ? (@.Key == "af_m124")')->>'Value')::int, 0) AS result_2,
  -- 统计指定键的存在数量
  (CASE WHEN jsonb_path_exists(array_obj, '$[*] ? (@.Key == "af_m1")') THEN 1 ELSE 0 END) +
  (CASE WHEN jsonb_path_exists(array_obj, '$[*] ? (@.Key == "af_m2")') THEN 1 ELSE 0 END) AS countkeys
FROM my_table
WHERE col = 0;

方案优势 #

这两种方案都无需展开数组和分组聚合，避免了原方案中数据膨胀（执行计划显示展开后行数是原表的100倍），一次表扫描即可生成所有需要的结果列，内存和IO开销显著降低，尤其适合大表场景。

内容的提问来源于stack exchange，提问作者Vince.Bdn