Conditionally replace object in a JSON array in jsonb

Question

I have a jsonb column in my table that looks like this:

[
   {
     "id": "1231-213-331",
     "name" : "Object 1 Name"
   },
   {
     "id": "4113-13-131-21",
     "name" : "Object 2 Name"
   }
]

I am passing a json object to my Postgres function:

{
  "id": "1231-213-331",
  "name" : "New Name For Object One"
}

With this, I would like to entirely replace the existing json object within the jsonb object with a matching id (which is unique).

I've tried concatenation via || and using the jsonb_set function, which apparently doesn't exist. (I'm using Postgres 15.4)

How can this be done?

Answer 1

you can go with exploding array as other answers suggest or you can use jsonb_path_query_array to remove the value from the array and then concat via '||' with the value:

do $$
declare
    data jsonb;
    new_value jsonb;
begin
    data := '[
      {"id":"1231-213-331", "name":"Object 1 Name"},
      {"id":"4113-13-131-21", "name":"Object 2 Name"}
    ]';

    new_value = '{"id": "1231-213-331", "name" : "New Name For Object One"}';

    data := jsonb_path_query_array(data, '$[*] ? (@.id != $id)', new_value) || new_value;

    raise notice '%', data;
end $$;


[
    {"id": "4113-13-131-21", "name": "Object 2 Name"},
    {"id": "1231-213-331", "name": "New Name For Object One"}
]