使用JSONB_SET方法时出现SequelizeDatabaseError: JSON类型输入语法无效

问题原因 #

报错invalid input syntax for type json是因为传入jsonb_set的值参数未被正确序列化为JSON格式，或是直接拼接字符串引发语法错误/转义问题。

修复方案 #

方案1：使用Sequelize ORM方法（推荐） #

用Sequelize.literal包裹参数，确保值被识别为合法JSON，同时规范路径参数格式：

await campaigns.update(
  {
    order_data: Sequelize.fn(
      "JSONB_SET",
      Sequelize.col('order_data'),
      Sequelize.literal(`'{advertiser,advertiserUrl}'`),
      Sequelize.literal(`'${JSON.stringify(advertiserUrl)}'`),
      true // 可选参数：路径不存在时自动创建，默认true可省略
    )
  },
  {
    where: { order_id: orderId }
  }
);

通过JSON.stringify将URL转为合法JSON字符串，避免单引号等特殊字符破坏SQL语法。

方案2：使用参数绑定的原生查询（规避SQL注入） #

直接拼接字符串不仅会触发语法错误，还存在注入风险，改用参数绑定方式：

const query = `
  UPDATE tbl_campaigns 
  SET order_data = jsonb_set(order_data::jsonb, '{advertiser,advertiserUrl}', $1::jsonb, true) 
  WHERE order_id = $2
`;
await connection.query(query, {
  bind: [JSON.stringify(advertiserUrl), orderId],
  raw: true
});

通过$1、$2绑定参数，让Sequelize自动处理转义和类型转换，同时保证值为合法JSON格式。

关键注意点 #

• 传入jsonb_set的值必须是合法JSON格式：字符串需用双引号包裹（JSON.stringify会自动处理），不能直接传递裸字符串。
• 路径参数要以单引号包裹的数组格式（'{advertiser,advertiserUrl}'）传入，确保PostgreSQL识别为JSON路径。
• 禁止直接拼接SQL字符串，必须使用参数绑定防止注入和语法错误。

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