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

Array aggregation in Exasol

Use case is fairly straightforward. In postgres I can aggregate values from a GROUP BY into an array: select customer, array_agg(product_name) as items from transactions group by customer customer …
Jivan
  • 21,522
  • 15
  • 80
  • 131
0
votes
2 answers

How correctly test the date is within time intervals

I have a timestamp of an user action. And several time intervals when the user have grants to perform the action. I need to check wether the timestamp of this action is within at least one of the time intervals or not. Table with users: CREATE TABLE…
PavelP
  • 89
  • 6
0
votes
1 answer

postgres array_agg with distinct on json_build_object

select t1.id, array_agg( json_build_object('id', t2.id, 'status', t2.status) ) as statuses from table1 t1 inner join table2 t2 on t1.id=t2.user_id inner join table3 t3 on t1.id=t3.user_id group by t1.id table1 id , user 1 , …
Niyaz
  • 2,677
  • 3
  • 21
  • 40
0
votes
0 answers

When using ARRAY_AGG it removes my record

I am trying to use Array_agg to help combine some records, but it seems to be removing the record when I try to use it. The format of what I have written is below. All the records which are individual stay. SELECT…
Big_D
  • 15
  • 3
0
votes
0 answers

Convert postgres database data to Json in an efficient way

My requirement is to fetch multiple tables data(more than 200K rows) and convert to Json and return. I can acheive above by two ways(Please suggest me if any other) a. convert DB data to Json in query itself(array_to_json(array_agg() or…
Arju Pidugu
  • 23
  • 3
0
votes
1 answer

Ecto query to grab all values that satisfy all values in array_aggregator not just any?

Wonder if someone can help me with an array aggregator issue I’ve got a query that does a join using a joining table then it filters down all values that are inside a given array and filters out values that are in another array. The code looks like…
Chris G
  • 449
  • 1
  • 5
  • 19
0
votes
1 answer

Query table with array_agg of ALL previous positions, excluding current position

I have a database table with: id | date | position | name -------------------------------------- 1 | 2016-06-29 | 9 | Ben Smith 2 | 2016-06-29 | 1 | Ben Smith 3 | 2016-06-29 | 5 | Ben Smith 4 | 2016-06-29 | 6 |…
Luke Byrne
  • 109
  • 7
0
votes
1 answer

"Function does not exist" errors when trying to split column containing array of timestampz into delimited text string in Postgres

I have a table with columns that contain arrays that I want converted into strings so I can split them by the delimiter into multiple columns. I'm having trouble doing this with arrays of dates with timezones. create materialized view matview1 as…
gulfy
  • 47
  • 2
  • 6
0
votes
2 answers

postgresql : self join with array

My question is about forming Postgres SQL query for below use case Approach#1 I have a table like below where I generate the same uuid across different types(a,b,c,d) like mapping different types. +----+------+-------------+ | id | type |…
Learner
  • 147
  • 1
  • 1
  • 9
0
votes
1 answer

Generating array-agg(...)s in postgres

Using Postgres 10 I have tables representing 'units', the 'group' to which each unit belongs, and the 'distance' between each pair of units. I now want a group-to-group distance table that aggregates all the unit-to-unit distances for each pair of…
user9175828
  • 17
  • 3
0
votes
1 answer

Postgresql Using array_agg and jsonb_build_object

I'm building a JSON object from various tables. I have successfully got the following code to almost do what I want. The only problem is the the aggregated array is set as another object, and I just want the item data in the main object to point to…
whitelined
  • 310
  • 4
  • 13
0
votes
1 answer

BigQuery SQL Select that returns Key Value Pairs and not two seperate columns

I am trying to merge 2 of my columns in my select statement on bigquery so that I have one column with key value pairs rather than having 2 columns (one with the key and one with the value). I have attempted to use array_agg() but whenever I do it…
s.m
  • 1
  • 1
  • 1
0
votes
0 answers

POSTGRESQL - UNNEST function not work in LINUX

I got a duplicate row displayed. though I already added 'DISTINCT',When i run query duplicate records show. id | product | service ---+----------------------- 1 |p1 |s1 2 |p2 |s2 3 |p2 |s2 SELECT DISTINCT…
user8918955
0
votes
1 answer

Why array_agg() is returning empty array in postgresql?

I have an integer type column named as start. I want to make an array by the values of this column. It seemed to be very easy and I used array_agg(), but it is giving empty array as output. Following is my column data start 1 2 11 5 . . .…
LSG
  • 213
  • 3
  • 14
0
votes
2 answers

PostgreSQL: Get an entity with all his relationships

I have a table "Cars" and a table "Person". A Person drives many Cars and a Car can be driven by many People so I have another table "Person_Car" which has both id's per row. Car(id, name) Person(id, name) Person_Car(car_id, person_id) How can I…
Alan
  • 2,559
  • 4
  • 32
  • 53
1 2 3 4 5
6
7