Convert postgres array to jsonb

Question

How we can convert postgres array :- ARRAY['{"id": "1"}', '{"id": "2"}'] to JSONB '[{"id": "1"}, {"id": "2"}]'

Did you try using one of the functions here? https://www.postgresql.org/docs/11/functions-json.html — András Váczi, Apr 09 '19 at 14:47

score 14 · Answer 1 · answered Jun 06 '20 at 19:33

14

The array_to_json function with a cast to jsonb accomplishes this.

Example: select array_to_json(ARRAY['{"id": "1"}', '{"id": "2"}'])::jsonb;

answered Jun 06 '20 at 19:33

Mark Douglas

This is by far the better way to do this! – Rohanthewiz Sep 06 '22 at 20:47
This is strange. Do you know why there is no array_to_jsonb function? – cemper93 Aug 01 '23 at 09:48

score 3 · Accepted Answer · answered Apr 09 '19 at 15:40

3

It appears that you have an array of JSON values, and want to turn that into a single JSONB value which is an array.

You can unnest your array, then cast the elements to JSONB and aggregate that back using jsonb_agg():

select jsonb_agg(j::jsonb)
from unnest(ARRAY['{"id": "1"}', '{"id": "2"}']) as x(j);

answered Apr 09 '19 at 15:40

2 Answers2