Update JSONB object in array without specifying index in PostgreSQL

Question

Is there a way to update a JSONB value in an array of array without having to specify the index (which is not always reliable) but rather by property inside one object?

Lets say I have this JSONB value:

select '[{"foo": [{"id": "baz"}, {"id": "bar"}]}, {"foo": [{"id": "bor"}]}]'::jsonb;

I can find the element that has the id bar with this query:

select * from x where f @> '[{"foo": [{"id": "bar"}]}]'::jsonb:

But I can't just say: Update the object where ID is bar and change it without specifying its index:

UPDATE x SET f = jsonb_set(f, '{0,foo,1}', '{"id": "new-bar", "something": "hello"}'::jsonb);

Is there a way to update a portion of JSONB by using a property of this nested object without having to specify an index (i.e position)?

Side note: the problem with the index position is that it can vary between queries and it's not readable when reading logs.

Not sure it's clear enough, please let me know if I can improve my question!

Does [Add property to objects in jsonb array](https://stackoverflow.com/q/63758141/1048572) help? (In your case you seem to have two nested arrays though) — Bergi, Sep 28 '20 at 18:46

score 1 · Accepted Answer · answered Sep 29 '20 at 12:13

One option would be applying JSONB_ARRAY_ELEMENTS() function along with CROSS JOINs recursively upto the point where the deepest array element reached.

And then matching '{"id": "bar"}::JSONB' as search identifier :

WITH x0 AS
(
 SELECT j
   FROM x
  CROSS JOIN JSONB_ARRAY_ELEMENTS(f) AS j 
), x1 AS
(
 SELECT ('{foo,'||index-1||'}')::text[] AS path, x0.*
   FROM x0
  CROSS JOIN JSONB_ARRAY_ELEMENTS((j->>'foo')::JSONB) 
   WITH ORDINALITY arr(j1,index)
  WHERE j1 = '{"id": "bar"}'::JSONB   
), x_upd AS
(
SELECT JSONB_AGG(
                 JSONB_SET(x1.j,x1.path,'{"id": "new-bar", "something": "hello"}',false)
       ) AS js_agg
  FROM x1
)
UPDATE x
   SET f = js_agg
  FROM x_upd

Demo

Update JSONB object in array without specifying index in PostgreSQL

1 Answers1