Node-PostgreSQL JSONB：修复无效JSON输入语法与值匹配问题

问题描述 #

执行更新jsonb字段的参数化查询时，触发如下错误：

error: invalid input syntax for type json. detail: 'Token "order_cbs1l" is invalid.' where: 'JSON data, line 1: order_cbs1l'

尝试用JSON.stringify(laveid)修复时，发现值被自动添加双引号，导致order_cbs1l !== "order_cbs1l"，WHERE条件无法匹配目标记录。

表结构 #

id(serial) | info(jsonb)

现有代码（server.js） #

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

// 序列化对象
var cleaner = JSON.stringify(contractorInfo);

// 目标ID
var laveid = 'order_cbs1l';

// 查询语句
var text02 ="UPDATE users SET info = JSONB_SET(info, '{schedule,0,cleaner}', $2) WHERE info->'schedule'->0->>'id'=$1 RETURNING*";
var value02 = [cleaner,laveid];

// 连接池操作...

数据库中目标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"
}

解决方案 #

问题根源有两点：

1. 参数类型与顺序错误：JSONB_SET需要接收jsonb类型参数，但你传入了字符串化后的JSON文本；同时参数数组value02的顺序和查询语句中的$1、$2对应关系颠倒。
2. 不必要的手动序列化：Node的PostgreSQL客户端（如pg模块）会自动将JS对象序列化为jsonb类型，无需手动调用JSON.stringify。

修正后的代码：

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

// 目标ID
var laveid = 'order_cbs1l';

// 查询语句保持不变
var text02 ="UPDATE users SET info = JSONB_SET(info, '{schedule,0,cleaner}', $2) WHERE info->'schedule'->0->>'id'=$1 RETURNING*";
// 调整参数顺序，直接传入JS对象而非序列化后的字符串
var value02 = [laveid, contractorInfo];

// 连接池执行查询...

如果客户端不支持自动序列化JSON对象，可在查询语句中显式转换参数类型：

UPDATE users SET info = JSONB_SET(info, '{schedule,0,cleaner}', $2::jsonb) WHERE info->'schedule'->0->>'id'=$1 RETURNING*

另外说明：WHERE子句中info->'schedule'->0->>'id'提取的是文本类型的ID值，直接传字符串laveid即可正确匹配数据库中的"order_cbs1l"，不需要额外处理。

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