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
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)