1

I got the following error

error: invalid input syntax for type 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
]);

whereby trace is the following object:

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

I do not know what I am doing wrong. I think it has smt to do with inserted values. Thanks for the help.

1 Answer 1

1

You're missing the quotes around the JSON strings. This should work:

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)
]);

However, I would rather pass whole JSON(B) values to the query, and let node-pg do the escaping:

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"]}
]);

Or using 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"]}
]);
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.