Changing a single element of an json array in PostgreSQL

Question

First of all I'd like to say that I don't have much experience but I started learning dbs after having a couple of subjects related to dbs at my university (so please keep that in mind while you're reading this).

I already asked this question on stackoverflow but I didn't get a conclusive answer so I thought it might be a good idea to ask the same question here since this site is focused solely on databases and their ins and outs.

I want to change a single element of a nested array using the UPDATE, DELETE or INSERT commands. In this case I wanted to update a single element of an array by swapping out one of the 'lastName' values for another using the UPDATE command (the syntax I'm using is probably wrong but that's the most I could work out from what I've read in the official documentation). I used dbfiddle to test out if what I am doing would work before putting anything into the real database as to not make any unnecessary mistakes. Here is what I have written in dbfiddle https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ecc2329efea6af28636b3537d46b6c01

I'd be grateful if someone could clarify what I'm doing wrong. If you have any suggestions on how this sort of thing should be done, I'd be more than happy to get some constructive feedback.

Answer 1

I don't think this can be done using jsonb_set().

The second parameter for that function requires a "target path". As podaci contains an array, you need to include the array position, e.g. jsonb_set(podaci, '{1,lastName}', '"Kirin"') - however, there is no easy way (I can think of) to get the index position that you need.

So you will need to iterate through the elements, change the one you want and aggregate back. This can be done with a sub-query in the assignment of the UPDATE statement:

update test_tablica1
   set podaci = (select jsonb_agg(case 
                                   when element @> '{"firstName": "Maria"}' 
                                     then element||'{"lastName": "Kirin"}' 
                                   else element
                                  end)
                 from jsonb_array_elements(podaci) as x(element))
where podaci @> '[{"firstName": "Maria"}]';                 

jsonb_agg() aggregates the elements that are returned by jsonb_array_elements() back into an array. The CASE expression will then check each element an if it contains the firstname Maria, the lastname will be replaced (appending a JSONB value to a JSONB value, replaces the existing keys)

The WHERE clause prevents updating rows that don't need updating

Online example

---

However, your model really looks like a mis-use of JSON in the database. It would be more efficient to store the names in a proper relational table with a one-to-many relationship to the base table (test_tablica1 in your example)

Something like:

create table test_tablica1 
(
  id int primary key generated always as identity, 
  ... other columns ...,
  datum_upisa timestamp
);

create table names
(
  id int not null, 
  tablica1_id int not null references test_tablica1,
  firstname text, 
  lastname text
);

insert into test_tablica1 (id) values (1);
insert into names (id, tablica1_id, firstname, lastname)
values
(1, 'Tom', 'Cruise'),
(2, 'Maria', 'Sharapova'),
(3, 'James', 'Bond')
;

Then the update becomes as simple as:

update names
   set lastname = 'Kirin'
where firstname = 'Maria'
  and tablica1_id = 1 
;

The condition tablica1_id = 1 is just an example. Add whatever condition is needed, to restrict the name change to rows that are linked to a specific row in the parent table.