PostgreSQL中更新jsonb列保留原有字段及重构更新语句方法

嘿，我来帮你搞定这两个PostgreSQL jsonb的问题，一步步给你讲清楚！

要实现更新jsonb列但不覆盖未指定的字段，PostgreSQL提供了两种非常实用的方式：

• 使用jsonb合并操作符||：这个操作符会把新的jsonb对象和原有的jsonb对象合并，新对象里的字段会覆盖原字段，但原对象中没提到的字段会完整保留。举个简单例子：

UPDATE your_table
SET data = data || '{"home_street_name": "Updated Moran Rd", "new_field": "new_value"}'::jsonb
WHERE contact_id = 111231;

执行后，原data里的email、firstname等字段都会保留，只有home_street_name被更新，同时新增new_field。

• 使用jsonb_set函数：如果你需要精准更新单个或嵌套字段，这个函数更合适，它只会修改指定路径的字段，完全不影响其他内容。比如更新顶层的home_street_name：

UPDATE your_table
SET data = jsonb_set(data, '{home_street_name}', '"Updated Moran Rd"'::jsonb)
WHERE contact_id = 111231;

要是需要同时更新多个字段，可以嵌套调用jsonb_set，不过用||操作符会更简洁。

根据你的contacts表结构（只有contact_id和data jsonb列），我们需要把原语句中更新的独立字段转换成jsonb对象，再合并到data列里，同时保留原有字段。

重构后的最终语句： #

UPDATE contacts AS c
SET data = c.data || jsonb_build_object(
    'latitude', v.latitude,
    'longitude', v.longitude,
    'home_house_num', v.home_house_num,
    'home_predirection', v.home_predirection,
    'home_street_name', v.home_street_name,
    'home_street_type', v.home_street_type
)
FROM (
    VALUES 
        (16247746, 40.814140, -74.259250, '25', null, 'Moran', 'Rd'),
        (16247747, 20.900840, -156.373700, '581', 'South', 'Pili Loko', 'St')
) AS v(contact_id, latitude, longitude, home_house_num, home_predirection, home_street_name, home_street_type)
WHERE c.contact_id = v.contact_id;

关键说明： #

• jsonb_build_object会把你传入的键值对转换成一个jsonb对象，对应你要更新的那些字段。
• ||操作符的作用是将这个新的jsonb对象和原data列合并：原data里的email、firstname等未指定的字段会完整保留，新对象中的字段会覆盖原data里的同名字段（如果有的话），没有的字段则会被添加进去。
• 如果你不想把null值的字段（比如第一行的home_predirection）写入jsonb，可以用jsonb_strip_nulls函数包裹jsonb_build_object，自动移除值为null的键值对：

SET data = c.data || jsonb_strip_nulls(jsonb_build_object(
    'latitude', v.latitude,
    'longitude', v.longitude,
    'home_house_num', v.home_house_num,
    'home_predirection', v.home_predirection,
    'home_street_name', v.home_street_name,
    'home_street_type', v.home_street_type
))

这样就完美实现了你的需求：只更新指定字段，完全不影响data列中其他已存在的内容。

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