postgresql: Aggregating arrays of text

Question

I want to get an array of all words associated with each id in this example data:

id | words
---|------------------
1  | {foo,bar,zap,bing}
2  | {foo}
1  | {bar,zap}
2  | {bing}
1  | {bing}
2  | {foo,bar}

Outputs:

id | allwords
---|--------------------------------
1  | {foo,bar,zap,bing,bar,zap,bing}
2  | {foo,bing,foo,bar}

I tried using array_agg(words) but it yields:

ERROR: could not find array type for data type text[]

What's the proper approach here? I want all words, even the duplicates.

Community · Accepted Answer · 2017-05-23 10:25:51Z

6

array_agg collects the results of a column into an array; it doesn't aggregate arrays.

array_cat is the functionality you want, but it isn't an aggregate function.

To define your own aggregate based on it, use this code:

CREATE AGGREGATE array_cat_aggregate (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

which I blatantly copied from this answer: https://stackoverflow.com/a/11763245/1394393. (I don't think the question is a duplicate.)

Pretending the table is called temp, then you can select using the function with a GROUP BY:

SELECT id, array_cat_aggregate(words)
FROM temp
GROUP BY id;

Here's a SQL Fiddle for you: http://sqlfiddle.com/#!12/7c828/1/0

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered May 22, 2013 at 2:50

jpmc26

30.2k14 gold badges100 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anthony Accioly · Accepted Answer · 2013-05-22 03:02:02Z

2

As an alternative to jpmc26 solution you can also combine array_agg with a subquery that unnest your arrays:

SELECT id, array_agg(w) AS allwords
FROM
(
  SELECT
   id,
   unnest(words) AS w
  FROM words_table
) x
GROUP BY id;

Working Fiddle

answered May 22, 2013 at 3:02

Anthony Accioly

22.6k9 gold badges76 silver badges125 bronze badges

1 Comment

jpmc26 Over a year ago

I would like to note that this answer extends to reducing the array to unique values better than mine. One need only add DISTINCT like so: array_agg(DISTINCT w).

Collectives™ on Stack Overflow

postgresql: Aggregating arrays of text

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related