PostgreSQL：如何单步为JSON列添加/更新数组类型的notes键

完全可以通过单条UPDATE语句实现需求，利用PostgreSQL的jsonb函数组合处理两种场景：

核心UPDATE语句 #

UPDATE my_table
SET data = jsonb_set(
  -- 处理data为空或null的情况，确保基础是有效JSON对象
  coalesce(data::jsonb, '{}'::jsonb),
  '{notes}',
  -- 若notes不存在则用空数组，存在则取原数组，再追加新元素
  coalesce(data::jsonb->'notes', '[]'::jsonb) || '["要添加的笔记内容"]'::jsonb,
  -- 第四个参数设为true：键不存在时自动创建，存在时覆盖（这里覆盖的是拼接后的新数组）
  true
)
WHERE id = 1;

关键逻辑说明 #

• coalesce(data::jsonb, '{}'::jsonb)：兜底处理data列是空对象{}、null或无效JSON的情况，保证后续操作基于合法的JSONB对象。
• coalesce(data::jsonb->'notes', '[]'::jsonb)：如果notes键不存在，默认使用空数组；存在则直接取原数组。
• ||操作符：JSONB数组的拼接运算符，把新的单元素数组拼接到原数组（或空数组）末尾，实现追加效果。
• jsonb_set(..., true)：最后一个参数true允许自动创建不存在的键，刚好适配“不存在则创建、存在则更新”的需求。

整合到函数中（同时更新其他列） #

如果需要封装成函数，同时更新表中其他列，示例如下：

CREATE OR REPLACE FUNCTION update_my_table_with_notes(
  p_id INT,
  p_note_content TEXT,
  p_category TEXT, -- 示例其他列参数
  p_verified BOOLEAN -- 示例其他列参数
) RETURNS VOID AS $$
BEGIN
  UPDATE my_table
  SET 
    data = jsonb_set(
      coalesce(data::jsonb, '{}'::jsonb),
      '{notes}',
      coalesce(data::jsonb->'notes', '[]'::jsonb) || jsonb_build_array(p_note_content),
      true
    ),
    category = p_category, -- 更新其他列
    verified = p_verified,
    updated_at = NOW() -- 可选：添加更新时间戳
  WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

函数优化点 #

• 使用jsonb_build_array(p_note_content)替代手动拼接JSON字符串，避免转义问题，更安全灵活。
• 如果你的data列频繁做这类操作，建议直接将列类型改为jsonb，省去每次::jsonb的转换开销，性能更优。

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