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
1
vote
1 answer

Do two array_aggs in a query share the same window?

Consider this example: SELECT comment_date , array_agg(user_id) users , array_agg(comment) comments FROM user_comments GROUP BY comment_date Is it safe to assume that the indexes of users and comments refer to the same…
vol7ron
  • 40,809
  • 21
  • 119
  • 172
0
votes
1 answer

Appending data to existing string_agg in bigquery

when a person pings or calls, we should be able to show that data and if he doesn't do, it should show as null but when he makes multiple pings or calls, it is appending as multiple array. below is the sample data in that I'm getting…
jay
  • 3
  • 3
0
votes
2 answers

How to query other tables in ARRAY_AGG rows?

I have the below…
Fred Hors
  • 3,258
  • 3
  • 25
  • 71
0
votes
1 answer

How can I use the "ARRAY_AGG" function twice in sequence in BigQuery? One inside the other?

I'm trying to make a query in BigQuery with the nested ARRAY_AGG function (one inside the other), as follows: ARRAY_AGG( STRUCT( . . . . . ARRAY_AGG( STRUCT( . ) ) as xxx ) …
dimitri.marinho
  • 15
  • 5
0
votes
1 answer

How to count array elements in subquery using ARRAY_AGG in Postgresql?

The initial query finds the languages applied for each feature_id and makes the result into an array. I want to count the number of languages in each area, per feature_id, per version (new and old). SELECT …
danaburtono
  • 13
  • 2
0
votes
1 answer

Filtering site with specific tags in Django while keeping all site tags aggregated in annotation field

Let's say I have the following django model: class Tag(models.Model): key = models.CharField(max_length=64, unique=True) class Site(models.Model): key = models.CharField(max_length=64, unique=True) tags = models.ManyToManyField(Tag,…
jlandercy
  • 7,183
  • 1
  • 39
  • 57
0
votes
1 answer

SQL Bigquery - Create a data set from 3 tables with master records and no duplicates with ARRAY_AGG(Struct)

I am trying to query 3 tables to create one data set with master item data, transaction data and then serial numbers/locations. My left table is a unique key for each item mentioned once, then the transaction data and serial data has the key…
Greg Brown
  • 1
0
votes
0 answers

How to use Array_agg without returning the same values in different Order?

When using Array_agg, it returns the same values in different orders. I tried using distinct in a few places and it didn't work. I tried using an order before and after the array and it would fail or not properly exclude results. I am trying to find…
Mike N Excel
  • 13
  • 2
0
votes
1 answer

Postgres - using `ARRAY_AGG()` aggregate function in multiple cases for same array

I am trying to write an SQL query using CASE where I want to return the values from the campaign table of the selected row when on two cases: The current time is between the start date and end date, which is displayed in the query. That part is…
jabepa
  • 61
  • 5
0
votes
2 answers

Aggregate multiple columns into array

Suppose I have a table like the following user score_1 score_2 score_3 1 100 80 100 1 80 null 80 2 95 90 65 I would like to aggregate the 3 scores columns into an array. The result will look like (the order does not…
Ryan
  • 219
  • 2
  • 11
0
votes
0 answers

Athena aggregate calculated rows to an array

I have the following SQL that produces a list of values: SELECT x / 100e0 FROM UNNEST(sequence(1, 100, 1)) t(x) # _col0 1 0.01 2 0.02 3 0.03 ... ... 100 1 I'm trying to aggregate the results to get an array of…
Shlomi Schwartz
  • 8,693
  • 29
  • 109
  • 186
0
votes
1 answer

Postgres, aggregate function and ORDER BY

The statement works for me: SELECT e.id, e.title, array_agg(d.start_date) date, array_agg(d.id) ids FROM event e JOIN event_date d ON e.id = d.event_id GROUP BY e.id I receive the results id title dates ids 1 First…
olek07
  • 513
  • 2
  • 7
  • 21
0
votes
0 answers

Selecting name from aggregated ID in Postgresql

I have no clue how to do this. (Select Dane1.Name, Array_agg(Dane1.UpperCat) as "upper_category", count(Dane1.UpperCat) As data_count, dane1.room_id From (Select contact.room_id, …
Serpher
  • 13
  • 3
0
votes
1 answer

Open, high, low, close aggregation in BigQuery

Based on the BigQuery best practice of using ARRAY_AGG() to obtain the latest record, here's how I'm getting the first, last minimum and maximum values of a field for a day. The data is reported approximately hourly. WITH t AS ( SELECT…
Martin Burch
  • 2,726
  • 4
  • 31
  • 59
0
votes
1 answer

BigQuery query optimisation - Unnest field to REPEATED STRUCT

I currently have the below query which perfectly works, but I would like to know if it can be optimized (perhaps avoid to UNNEST firstly and GROUP BY secondly and make transformations in one step). with src as ( select 1 as row_key,…
JohnDu17
  • 37
  • 1
  • 6
1 2 3
4
5 6 7