PostgreSQL中为JSONB列新增计算生成的属性

问题分析 #

你当前的代码存在几个关键问题：

• 列名混淆：目标列是fee，但代码里错误使用了excess_fees
• JSON解析逻辑错误：jsonb_array_elements(plans.excess_fees->'Value')写法有误，fee是JSON数组结构，需先拆分数组元素，再提取每个元素内的Value字段
• 仅处理单元素：{0, ValueExclGst}仅针对数组第一个元素，无法适配多元素数组的通用场景

正确实现方案 #

采用「拆分数组元素→修改单个元素→重新聚合数组」的思路，以下是可行的UPDATE语句：

UPDATE plans
SET fee = (
  SELECT CASE
           -- 原数组为空时直接返回空数组
           WHEN jsonb_array_length(fee) = 0 THEN '[]'::jsonb
           -- 处理每个数组元素，新增计算字段
           ELSE jsonb_agg(
                  jsonb_set(
                    elem,
                    '{ValueExclGst}',
                    -- 按公式计算并转换为JSONB类型
                    to_jsonb(ROUND((elem->>'Value')::numeric / 1.07, 2))
                  )
                )
         END
  FROM jsonb_array_elements(fee) AS elem
)
-- 可选：仅更新非空数组的记录，提升执行效率
WHERE fee <> '[]'::jsonb;

代码说明 #

1. jsonb_array_elements(fee)：将fee数组拆分为单个JSON对象元素
2. (elem->>'Value')::numeric：提取元素中的Value字符串并转换为数值类型，用于计算
3. ROUND(..., 2)：按公式计算结果并保留两位小数
4. to_jsonb(...)：将计算结果转换为JSONB类型，确保能正确插入JSON对象
5. jsonb_set(elem, '{ValueExclGst}', ...)：为每个元素新增ValueExclGst字段
6. jsonb_agg(...)：将处理后的单个元素重新聚合成JSON数组
7. CASE语句：兼容原数组为空的场景，保持结果为空数组

执行后结果 #

执行完成后，plans表数据会符合预期：

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