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
160
votes
4 answers

PostgreSQL array_agg order

Table 'animals': animal_name animal_type Tom Cat Jerry Mouse Kermit Frog Query: SELECT array_to_string(array_agg(animal_name),';') animal_names, array_to_string(array_agg(animal_type),';') animal_types FROM animals; Expected…
Olo
  • 1,603
  • 2
  • 11
  • 4
96
votes
2 answers

How to remove duplicates, which are generated with array_agg postgres function

Does anyone an idea how to rewrite following SQL query to generate results, that would contains only one occurrence of name? (results grouped by user). The query SELECT array_to_string(array_agg(CONCAT(u.firstname, ' ', u.lastname)), ', ') FROM…
Peter Jurkovic
  • 2,686
  • 6
  • 36
  • 55
34
votes
2 answers

How to aggregate two PostgreSQL columns to an array separated by brackets

I would like to concatenate two columns using a group-by query resulting in an array separed with brackets. I know this question is related to this question, but as usual my use-case is a little different. A simple example (also as SQL…
Mattijn
  • 12,975
  • 15
  • 45
  • 68
29
votes
1 answer

how to make array_agg() work like group_concat() from mySQL

So I have this table: create table test ( id integer, rank integer, image varchar(30) ); Then some values: id | rank | image ---+------+------- 1 | 2 | bbb 1 | 3 | ccc 1 | 1 | aaa 2 | 3 | c 2 | 1 | a …
user491575
  • 563
  • 1
  • 6
  • 6
9
votes
2 answers

SQL array agg and joins

In my Postgres database, I have 3 tables. One for users one for comments and one to map the two user_comment_map. Here's how the tables look: users | id | name | age | |----|------|-----| | 1 | user | 20 | comments | id | mood | subject |…
William Gottschalk
  • 261
  • 4
  • 10
8
votes
2 answers

Equivalent of PostgreSQL's array_agg in Oracle XE 11.2

I have a Oracle 11g XE database and I have a query the result set: ID Category 1 Cat1 1 Cat2 2 Cat3 2 Cat4 I want to get distinct id's with all related categories in same row as comma separated like this ID Categories 1 …
Murmelto
  • 162
  • 2
  • 11
6
votes
2 answers

array_agg with distinct works in postgres 9.4 but not in postgres 9.6

I have a query that use array_agg with distinct as an argument and is not accepted on postgres 9.6. I created this sample to illustrate the issue: create table numbers (id integer primary key, name varchar(10)); insert into numbers…
Browser_80
  • 131
  • 1
  • 5
5
votes
1 answer

ARRAY_AGG interrupted by GROUP BY when trying to ORDER BY timestamps

I have prepared an SQL Fiddle for my problem - Given the following table: CREATE TABLE chat( gid integer, /* game id */ uid integer, /* user id */ created timestamptz, msg text ); filled with the following test…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
4
votes
1 answer

How to count on a group by array_agg in PostgreSQL

Here is my SQL: SELECT t.uid, array_agg(t.place) FROM tour_tracking t WHERE (orderon::time BETWEEN '18:00:00' AND '20:00:00') GROUP BY t.uid; Origin result: |---------------|----------------------| | uid | place …
stackoverYC
  • 490
  • 2
  • 4
  • 13
4
votes
0 answers

How to use PostgreSQL ArrayAgg function on a grouped by query set in Django ORM?

To keep it simple I have four tables(A, B, Category and Relation), Relation table stores the Intensity of A in B and Category stores the type of B. A <--- Relation ---> B ---> Category I am trying to eliminate joins in my query to reduce…
Azee
  • 329
  • 6
  • 20
3
votes
1 answer

Django ArrayAgg - filtering annotated array

class Language(models.Model): iso_code = models.CharField() class Publisher(models.Model) name = models.CharField() class Book(modle.Model): name = models.CharField() language = models.ForeignKey(Language) publisher =…
sheesh
  • 51
  • 1
  • 6
3
votes
1 answer

Postgres GROUP BY an array column

I have a list of students and parents and would like to group them into families using the student id's. Parents who share common student id's can be considered to be a family while also students who share common parent id's can be considered to be…
Clint_A
  • 518
  • 2
  • 11
  • 35
3
votes
0 answers

PostgreSQL function array_agg in JPA

In PostgreSQL, array_agg (column) allows to aggregate. I try to use it with JPA. ParameterExpression param = criteriaBuilder.parameter(Integer.class, "empId"); Expression groupId = criteriaBuilder.function("array_agg",…
rushabh sojitra
  • 142
  • 14
3
votes
1 answer

POSTGRESQL multiple select, multiple row arrays possible?

Say I'm doing something simple like selecting everything from two tables: "SELECT * FROM table1; SELECT * FROM table2;" It will return a results object that looks like: {rows:[{},{},{} etc...]} Where the array of row objects is every row from…
OliverJ90
  • 1,291
  • 2
  • 21
  • 42
2
votes
3 answers

Is it wasteful to use ARRAY_AGG to get the first non-NULL value in a column?

I use PostgreSQL 14 to manage a table which stores updates to a table of medics: users can update the first name, last name, and or/ the age of the medic. A field which was not touched by an update operation has a NULL value. Here's an example of…
Frerich Raabe
  • 90,689
  • 19
  • 115
  • 207
1
2 3 4 5 6 7