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

Postgresql Replace JSON Object With Key Value Within JSONB With Unknown Index Number

I have a jsonb column within 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 into my postgresql 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_setfunction, which apparently doesn’t exist (I’m using postgres 15.4)

How can this be done?

Tags:

2

Answers


  1. 0

    I would find the match, turn it into a text[], and then user jsonb_set() to perform the replacement:

    with invars as (
  select '[
   {
     "id": "1231-213-331",
     "name" : "Object 1 Name"
   },
   {
     "id": "4113-13-131-21",
     "name" : "Object 2 Name"
   } 
]'::jsonb as jdata, 
   '{
  "id": "1231-213-331",
  "name" : "New Name For Object One"
}'::jsonb as newobj
), findindex as (
  select ARRAY[(n - 1)::text] as jpath
    from invars i
         join lateral jsonb_array_elements(i.jdata) with ordinality e(j, n)
           on e.j->'id' = i.newobj->'id'
)
select jsonb_set(i.jdata, f.jpath, i.newobj) as result
  from findindex f
       cross join invars i;

    working fiddle

    Login or Signup to reply.
  2. 0

    Your error is because function parameters must be jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ). Simulating your function with an anonymous block it would be something like this

    do $$
declare
 in_object jsonb; 
begin
    in_object = '{
                    "id": "1231-213-331",
                    "name" : "New Name For Object One"
                }'::jsonb; 
    update a set jsonb_column = jsonb_set(jsonb_column, jbpath, in_object)
    from (
        select id, array[(e.i-1)::text] jbpath
        from a
        , jsonb_array_elements(jsonb_column) with ordinality e(cont, i)
        where e.cont->'id' = in_object->'id') sq
    where a.id = sq.id;    
end $$;

    Fiddle to test

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search