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.

1 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
                 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)
(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.

  • I apologize for not explaining why I used json in this example. I was trying to make a "proof of concept". The data I have in that dbfiddle link was originally stored in a noSQL Couchbase cluster and I tried importing that data in the same format to an SQL DB (in this case PostgreSQL). That's probably the reason it doesn't make much sense. Since that Couchbase cluster has something similar but it has 300+ such entries in a single document I wanted to try storing it all in json/jsonb because I read that it has the capability to store large amounts of data within itself.
    – Leahim
    Aug 2, 2022 at 10:51
  • Do you think a modified version of the statement you wrote above could be used for the DELETE, INSERT and SELECT commands? @a_horse_with_no_name
    – Leahim
    Aug 2, 2022 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.