PostgreSQL：如何更新嵌套JSONB中的指定属性值

基础更新查询（针对launches数组第一个元素） #

如果只需更新目标cars元素下launches数组的第一个元素的nextUpdate值，可使用以下语句：

UPDATE my_table
SET details = jsonb_set(
    details,
    ARRAY[
        'cars',
        -- 获取符合条件的cars元素的索引
        (SELECT idx::text 
         FROM jsonb_array_elements(details->'cars') WITH ORDINALITY arr(car, idx)
         WHERE car->>'id' = 'b_1111' AND car->>'type' = 'SportsCar'),
        'lauches',
        '0',  -- 定位launches数组的第一个元素（索引从0开始）
        'nextUpdate'
    ],
    '"2026"'::jsonb  -- 替换为你需要的新值，注意转成jsonb类型
)
WHERE
    details->>'id' = 'V_12345'
    -- 确保当前行存在符合条件的cars元素，避免无效更新
    AND EXISTS (
        SELECT 1 
        FROM jsonb_array_elements(details->'cars') car
        WHERE car->>'id' = 'b_1111' AND car->>'type' = 'SportsCar'
    );

精准更新launches中特定id的元素 #

如果需要更新launches数组中指定id的元素（比如id='S_2112'），可通过嵌套子查询定位索引：

UPDATE my_table
SET details = jsonb_set(
    details,
    ARRAY[
        'cars',
        -- 获取目标cars元素的索引
        (SELECT idx::text 
         FROM jsonb_array_elements(details->'cars') WITH ORDINALITY arr(car, idx)
         WHERE car->>'id' = 'b_1111' AND car->>'type' = 'SportsCar'),
        'lauches',
        -- 获取目标launches元素的索引
        (SELECT idx::text 
         FROM jsonb_array_elements(
             (details->'cars'->(SELECT idx-1 
                                FROM jsonb_array_elements(details->'cars') WITH ORDINALITY arr(car, idx)
                                WHERE car->>'id' = 'b_1111' AND car->>'type' = 'SportsCar'))->'lauches'
         ) WITH ORDINALITY arr(launch, idx)
         WHERE launch->>'id' = 'S_2112'),
        'nextUpdate'
    ],
    '"2026"'::jsonb
)
WHERE
    details->>'id' = 'V_12345'
    -- 确保同时存在符合条件的cars和launches元素
    AND EXISTS (
        SELECT 1 
        FROM jsonb_array_elements(details->'cars') car
        WHERE car->>'id' = 'b_1111' AND car->>'type' = 'SportsCar'
        AND EXISTS (
            SELECT 1 
            FROM jsonb_array_elements(car->'lauches') launch
            WHERE launch->>'id' = 'S_2112'
        )
    );

关键细节说明 #

• ->> vs ->：->>用于提取jsonb字段的文本值（用于条件判断），->用于保留jsonb类型（用于数组操作）。
• jsonb_set函数：核心用于修改jsonb对象的指定路径，路径数组中的每一项对应json结构的层级。
• WITH ORDINALITY：配合jsonb_array_elements展开数组时，同时获取元素的索引（从1开始，定位json数组时需减1，因为json数组索引从0开始）。
• 条件过滤：通过EXISTS子查询确保只对存在目标元素的行执行更新，避免无意义操作。

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