Node-PostgreSQL JSONB : How to fix Invalid input syntax for type json Token is invalid error?

Question

I've got a parameterized query using Node server side to do operations on a Postgresql database. When I try to update the table, I get following error

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

I tried to solve this with JSON.stringify(laveid) but problem is that JSON.stringify() adds double quotes around the value and so to the logic of the program order_cbs1l !== "order_cbs1l". The program cannot find a match if one value has double quotes and the other does not. Is there a solution for this?

table

id(serial) | info(jsonb)

server.js

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

  //stringify array
var cleaner = JSON.stringify(contractorInfo);

//id
var laveid = 'order_cbs1l';


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

//pool
[...]

UPDATE

Here I'm adding the row from the database.

json object

{
  "dob": "1988-12-11",
  "type": "seller",
  "email": "[email protected]",
  "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"
}

Seems you flipped the parameters. laveId should come first ($1), cleaner should come second ($2). — Bergi
– Bergi, Commented Dec 30, 2022 at 17:05
@Bergi : Good catch! I flipped them and no more error. But it's not returning anything returns empty [] and in the db; nothing is updated. Any idea? — Grogu
– Grogu, Commented Dec 30, 2022 at 17:12
Looks like WHERE info->'schedule'->0->>'id' = 'order_cbs1l' matches no rows in your table. I can't tell why that might be. — Bergi
– Bergi, Commented Dec 30, 2022 at 17:14
@Bergi: let me update with the object from the db.It is there. — Grogu
– Grogu, Commented Dec 30, 2022 at 17:15
How do you know that object exists in the database? Did you find that row using SELECT * FROM users WHERE info->'schedule'->0->>'id' = 'order_cbs1l'? How about SELECT * FROM users WHERE info->'schedule'->0->>'id' = $1? — Bergi
– Bergi, Commented Dec 30, 2022 at 17:22

egeorge · Accepted Answer · 2022-12-30 16:57:53Z

-1

I think you need single quotes around the $2 parameter.

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

answered Dec 30, 2022 at 16:57

egeorge

6823 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Node-PostgreSQL JSONB : How to fix Invalid input syntax for type json Token is invalid error?

UPDATE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related