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

How to create array in postgres based on unique values from the same table joined twice?

I have a query which in I joined twice the same table, i.e: left join tag tag1 [...] left join tag tag2 [...] I would like to have query with one column called tag which in I will have unique values from both tables (tag1, tag2). Example: For now I…
Tomasz
  • 89
  • 1
  • 1
  • 6
0
votes
1 answer

How to get the results of conditions in array_agg into one line using postgres?

I need to process this table, divide the data into columns based on the value in exception_type in postgres. service_id, date,…
Radek
  • 3
  • 2
0
votes
1 answer

Javascript - Unable to read json key values - Unexpected token i in JSON at position 1 - sql query output problem

I'ved got this string generated from sql query (basically using ARRAY_AGG and some cases to format the output) that looks like this {id:2,name_of_division:'super department1',attendance_radius:1000} However, in javascript, I cant get any of the key…
Axil
  • 3,606
  • 10
  • 62
  • 136
0
votes
1 answer

How to avoid an array uf NULLS when making nested table in BQ

I am joining 2 tables, the join is left, and second table does not have all keys from the first, so that does produce NULLs on the right. How do I avoid them when grouping this into an array? For example: with t1 AS (select 1 AS c1, 2 AS c2 UNION…
MStikh
  • 368
  • 4
  • 21
0
votes
1 answer

Bigquery - Repeated field inside of a repeated record

I have a denormalised table and I want to simplify it using repeated fields inside of BigQuery. To illustrate what I am trying to do, I want to go…
Clément Voisin
  • 3
  • 2
0
votes
0 answers

In Big Query, is there a way to take the latest row of data while building a struct over a window?

Hi and thanks for your time I'm trying to run a process that backfills data between the partition dates of '2021-06-08' and '2021-06-10'. Each partition requires 10 days of data I want to produce a row for every user every day in the partition, so…
Mark Aitkin
  • 31
  • 2
0
votes
2 answers

How to get an empty array in array_agg if condition is not met?

I have 2 tables: collections_books (collection_id, book_id) users_collections (user_id, collection_id, access) I'm using PostgreSQL. The following query gives me a list of book_ids grouped by collection_ids. The problem is, since I'm using where…
Siddiq Nx
  • 122
  • 1
  • 6
0
votes
2 answers

SQL query with array of counts?

I have a table which looks like this: record no firstType secondtype win? 1 X A 1 2 X A 0 3 X B 1 4 Y B 0 5 …
David Kravitz
  • 3
  • 2
0
votes
1 answer

Array_agg() being cast to a string on select

How can i force a type in db:select(db::raw($query)) on PHP?? I have a really complex query that I'm getting data from, to do so I'm using DB::raw($query). I'm using the postgreSQL array_agg() function in 2 columns. SQL example: $query = 'select …
Felipe Colussi-oliva
  • 168
  • 1
  • 8
0
votes
1 answer

How to return an array of table records in PostgreSQL

I am getting a type mismatch error when trying to return an array of elements of type table1, the inherent type of the table1 I have declared. Error occurred during SQL query execution Razón: SQL Error [42P13]: ERROR: return type mismatch in…
coterobarros
  • 941
  • 1
  • 16
  • 25
0
votes
2 answers

How to respect the order of an array in a PostgreSQL select sentence

This is my (extremely simplified) product table and some test data. drop table if exists product cascade; create table product ( product_id integer not null, reference varchar, price decimal(13,4), primary key…
coterobarros
  • 941
  • 1
  • 16
  • 25
0
votes
1 answer

Postgres how to group by 1 column and aggregate other columns as elements in an array

DB table looks like this: state | city | contact --------------------------- NY | city1 | person1;person2;person3 NY | city2 | person4;person5;person6 NY | city3 | null CA | city1 | person7;person8;person9 CA |…
e1v1s
  • 365
  • 6
  • 18
0
votes
1 answer

Postgres Complex Query to get column values having corresponding column joined with another table having a specific value

I have two Postgres tables: 1- relationships: | user_id | target_user_id | 2- affiliations: | user_id | user_type_id | current | user_id from affiliations can be any of the two column values in relationships, and current in affiliations…
Riham Nour
  • 387
  • 4
  • 10
0
votes
0 answers

array_agg function in postgres, when used in java does not return any record/data?

I have a query like this select sender_id, ARRAY_AGG(receiver_id) as receiver_id from senderTbl group by sender_id When I execute this query in pgAdmin it works well and returns the record like this image But when called from java code it doesn't…
John
  • 276
  • 1
  • 9
  • 29
0
votes
1 answer

Structuring SQL Output for single and 2 dimensional array

I am working on a PHP program which requires a series of SQL related outputs and I was hoping that SQL could do most of the work form me. I have a table structured for the sake of this question with this type of data and headers Note : ID is used…
Alex Carlson
  • 99
  • 9
1 2 3 4
5
6 7