PostgreSQL：如何用单条语句修改JSON数组中JSON对象的字段

当然可以！针对你这种修改JSONB数组中特定元素字段的场景，PostgreSQL自带的工具就能搞定，一条UPDATE语句就足够了。

直接可用的更新语句 #

UPDATE your_table_name
SET players = jsonb_set(
    players,
    -- 精准定位到Joe的role字段
    array[(SELECT idx - 1 FROM jsonb_array_elements(players) WITH ORDINALITY arr(elem, idx) WHERE elem->>'name' = 'Joe')::integer, 'role'],
    '"user"'
)
WHERE game = 'chess'
-- 先确认这条记录里确实有Joe，避免无效更新
AND EXISTS (
    SELECT 1 FROM jsonb_array_elements(players) elem WHERE elem->>'name' = 'Joe'
);

关键部分解释 #

• jsonb_set：这是PostgreSQL修改JSONB数据的核心函数，三个参数分别是：原始JSONB数据、要修改的路径、新值。
• 路径定位逻辑：jsonb_array_elements(players) WITH ORDINALITY会把数组拆成单个元素并带上它们的序号（注意序号从1开始，但JSONB数组索引是从0开始的，所以要减1）。通过子查询找到Joe对应的索引，再加上'role'就精准指向了要修改的字段。
• WHERE条件：game = 'chess'确保只操作目标游戏的记录，EXISTS子句用来过滤掉没有Joe的行，避免对不需要的记录执行更新，提升效率。

执行这条语句后，chess游戏里Joe的role就会被改成user，其他记录完全保持原样，和你预期的结果一致。

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