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

postgres sql window function - trying to consolidate array_agg based on date and employee_id

I am currently using postgres and there is this sql window function that is used to generate employee check in and out time based on date. But its flawed. CREATE OR REPLACE VIEW view_test AS SELECT row_number() OVER () AS id, …
Axil
  • 3,606
  • 10
  • 62
  • 136
1
vote
0 answers

Django query More than one row returned by a subquery used as an expression

I have a pretty simple query and I need to add one field to get an average price grouped by benefit - I tried with ArrayAgg but is not working. properties = ( House.objects.filter( # a lot of filters ).annotate( …
Janosz
  • 21
  • 2
1
vote
1 answer

Array_agg function

I have multiple column to aggregate in one new column .I wanna Concatenate them in one array and separator with ',' between them.Can you tell me some advances to write query .My example i want work simple and faster : SELECT array_agg('[' ||…
ittos2
  • 11
  • 2
1
vote
2 answers

BigQuery: Select top 3 Day of Sales with GroupBy and save in separate columns leaving others

Input: Have got Table 'A' Store Category Sales Day 11 aaa 1.5 Sunday 11 aaa 0.5 Monday 11 aaa 2.5 Tuesday 11 aaa 2.0 Wednesday 11 aaa 3.0 Thursday 11 aaa …
user12345
  • 499
  • 1
  • 5
  • 21
1
vote
1 answer

Whats the best method of making arrays of joined tables in Big Query?

I'm trying to do some data transformation inside of Big Query, with SQL. Let's say I have three tables: Customer - data about the customer, like age, etc Subscriptions - data about what subscriptions the user have Engagements - data about how the…
David Beck
  • 975
  • 8
  • 12
1
vote
4 answers

how to convert array of integers after array_agg into values for IN clause

Could please help me, I'm trying resolve this for a quite long time... I have table Product and RelatedProducts (top level products consist of other base products). Goal: I'd like get all base products. So, table looks like: product_id …
1
vote
0 answers

array_agg function on Apache Drill

I need to use array_agg function like below.(It`s postgresql) test=# select t2.c_no, array_agg(t4.contents) from table2 as t2 inner join table4 as t4 on t2.c_no = t4.c_no group by t2.c_no; c_no | array_agg …
Steven Chu
  • 71
  • 1
  • 7
1
vote
2 answers

PostgreSQL group_concat rows as json

I have a query in mysql and want to do the same in PostgreSql. Here's the query: -- mysql SELECT cat.id, cat.category, CONCAT('[', GROUP_CONCAT(CONCAT('{"rate":"',mod.rate,'", "model_name":"', mod.modelName, '",…
Azima
  • 3,835
  • 15
  • 49
  • 95
1
vote
1 answer

Query table with array_agg/median of ALL previous positions, LAST_10, LAST_50, excluding current position

This is a variation on this brilliantly answered question I posted previously: I have a database table with: id | date | position | name -------------------------------------- 1 | 2016-06-29 | 9 | Ben Smith 2 | 2016-06-29 | 1 |…
Luke Byrne
  • 109
  • 7
1
vote
1 answer

How to create array column based on unique values in another column SQL

I have data from a postgres tables that looks like this: Server | Database | Contact server1 | db1 | contact 1 server1 | db2 | contact 2 server1 | db3 | contact 3 server2 | db1 |…
e1v1s
  • 365
  • 6
  • 18
1
vote
1 answer

Buggy behaviour of "ORDER BY" in ARRAY_AGG function of BigQuery

I think ARRAY_AGG function in BigQuery seems to have a bug in behaviour of ORDER BY. Here is some SQL to explain that: #standardSQL WITH t1 AS ( SELECT * FROM UNNEST ( [ STRUCT(1 AS user_id, 1 AS team_id, "2018-07-17" AS date_str), ( 2,…
mule
  • 41
  • 4
1
vote
1 answer

Converting a returned SETOF into An Array in PL/PgSQL (specifically)

I know there is the process of SELECT array_agg(f) FROM (SELECT blah FROM stuff) f, which is great in SQL, but when writing functions in PL/pgSQL, is there a shorthand method?? I'm trying to put JSON keys into an array I can use to look at the…
Guy Park
  • 959
  • 12
  • 25
1
vote
3 answers

Group table in a dict or json

I have two tables with a 1:M relation, and I'd like to group this relation in a dict or JSON. My data is something like: (id, provider_code,…
Pablo Pardo
  • 729
  • 5
  • 12
  • 26
1
vote
3 answers

How to make this sql with query array_agg?

I want to make a query select * from projects where user_id = 3; and depending on it's result r, I need to make n queries, where n is the length l of r. eg: | id | project_name | description | user_id | | 1 | Project A | lorem ipsu | 3 …
cesarvargas
  • 182
  • 3
  • 15
1
vote
2 answers

PostgreSQL array_agg order for window functions

The answer to my question was almost here: PostgreSQL array_agg order Except that I wanted to array_agg over a window function: select distinct c.concept_name, array_agg(c2.vocabulary_id||':'||c2.concept_name order by…
Sigfried
  • 2,943
  • 3
  • 31
  • 43