错误: json postgreSQL NodeJS类型的输入语法无效

Question

我得到了以下错误

错误: json类型的输入语法无效

await this.db.query(
  `UPDATE guest_group
  SET custom_fields = jsonb_insert(custom_fields, '{0}', '{"value": $3, "display_name": "Traces", "servicio_tags": ["trace"]}'::jsonb, true)
  WHERE '{"value": $1}' <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["full-name"]'::jsonb)
  AND '{"value": $2}' <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["room-number"]'::jsonb)`,[
   trace.guestName,
   trace.guestRoomNumber,
   trace.value
]);

因此，trace是以下对象：

AppTrace {
  value:'test',
  guestName: 'Bortolotti, Ingrid',
  guestRoomNumber: '422',
}

我不知道我做错了什么。我认为它与插入值有关。谢谢你的帮助。

Answer 1

您忽略了JSON字符串周围的引号。这应该是可行的：

await this.db.query(
  `UPDATE guest_group
  SET custom_fields = jsonb_insert(custom_fields, '{0}', '{"value": $3, "display_name": "Traces", "servicio_tags": ["trace"]}'::jsonb, true)
  WHERE '{"value": $1}' <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["full-name"]'::jsonb)
  AND '{"value": $2}' <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["room-number"]'::jsonb)`,[
   JSON.stringify(trace.guestName),
   JSON.stringify(trace.guestRoomNumber),
   JSON.stringify(trace.value)
]);

但是，我宁愿将整个JSON(B)值传递给查询，并让节点-pg执行转义操作：

await this.db.query(
  `UPDATE guest_group
  SET custom_fields = jsonb_insert(custom_fields, '{0}', $3::jsonb, true)
  WHERE $1 <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["full-name"]'::jsonb)
  AND $2 <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["room-number"]'::jsonb)`,[
   {"value": trace.guestName },
   {"value": trace.guestRoomNumber },
   {"value": trace.value, "display_name": "Traces", "servicio_tags": ["trace"]}
]);

或者使用the other query

await this.db.query(
  `UPDATE guest_group
  SET custom_fields = jsonb_insert(custom_fields, '{0}', $3::jsonb, true)
  WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(custom_fields) AS el
    WHERE el->>'value' = $1
      AND el->'servicio_tags' = '["full-name"]'::jsonb)
  AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(custom_fields) AS el
    WHERE el->>'value' = $2
      AND el->'servicio_tags' = '["room-number"]'::jsonb)
`, [
   trace.guestName,
   trace.guestRoomNumber,
   {"value": trace.value, "display_name": "Traces", "servicio_tags": ["trace"]}
]);