PostgreSQL更新text列内JSON数组指定条件元素的属性值

你之前使用整段文本替换的方案无法生效，核心问题有两个：

• 硬编码匹配整段JSON文本容错率极低，只要存在空格、换行差异，或是base_table值不同（比如Report3中是another_table），就会匹配失败
• 没有利用PostgreSQL原生的JSON处理能力：parameters字段存储的是结构化JSON数据，不需要做纯文本级别的替换

PostgreSQL 10原生支持JSON/JSONB类型的结构化操作，以下语句可以精准遍历JSON数组修改目标值，完全不会改动其余内容，也不需要关心缩进、表名差异：

UPDATE example
SET parameters = (
  SELECT jsonb_pretty(
    jsonb_agg(
      CASE
        WHEN elem->>'expression' = 'date_from' THEN jsonb_set(elem, '{default}', '"Date.today"')
        WHEN elem->>'expression' = 'date_until' THEN jsonb_set(elem, '{default}', '"Date.new(2050,01,01)"')
        ELSE elem
      END
    )
  )
  FROM jsonb_array_elements(parameters::jsonb) elem
)
WHERE parameters IS NOT NULL;

• 先将text类型的parameters字段转为jsonb格式，通过jsonb_array_elements把JSON数组拆分为单个元素逐行遍历
• 用CASE分支判断每个元素的expression字段值：
  • 匹配到date_from时，用jsonb_set将对应元素的default字段更新为Date.today
  • 匹配到date_until时，用jsonb_set将对应元素的default字段更新为Date.new(2050,01,01)
  • 其余所有元素完全保留原始内容，不做任何修改
• 修改完成后用jsonb_agg将元素重新拼接为JSON数组，再通过jsonb_pretty格式化输出，保持和原有存储接近的缩进可读性
• WHERE条件自动过滤parameters为null的行，避免无效计算

正式执行UPDATE前，可以先用SELECT语句预览修改结果，确认符合预期后再执行更新：

SELECT 
  id,
  name,
  parameters AS 原始值,
  (
  SELECT jsonb_pretty(
    jsonb_agg(
      CASE
        WHEN elem->>'expression' = 'date_from' THEN jsonb_set(elem, '{default}', '"Date.today"')
        WHEN elem->>'expression' = 'date_until' THEN jsonb_set(elem, '{default}', '"Date.new(2050,01,01)"')
        ELSE elem
      END
    )
  )
  FROM jsonb_array_elements(parameters::jsonb) elem
) AS 修改后值
FROM example
WHERE parameters IS NOT NULL;

• 该方案自动适配所有结构一致的记录，无论base_table是什么值，都能精准定位到目标字段修改，不需要针对每个报表单独写替换规则
• 如果不需要保留格式化缩进，去掉外层的jsonb_pretty()函数即可，存储的文本会更紧凑

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