0

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": "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"
}
Grogu
  • 2,097
  • 15
  • 36
  • 1
    Seems you flipped the parameters. `laveId` should come first (`$1`), `cleaner` should come second (`$2`). – Bergi Dec 30 '22 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 Dec 30 '22 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 Dec 30 '22 at 17:14
  • @Bergi: let me update with the object from the db.It is there. – Grogu Dec 30 '22 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 Dec 30 '22 at 17:22
  • @Bergi: it's very bizarre because no matter what I try the SELECT and/or UPDATE returns null despite the value existing in my table. I'm checking in the database and yes it is there. Strange behavior. I've been going over possible problems and solutions for the past hours. Scratching my head! – Grogu Dec 30 '22 at 18:51
  • "*I'm checking in the database*" - how do you do that? Are you sure you're connected to the same database? Using the same role? – Bergi Dec 30 '22 at 19:08
  • @Bergi : I've built an app.Everything else works well at fetching and updating data back and fourth. So yes everything is connected to the same DB and same role. It's a software. And if I go check in the PgAdmin UI on the web, I can see the data is there. – Grogu Dec 30 '22 at 19:26
  • @Bergi: Its got to be the wrong query. Am I right? Else why would it return [ ] every time? – Grogu Dec 30 '22 at 20:29
  • @Bergi : WHERE info->'schedule'->1->>'id' = 'order_cbs1l' works. Does this mean I have to manually enter the position of the array in the object I want to fetch or update? This is not proficient... If the user has 100 appointments inside schedule... Everything needs to be dynamic! – Grogu Dec 30 '22 at 21:19
  • That's weird, [the docs](https://www.postgresql.org/docs/current/functions-json.html) state "*array elements are indexed from zero*" so `->0` really should have matched the object from your question. – Bergi Dec 30 '22 at 23:51
  • "*Does this mean I have to manually enter the position of the array in the object I want to fetch or update? Everything needs to be dynamic!*" - that's why on [your previous question](https://stackoverflow.com/questions/74945015/jsonb-postgresql-how-to-update-array-attribute-in-a-nested-object#comment132256424_74945015) I commented, asking what should happen the array is empty or has multiple members! Also which of the array elements will you want to update if they have multiple schedules?! While there are solutions to do this in JSON, you really should consider normalising your schema. – Bergi Dec 30 '22 at 23:54
  • @Bergi: I found a way around this. My strategy is to send the array position from client when iterating. It works 100% of the time so that's good for me. Curious. How would you have solved it? – Grogu Jan 03 '23 at 16:17
  • There's various ways to update nested arrays (with subqueries, similar to [this](https://stackoverflow.com/a/60115303/1048572)), but I would probably have normalised the database schema instead and stores a user's schedules in a separate table – Bergi Jan 03 '23 at 16:22

1 Answers1

-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*";
egeorge
  • 612
  • 3
  • 11