PostgreSQL: Add object to jsonb array by function

Question 1

I'm having thisjsonb 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 aboutERROR: column "bar" does not exist

I'm using Postgres 9.5

Question 2

What version of postgres are you targeting?

Question 3

@teppic Sorry, forgot to add, I'm using Postgres 9.5

Question 4

Use the concatenation operator on elements got fromjsonb_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)

Question 5

Nicely done, it works! I've been coming close but never used json_agg before so it was quite confusing. Thank you very much!

klin 123k15 gold badges240 silver badges262 bronze badges · Accepted Answer · 2017-11-01 22:20:34Z

Use the concatenation operator on elements got fromjsonb_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!

Movatterモバイル変換

Collectives™ on Stack Overflow

PostgreSQL: Add object to jsonb array by function

1 Answer1

1 Comment

Your Answer

Sign up orlog in

Post as a guest

Linked

Related

Hot Network Questions

Subscribe to RSS