使用JSONB_SET向嵌套JSON数组的对象插入新字段报错求助

首先，你遇到的ERROR: path element at position 1 is not an integer: "activities"问题很明确——jsonb_set的路径参数写错了。

错误原因分析 #

你操作的m.activities本身就是一个JSON数组，而不是一个包含activities键的对象。所以路径里不需要加'activities'这个元素，JSON数组只能用整数索引来定位元素，你加了字符串"activities"作为路径开头，PostgreSQL自然会报错说路径元素不是整数。

修正后的SQL语句 #

这里给你调整好的查询，直接就能用：

UPDATE log_table m
SET activities = jsonb_set(
    m.activities::jsonb,
    -- 路径从外层数组索引开始，定位到目标items元素
    array[(pos1 - 1)::text, 'items', (pos - 1)::text, 'newId'],
    (obj->>'id')::jsonb
)::json
FROM log_table l
CROSS JOIN jsonb_array_elements(l.activities::jsonb) WITH ORDINALITY arr1(elems, pos1)
CROSS JOIN jsonb_array_elements(
    -- 把null的items转为空数组，避免丢失外层activity元素
    COALESCE(elems->'items', '[]'::jsonb)
) WITH ORDINALITY arr(obj, pos)
WHERE (obj->>'id')::int = 387015007 
  AND l.id = m.id;

关键修正点说明 #

1. 路径参数修正：去掉了原路径中的'activities'，直接从外层数组的索引（pos1-1，因为WITH ORDINALITY返回的序号是从1开始的，而JSON数组索引从0开始）开始，接着定位到items数组的对应索引，最后指定要添加的newId字段。
2. 优化null处理：用COALESCE(elems->'items', '[]'::jsonb)替代原来的case语句，更简洁地处理items为null的情况，避免这种情况下cross join丢失外层的activity元素。
3. 索引对齐：确保pos1和pos都转成了JSON数组的0-based索引，保证定位到正确的元素。

执行这个查询后，就能得到你期望的结果——给id=387015007的items对象添加newId字段，值和id一致。

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