Stack Overflow
Stack Overflow

Questions tagged [array-agg]

array_agg() is a database function to aggregate an attribute from different rows into a single array. It is a built-in function in PostgreSQL, HSQLDB and DB2.

The array_agg() aggregate function is defined in the ANSI SQL:2008 specification. It aggregates an attribute from a set of source rows into a single array with a value for each of the attributes. The data type of the attribute is preserved in the array.

The array_agg() function is supported by PostgreSQL (8.4 and later), HSQLDB and DB2 (at least since 9.5.0).

Other DBMSs use alternative functions such as listagg() (Oracle) or group_concat() (MySQL and SQLLite) but the resulting array contains string representations of the source row attributes.

95 questions
0
votes
2 answers

Postgres Find and Return Keywords From List Within Select

I have a simple postgres table that contains a comments (text) column. Within a view, I need to search that comments field for a list of words and then return a comma separated list of the words found as a column (as well as a bunch of normal…
Steve Goddard
  • 71
  • 8
-1
votes
1 answer

Redshift - Array returning single data per record

I have a table containing the following fields: email - logged user email allowed_id - A ID of another User The table contains multiple entries for the same email, each one containing a different allowed_id. I'm trying to aggregate this in an array…
andrepz
  • 443
  • 6
  • 16
-1
votes
1 answer

BigQuery - Get fields of nested Repeated Records

I am working with BigQuery tables that can have many levels of nested repeated record fields, as shown in the example. I need to make a select on the given table, extract only some fields and ignore others, and at the end have the same structure…
Boschi Francesco
  • 43
  • 4
-1
votes
1 answer

How to dedup array_agg in bigquery

I created a new table with repeating records with duplicates. I am trying to find the most efficient way to deduplicate records as this will be run on a table with millions of records. If you using multiple CTE's nested does it matter what your data…
Denis The Menace
  • 49
  • 7
-1
votes
1 answer

Is it possible to use star expression inside array_agg(struct expressions in BigQuery?

I am joining several tables into one nested, and I was wondering if I could use a star expression, for example, SELECT t1.*, ARRAY_AGG(STRUCT(t2.*)) FROM t1 JOIN t2 on t1.col1=t2.col1 GROUP BY t1.* I get an error, but I was wondering, maybe I could…
MStikh
  • 368
  • 4
  • 21
1 2 3 4 5 6
7