Aggregate concatenate jsonb arrays

user_750216 0
Viewed 387

Having a table named example_table with one column named example_column of type JSONB and every value in the column being an array.

Having the values in 2 rows be: [1, 2] and [3]

How can I aggregate-concatenate the values in example_column?

The result should be: [1, 2, 3]

I tried using:

select json_agg(example_column) from example_table

but that returns [[1, 2,], [3]]

jsonb postgresql
2 Answers

Use the function jsonb_array_elements(example_column), example:

with example_table(example_column) as (
values
    (jsonb '[1, 2]'),
    (jsonb '[3]')
)

select jsonb_agg(value)
from example_table
cross join jsonb_array_elements(example_column) 

jsonb_agg 
-----------
 [1, 2, 3]
(1 row)

You can define the sort order of aggregated elements and/or remove duplicates, e.g.:

with example_table(id, example_column) as (
values
    (1, jsonb '[1, 2]'),
    (2, jsonb '[3]'),
    (3, jsonb '[3, 1]')
)

select 
    jsonb_agg(value order by id) as agg1,
    jsonb_agg(value order by value) as agg2,
    jsonb_agg(distinct value order by value) as agg3
from example_table
cross join jsonb_array_elements(example_column) 

      agg1       |      agg2       |   agg3    
-----------------+-----------------+-----------
 [1, 2, 3, 3, 1] | [1, 1, 2, 3, 3] | [1, 2, 3]
(1 row)
user_19957380

If you need to do that a lot, you could create your own aggregate for this: 

create function combine_jsonb_arrays(p_array_1 jsonb, p_array_2 jsonb) 
  returns jsonb
as
$$
  select jsonb_agg(t.val order by t.val)
  from (
    select *
    from jsonb_array_elements(p_array_1) as x1(val)
    union all
    select *
    from jsonb_array_elements(p_array_2) as x2(val)
  ) t;
$$
language sql;

create aggregate jsonb_elements_agg(jsonb)
(
  sfunc = combine_jsonb_arrays,
  stype = jsonb
);

Then you can use it like this:

select jsonb_elements_agg(example_column)
from example_table;
user_3303150
Related