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
2
votes
2 answers

Multiple array_agg in sqlalchemy

I am working with postgres. I want to fetch multiple fields using array_agg in sqlalchemy. But I couldn't find examples of such use anywhere. I made my request. But I can't process the result of array_agg. I'd like to get a list of strings, or…
Alexander Goryushkin
  • 299
  • 2
  • 7
2
votes
0 answers

mysql jsonarrayagg for comma seperated id

i have two table casetemp and medicication_master patient has fields id name age sex medicineid 1 xyz 23 M 1,2 2 abc 20 f 3 medicine has fields id medname desc 1 crosin fever tab 2 etzaa shampoo 3 zaanta painkiller i…
user15330211
2
votes
2 answers

Big Query (SQL) convert multiple columns to rows / array

I have a data source with multiple similar columns that looks like this, with each question as a new column and the corresponding response: Original and I would like to convert it to use an array with two paired columns so that it ends up looking…
RJB
  • 23
  • 3
2
votes
1 answer

Count values in array_agg

Considering the following data: |model| qty | color | -------------------------------------------------- | y | 6 | {yellow,red,red} | -------------------------------------------------- | z | 4 |…
douglas_forsell
  • 111
  • 1
  • 10
2
votes
2 answers

Nested aggregate function in PostgreSQL

I am trying to return every author_id, author_name, and AVG(total) per author for every article_group. I am trying to lump the author_id, author_name, and AVG(total) into arrays. I understand that this query will return an article_group per array,…
SC4RECROW
  • 119
  • 1
  • 10
2
votes
2 answers

How to return an array of structs from a struct of arrays in Standard SQL?

I have a non-repeated record column on my table that I want to access. On this record, there are several repeated values. So it is a RECORD, like so: STRUCT unit_cost ARRAY quantity ARRAY> as costs Eg. the data…
Jack Collins
  • 57
  • 1
  • 2
  • 7
2
votes
2 answers

PostgreSQL array_agg but with stop condition

I have table with record of children & i want to get comma separated results in descending order by month but with a breaking condition of status of child in each month. if status is 0 push it to array but if status is 1 then don't push it and break…
Muhammad Suhaib
  • 90
  • 7
2
votes
2 answers

Adding LIMIT to ARRAY_TO_JSON or ARRAY_AGG

In a 2-player game using PostgreSQL 9.6.6 as backend I have defined the following custom stored function for retrieving chat messages for a user: CREATE OR REPLACE FUNCTION words_get_user_chat( in_uid integer ) RETURNS jsonb…
Alexander Farber
  • 21,519
  • 75
  • 241
  • 416
2
votes
2 answers

How to use array_agg() aggregate function in pig or hive

I have the below data: ================================================================ session_id screen_name screen_launch_time ================================================================ 990004916946605-1404157897784 …
explorethis
  • 61
  • 2
  • 9
2
votes
2 answers

Postgresql aggregate array bracket removal

How do i remove the brackets {} from an Array_AGG query output? ARRAY_AGG(DISTINCT(SGL.short_name)) from this {01,02} to this 01|02| thanks!
user3263892
  • 93
  • 2
  • 9
1
vote
2 answers

In BigQuery how to concatenate arrays with distinct values in nested aggregations?

I have a BigQuery SQL below. The source table is aggregated from another table and field x contains distinct values. To further aggregate on field name, the query unnests x and uses array_agg(distinct x) to merge the x arrays. It works, but it takes…
0xLN
  • 149
  • 1
  • 5
1
vote
1 answer

Remove duplicates from array_agg, where elements are also arrays

In a Postgres 11 database, I've got two arrays in two views which are joined to MAIN table: vw_ideas_role_direction_mappings.user_direction_id - array (like {101,103,} or {101,103} or {101,} or…
Gerzzog
  • 95
  • 7
1
vote
2 answers

Why do I have "message": "Expected 1 bindings, saw 0" when using array_agg with knex?

I'm using postgres and I try to make my request with array_agg in knex, but I have the error: "message": "Expected 1 bindings, saw 0" Does anyone know where this error may come from? And how can I fix it? my…
poumchakaa
  • 11
  • 3
1
vote
1 answer

Why it works different with ~~ any()?

Let me first show you my table: INSERT INTO my_table(name, brand, source) VALUES ('Abc', 'Abc', 'Orig'), ('Auchan', 'Auchan', 'Orig'), ('Auchan', 'Auchan', 'Added'), ('dj-auchan-djd', 'Auchan', 'Added'), ('Auchan', 'Other', 'Added'), ('Other',…
Viktor Kachmaryk UA
  • 13
  • 3
1
vote
2 answers

How to object in row_to_json and array_agg from multiple value

I manage to create a jsonb output, however I need to make it as object My current code create function extract_data() returns jsonb as $$ declare v_msgar text[]; v_result jsonb; analyzer_test_full_desc character varying; data_reading…
Moncici101
  • 23
  • 8
1
2
3 4 5 6 7