PostgreSQL单条SQL实现JSON字段加属性、转数组并追加对象

该需求可以在单条UPDATE语句中完成，基于你正在使用的SQL Server JSON函数组合即可实现，不需要分步更新。
核心实现逻辑分三步：

1. 用你已经写好的JSON_MODIFY逻辑给原有单个活动JSON对象补充effectiveTo字段
2. 用字符串拼接的方式把修改后的单个对象包装为单元素JSON数组
3. 用JSON_MODIFY的append语法，把新的活动JSON对象追加到数组末尾

可直接使用的代码 #

-- 单条更新完成全部需求
UPDATE event_races
SET activities = JSON_MODIFY(
    -- 将加完effectiveTo字段的原对象包装为数组
    CONCAT('[', JSON_MODIFY(activities, '$.effectiveTo', '2021-05-13T10:50:00Z'), ']'),
    -- 向数组末尾追加新对象
    'append $',
    -- 必须用JSON_QUERY包裹新JSON，避免被转义为普通字符串
    JSON_QUERY(N'{"event": "walking race", "effectiveFrom": "2021-05-13T10:50:00Z"}')
)

如果你是在应用代码中拼接SQL，只需要把上面写死的时间值、新对象JSON换成你传入的参数即可，比如你原来用的time.Now().UTC()可以直接作为参数传给第一个JSON_MODIFY的入参位置。

注意事项 #

• 追加新JSON对象时必须包裹JSON_QUERY函数，否则SQL Server会将JSON对象识别为普通字符串，存入后带转义符，破坏JSON结构
• 正式执行更新前，可以先替换为SELECT语句预览结果，确认输出格式符合预期再执行更新，预览语句参考：

SELECT 
    JSON_MODIFY(
        CONCAT('[', JSON_MODIFY(activities, '$.effectiveTo', '2021-05-13T10:50:00Z'), ']'),
        'append $',
        JSON_QUERY(N'{"event": "walking race", "effectiveFrom": "2021-05-13T10:50:00Z"}')
    ) AS preview_result
FROM event_races

• 如果需要做批量更新，只需要将写死的时间、新活动JSON替换为对应行的字段值即可，不需要编写循环逻辑

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