UPDATE a JSONB object, adding record to array

Roachers, please help. I and a colleague are totally vexed by this problem.

We have records in a JSONB field that look like so:

'{"pattern": "123", "subscribers": [{"user": "1111", "address": "one@example.com", "method": "email"}]}'

We want to simply add a new subscriber record to the array inside the JSONB object.

'{"pattern": "123", "subscribers": [{"user": "1111", "address": "one@example.com", "method": "email"}, {"user": "2222", "address": "two@example.com", "method": "email"}]}'

We have attempted this with JSON_SET, concat ||, etc. Nothing works and the CRDB documentation and Google offerings just don’t reveal this mystery. So over to this awesome community for help. Please && thank you!

Hi @DaveA, this is an interesting case to be sure. Here is what I came up with after a bit of trial and error. I’m sure there is something more concise that you could do, but it should give you an idea of the raw building blocks.

root@localhost:26257/defaultdb> create table a (a int primary key, b json);
CREATE TABLE

root@localhost:26257/defaultdb> insert into a values(1, '{"pattern": "123", "subscribers": [{"user": "1111", "address": "one@example.com", "method": "email"}]}'

);
INSERT 1

root@localhost:26257/defaultdb> with b(subscribers) as (select jsonb_array_elements(b->'subscribers') from a where a=1),
     c(subscribers) as (select * from b union all select '{"user": "2222", "address": "two@example.com", "method": "email"}'::json),
     d(arr) as (select jsonb_agg(subscribers) from c)
     update a set b = jsonb_set(b, array['subscribers'], (select arr from d)) where a=1;
UPDATE 1

Time: 33ms total (execution 33ms / network 0ms)

root@localhost:26257/defaultdb> select * from a;
  a |                                                                   b
----+-----------------------------------------------------------------------------------------------------------------------------------------
  1 | [{"address": "one@example.com", "method": "email", "user": "1111"}, {"address": "two@example.com", "method": "email", "user": "2222"}]
(1 row)

Time: 1ms total (execution 1ms / network 0ms)

The idea is that we first decompose the JSON array into a SQL result set using jsonb_array_elements. Then, we UNION the new array element you want to add, adding it to the result set. Then, we aggregate the result set back up into a JSON array using jsonb_agg. Finally, we use UPDATE with a json_set to set the subscribers array on the original row to the JSON array from the previous step.

Hope this helps!

Jordan

Whoops, my original post contained an error (didn’t use jsonb_set on the final update), but it’s been edited now to correct the error.

@Jordan Outstanding solution. Thank you so much! I was close with the use of jsonb_set. The Unrolling of the array with the select jsonb_array_elements() call was a brilliant move.

Makes me think something like a jsonb_array_append(element, path, new data) function would be an awesome addition some day…to simplify this potentially common place task. Thanks again!

Agreed, this is a total pain in the neck!