'flatten/concat-aggregate JSONB array

I have a JSONB column that contains JSON arrays. I now need to get all distinct values from all rows into one single array.

Input:

id | values
-----------
1  | [x, y, z]
2  | [a, b, x]

Desired output:

result
---------------
[a, b, x, y, z]

I can't just use jsonb_agg with DISTINCT because that would return a 2-dimensional array, and I can't find any flatten or concat aggregate functions. How can I solve this?

arrayspostgresqlaggregate-functionsjsonb


Solution 1:[1]

If you don't care about distinct elements, then you could define you own aggregator

CREATE AGGREGATE jsonb_concat_agg (jsonb)
(
    sfunc = jsonb_concat,
    stype = jsonb,
    initcond = '[]'
);

Then it would be

SELECT jsonb_concat_agg(values);

The result would be

result
---------------
[a, b, x, x, y, z]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Gabriel Furstenheim

Related Questions