PostgreSQL中如何为JSONB数组内的每个对象添加新键值对

要给master_data_approval_table表中所有记录的approval_value字段（JSONB类型数组）里的每个对象添加"comment": null键值对，可以使用以下UPDATE语句：

UPDATE master_data_approval_table
SET approval_value = (
  SELECT jsonb_agg(elem || '{"comment": null}'::jsonb)
  FROM jsonb_array_elements(approval_value) AS elem
);

语句说明： #

• jsonb_array_elements(approval_value)：将JSONB数组拆分为单个对象元素，每行对应一个数组元素
• elem || '{"comment": null}'::jsonb：利用PostgreSQL的JSONB合并操作符||，给每个对象元素添加新的键值对
• jsonb_agg(...)：将处理后的单个对象重新聚合为JSONB数组
• 最终将聚合后的数组赋值回原字段，完成所有记录的更新

如果需要避免对空数组或null值的字段做无效更新，可以添加WHERE条件：

UPDATE master_data_approval_table
SET approval_value = (
  SELECT jsonb_agg(elem || '{"comment": null}'::jsonb)
  FROM jsonb_array_elements(approval_value) AS elem
)
WHERE approval_value IS NOT NULL AND approval_value != '[]'::jsonb;

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