Question 90431 in PostgreSQL Question posted in
The official documentation can be found here.

How to replace existing key in jsonb? – Postgresql

I’m trying to update a jsonb array in Postgres by replacing the entire array. It’s important to note, I’m not trying to add an array to the object, but simply replace the whole thing with new values. When I try the code below, I get this error in the console

error: cannot replace existing key

I’m using Nodejs as server-side language.

server.js

//new array with new values
var address = {
    "appt": appt,
    "city": city,
    "street": street,
    "country": country,
    "timezone": timezone,
    "coordinates": coordinates,
    "door_number": door_number,
    "state_province": state_province,
    "zip_postal_code": zip_postal_code
  }

//query
var text = "UPDATE users SET info = JSONB_insert(info, '{address}', '" + JSON.stringify(address) + "') WHERE id=$1 RETURNING*";
var values = [userid];

//pool...[below]

users table

id(serial | info(jsonb)

And this is the object I need update

{
  "dob": "1988-12-29",
  "type": "seller",
  "email": "[email protected]",
  "phone": "5553766962",
  "avatar": "f",
  "address": [
    {
      "appt": "",
      "city": "Brandon",
      "street": "11th Street East",
      "country": "Canada",
      "timezone": "Eastern Standard Time",
      "coordinates": [
        "-99.925011",
        "49.840649"
      ],
      "door_number": "666",
      "state_province": "Manitoba",
      "zip_postal_code": "R7A 7B8"
    }
  ],
  "last_name": "doe",
  "first_name": "john",
  "date_created": "2022-11-12T19:44:36.714Z",
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I have found the answer for this. Going through some of my older apps I coded, I stumbled upon the answer. It's not JSONB_INSERT but JSONB_SET. Notice the difference. The later will replace the entire key and not insert or add to the object.

    JSONB_INSERT --> insert

    UPDATE users SET info = JSONB_insert(info, '{address,-1}', '" + JSON.stringify(address) + "',true) WHERE id=$1 RETURNING*

    JSONB_SET --> set and replace

    UPDATE users SET info = JSONB_SET(info, '{address}', '" + JSON.stringify(address) +"') WHERE id=$1 RETURNING*

  2. 0

    below works in db-fiddle Postgresql v15 (did not in work in v12)

    1. specific element
    update json_update_t set info['address'][0] = '{
  "appt": "12",
  "city": "crater",
  "street": "11th Street East",
  "country": "mars",
  "timezone": "Eastern Standard Time",
  "coordinates": [
    "-99.925011",
    "49.840649"
  ],
  "door_number": "9999",
  "state_province": "marsbar",
  "zip_postal_code": "abc 123"
}';
    1. whole array
    
update json_update_t set info['address'] = '[{
  "appt": "14",
  "city": "crater",
  "street": "11th Street East",
  "country": "mars",
  "timezone": "Eastern Standard Time",
  "coordinates": [
    "-99.925011",
    "49.840649"
  ],
  "door_number": "9999",
  "state_province": "marsbar",
  "zip_postal_code": "abc 123"
}]';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search