JSONB Postgresql - How to update array attribute in a nested object?

Question

I need to update an array in jsonb postgresql in nested object. I can't seem to have this working. I have tried many different things so far without success. I am trying to update the cleaner array by comparing with the schedule id.

table

id(serial) | info(jsonb)

server.js

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

//id
var laveid = 'order_cbs1l';

Returns null doesn't update

"UPDATE users SET info = JSONB_SET(info, '{schedule,cleaner}', '"+JSON.stringify(contractorInfo)+"') WHERE info->'schedule'->>'id'='"+laveid+"' RETURNING*"

Returns null doesn't update

"UPDATE users SET info = JSONB_SET(info, '{schedule,cleaner}', '"+JSON.stringify(contractorInfo)+"') WHERE info #>> '{schedule,id}' = '"+laveid+"' RETURNING*"

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"
}

Your code is susceptible to SQL injections! Stop using string concatenation and use a parameterised query instead. — Bergi
– Bergi, Commented Dec 28, 2022 at 22:33
You'll need to use SET info = JSONB_SET(info, '{schedule,0,cleaner}', $2) WHERE info->'schedule'->0->>'id'=$1 if you want to test and update the first object in the array. What do you want to do if the array is empty or has multiple members? — Bergi
– Bergi, Commented Dec 28, 2022 at 22:36
I get this error with your codes : error: invalid input syntax for type json. detail: 'Token "order_cbs1l" is invalid.' where: 'JSON data, line 1: order_cbs1l'... So I tried to solve this with JSON.stringify(laveid) but JSON.stringify adds double quotes around the value and so the program cannot find it. order_cbs1l !== "order_cbs1l" — Grogu
– Grogu, Commented Dec 29, 2022 at 15:54

Nathan Erkamp · Accepted Answer · 2023-01-01 11:31:40Z

The problem that I can see is that your where class does not match the row in question. Since "schedule" is an array you should substitute

WHERE info->'schedule'->>'id'='"+laveid+"'

for

WHERE info->'schedule'-> 0 ->>'id'='"+laveid+"'

since this would reference the first element in the "schedule" array. Since the array only has one element you might also want to consider removing the array altogether by storing the json as

{
  "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"
}

In which case the original where clause would work.

Collectives™ on Stack Overflow

JSONB Postgresql - How to update array attribute in a nested object?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related