PostgreSQL: Add object to jsonb array by function

Question

I'm having this jsonb array: [{"id": 1, "foo": false}, {"id": 1, "foo": true}]

I have been struggling trying to add another field to all the objects inside. This is the result I'm looking for:

[{"id": 1, "foo": false, "bar": true}, {"id": 1, "foo": true, "bar": true}]

I know I need to write a function but I'm kind of new to PostgreSQL so I'm not sure where to start. This is the closest thread I can find: Postgres/JSON - update all array elements but they're to update an existing object by the key.

Any help or point to the direction is much appreciated.

Edit:

I tried to modified to this function

create or replace function add_elements(arr jsonb)
returns jsonb language sql as $$
    select jsonb_agg(jsonb_build_object("bar", true))
    from jsonb_array_elements(arr) e(e)    
$$;

but PostgreSQL complained about ERROR: column "bar" does not exist

I'm using Postgres 9.5

What version of postgres are you targeting? – teppic Nov 01 '17 at 21:50 — teppic, Nov 01 '17 at 21:50
@teppic Sorry, forgot to add, I'm using Postgres 9.5 – Bao Nguyen Nov 01 '17 at 21:51 — Bao Nguyen, Nov 01 '17 at 21:51

score 1 · Accepted Answer · answered Nov 01 '17 at 22:20

Use the concatenation operator on elements got from jsonb_array_elements():

with my_table(arr) as (
values
    ('[{"id": 1, "foo": false}, {"id": 1, "foo": true}]'::jsonb)
)

select jsonb_agg(elem || '{"bar": true}'::jsonb)
from my_table,
jsonb_array_elements(arr) elem;

                                  jsonb_agg                                  
-----------------------------------------------------------------------------
 [{"id": 1, "bar": true, "foo": false}, {"id": 1, "bar": true, "foo": true}]
(1 row)

Your function may look like this:

create or replace function add_elements(arr jsonb, val jsonb)
returns jsonb language sql as $$
    select jsonb_agg(elem || val)
    from jsonb_array_elements(arr) elem    
$$;


with my_table(arr) as (
values
    ('[{"id": 1, "foo": false}, {"id": 1, "foo": true}]'::jsonb)
)

select add_elements(arr, '{"bar": true}')
from my_table;

                                add_elements                                 
-----------------------------------------------------------------------------
 [{"id": 1, "bar": true, "foo": false}, {"id": 1, "bar": true, "foo": true}]
(1 row)

Nicely done, it works! I've been coming close but never used json_agg before so it was quite confusing. Thank you very much! — Bao Nguyen, Nov 02 '17 at 07:48

PostgreSQL: Add object to jsonb array by function

1 Answers1