PostgreSQL中JSONB数组字段更新：替换Class字段内容

如果你的PostgreSQL表中JSONB字段存储的是JSON对象数组（示例格式如下）：

[{"DOB": "04-May-2006", "Code": "2009585", "Class" : "I - Noon"}]

需要将数组中每个对象的Class字段值里的Noon替换为NS，可以用以下SQL语句实现：

-- 替换your_table为实际表名，jsonb_column为实际JSONB字段名
UPDATE your_table
SET jsonb_column = (
  SELECT jsonb_agg(
    CASE 
      WHEN obj->>'Class' LIKE '%Noon%'
      THEN obj || jsonb_build_object('Class', replace(obj->>'Class', 'Noon', 'NS'))
      ELSE obj
    END
  )
  FROM jsonb_array_elements(jsonb_column) AS obj
)
-- 可选WHERE条件：仅更新包含目标内容的行，避免全表扫描
WHERE jsonb_column @> '[{"Class": "%Noon%"}]'::jsonb;

语句说明： #

• jsonb_array_elements(jsonb_column)：将JSONB数组拆分为单个JSON对象，方便逐个处理
• replace(obj->>'Class', 'Noon', 'NS')：对每个对象的Class字段值执行字符串替换
• obj || jsonb_build_object(...)：将替换后的Class字段合并回原对象，保留其他字段不变
• jsonb_agg(...)：将处理后的单个对象重新聚合为JSON数组，覆盖原字段值

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