PostgreSQL JSONB：如何更新嵌套对象中的数组属性？

我尝试更新PostgreSQL的jsonb字段中嵌套数组内对象的cleaner属性，但执行SQL后返回null且没有任何更新操作。需求是根据schedule的id值，更新对应项里的cleaner对象。

表结构 #

id(serial) | info(jsonb)

server.js 代码片段 #

var contractorInfo = {
    "id": cleanerid,
    "fname": fname,
    "lname": lname,
    "avatar": avatar
}

// 目标schedule的id
var laveid = 'order_cbs1l';

执行后无更新且返回null的SQL语句 #

UPDATE users SET info = JSONB_SET(info, '{schedule,cleaner}', '"+JSON.stringify(contractorInfo)+"') WHERE info->'schedule'->>'id'='"+laveid+"' RETURNING*

UPDATE users SET info = JSONB_SET(info, '{schedule,cleaner}', '"+JSON.stringify(contractorInfo)+"') WHERE info #>> '{schedule,id}' = '"+laveid+"' RETURNING*

示例JSON数据 #

{
  "dob": "1988-12-11",
  "type": "seller",
  "email": "johndoe@gmail.com",
  "phone": "5553766962",
  "avatar": "image.png",
  "schedule": [
    {
      "id": "order_cbs1l",
      "pay": "230",
      "date": "2022-12-29",
      "status": "Available",
      "address": "234 Eleventh Street, Mildura Victoria 3500, Australia",
      "cleaner": {
        "id": "",
        "fname": "",
        "lname": "",
        "avatar": ""
      },
      "end_time": "10:15",
      "start_time": "01:00",
      "total_hours": "33300000",
      "paymentIntentId": "pi_3KJnrEFzZWeJoxzV1yUdGLQ8"
    }
  ],
  "last_name": "doe",
  "first_name": "john",
  "countrycode": "Canada: +1",
  "countryflag": "iti__ca",
  "date_created": "2022-11-12T19:44:36.714Z"
}

问题核心在于schedule是数组而非单个对象，之前的SQL既没有定位到数组中具体元素的索引，WHERE条件的写法也错误——直接访问info->'schedule'->>'id'是操作整个数组，无法匹配到数组内对象的id值。

正确写法（推荐参数化查询防注入） #

在Node.js中不要直接拼接SQL字符串，改用参数化查询：

const query = `
  UPDATE users 
  SET info = jsonb_set(
    info,
    array['schedule', (idx - 1)::text, 'cleaner'],
    $1::jsonb
  )
  FROM (
    SELECT id, jsonb_array_elements(info->'schedule') WITH ORDINALITY AS s(sched, idx)
    FROM users
    WHERE s.sched->>'id' = $2
  ) AS sub
  WHERE users.id = sub.id
  RETURNING *;
`;

// 执行参数化查询
client.query(query, [contractorInfo, laveid], (err, res) => {
  if (err) throw err;
  console.log(res.rows);
});

逻辑说明 #

1. 子查询定位数组元素：通过jsonb_array_elements(info->'schedule') WITH ORDINALITY遍历schedule数组，获取每个元素及其索引（idx），筛选出id匹配的目标元素。
2. jsonb_set路径修正：数组索引从0开始，所以用idx-1转换为JSON路径的索引，完整路径为['schedule', 索引位置, 'cleaner']。
3. 参数化查询：直接传入contractorInfo（pg客户端会自动转为jsonb）和laveid，彻底避免SQL注入风险。

临时字符串拼接写法（不推荐生产环境使用） #

如果必须用字符串拼接，修正后的SQL如下：

UPDATE users 
SET info = jsonb_set(
  info,
  array['schedule', (idx - 1)::text, 'cleaner'],
  '${JSON.stringify(contractorInfo)}'::jsonb
)
FROM (
  SELECT id, jsonb_array_elements(info->'schedule') WITH ORDINALITY AS s(sched, idx)
  FROM users
  WHERE s.sched->>'id' = '${laveid}'
) AS sub
WHERE users.id = sub.id
RETURNING *;

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