PostgreSQL JSONB特殊Upsert咨询：实现键集与新对象对齐且同键保留旧值

我明白你要的这个特殊JSONB Upsert逻辑——既要保留新对象的所有键结构，又要让同名键用旧值，还要删掉旧对象里多余的键。原来用||操作符的方式确实做不到自动删键，因为它只是简单合并两个对象、保留所有键。这里给你一个精准实现需求的方案：

核心思路是：只基于新传入对象的键集合来构建最终的JSONB对象，对每个键优先取旧对象的值（如果存在），否则用新对象的值，这样自然就过滤掉了旧对象里不在新对象中的键。

最终SQL语句示例 #

INSERT INTO your_table (project_id, other_stuf, payload)
VALUES (123, 'some_value', '{"a":999, "d":4}'::jsonb)
ON CONFLICT (project_id, other_stuf)
DO UPDATE SET payload = (
  SELECT jsonb_object_agg(key, COALESCE(answers.payload->key, excluded.payload->key))
  FROM jsonb_object_keys(excluded.payload) AS key
);

逻辑拆解 #

1. 提取新对象的所有键：jsonb_object_keys(excluded.payload)会把新传入的payload的所有键列出来，这一步确保我们只处理新对象中存在的键，旧对象里额外的键（比如你例子中的b、c）会被自动排除。
2. 优先使用旧值：COALESCE(answers.payload->key, excluded.payload->key)会先尝试从旧对象（answers.payload）中取对应键的值，如果旧对象没有这个键，再用新对象（excluded.payload）的值。对应你例子中的a键，就会保留旧值1而非新传入的999。
3. 重新聚合为JSONB对象：jsonb_object_agg把处理后的键值对重新组合成完整的JSONB对象，这就是最终要更新的payload。

验证你的示例 #

旧对象：{"a":1,"b":2,"c":3}，新对象：{"a":999,"d":4}

• 提取新键：a、d
• 处理a：取旧值1；处理d：旧对象无此键，取新值4
• 聚合结果：{"a":1,"d":4}，完全符合你的预期。

额外注意点 #

• 如果新传入的payload是空对象，更新后的payload也会变成空对象，这符合“仅包含新对象全部键”的要求。
• 这个方法的性能在键数量不多的场景下非常友好，要是你处理的JSONB对象有大量键，可以考虑提前做一些键的缓存优化，但一般场景下直接用这个语句就足够了。

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