The result data type of ARRAY_AGG is an array. The data type of an array element of the result array is the same as the type of expression. The ARRAY_AGG function can be invoked only in the following contexts: The SELECT list of a SELECT INTO statement A much simpler alternative is to create a custom aggregate function (you only need to do this once). CREATE AGGREGATE array_concat_agg(anyarray) ( SFUNC = array_cat, STYPE = anyarray ); Then replace array_agg for array_concat_agg:. SELECT array_concat_agg(DISTINCT dp.zoning) zoning, array_concat_agg(DISTINCT …Table 2: (The format needed) So I need to: Get distinct "Action" values for each user. Preserve the order ( UserID, Visit, Order ) Show only the 1st and 2nd actions in one row. My attempted query …The SQL/JSON function JSON_ARRAYAGG is an aggregate function. It takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values. 1. I have a table that is using ARRAY_AGG on a few STRUCTs. So far, none of the values needed to be aggregated. I added a new STRUCT and added it to a ARRAY_AGG but cannot understand how to sum a field in this new STRUCT. There are currently 2 STRUCTs using ARRAY_AGG, professor and class. I am trying to add a 3rd …Oct 6, 2017 · I am trying to use ARRAY_AGG with ORDINAL to select only the first two "Action"s for each user and restructure as shown in TABLE 2. SELECT UserId, ARRAY_AGG( STRUCT(A.Action, A.Visit, A.Order) ORDER BY A.Visit, A.Order, A.Action ) FROM `table` LEFT JOIN UNNEST(A) AS A GROUP BY UserId Table 1: (Sample output of above query ) Description Returns expression for some row chosen from the group. Which row is chosen is nondeterministic, not random. Returns NULL when the input produces no rows. Returns NULL when expression is...Description Returns expression for some row chosen from the group. Which row is chosen is nondeterministic, not random. Returns NULL when the input produces no rows. Returns NULL when expression is... 0. If you're expecting integers in the array, you can use regex and loop through the matches of (\d+). Another method is to use array_to_string and have it join using values that do not appear in the content, a newline \n for example: array_to_string (some_array_content, E'\n'), then split it later. Once you get it into an array one way or ...Jul 30, 2009 · Functions abs acos acosh add_months aes_decrypt aes_encrypt aggregate and any any_value approx_count_distinct approx_percentile array array_agg array_append array_compact array_contains array_distinct array_except array_insert array_intersect array_join array_max array_min array_position array_remove array_repeat array_size array_sort array_union I'm joining the tables like this, using array_agg to aggregate the tags into one field: SELECT objects.*, array_agg(tags.tag) AS tags, FROM objects LEFT JOIN taggings ON objects.id = taggings.object_id LEFT JOIN tags ON tags.id = taggings.tag_id However, if the object has no tags, Postgres returns this: [ null ] instead of an an empty …Jun 7, 2012 · 235 Use array_agg: http://www.sqlfiddle.com/#!1/5099e/1 SELECT s.name, array_agg (g.Mark) as marks FROM student s LEFT JOIN Grade g ON g.Student_id = s.Id GROUP BY s.Id By the way, if you are using Postgres 9.1, you don't need to repeat the columns on SELECT to GROUP BY, e.g. you don't need to repeat the student name on GROUP BY. Part of AWS Collective. 0. AWS Athena's array_agg, when sorted by multiple values, can return an incorrect order. Here is the min repro. with xxx (id, sort_by, val) as ( values (1, 'a' , 999), (1, 'b', 555) ) select id, array_agg (val order by sort_by) as single_sort, -- [999, 555] array_agg (val order by sort_by, val) as dual_sort -- [555, 999 ...The ARRAY_AGG function can only be specified within an SQL procedure, compiled SQL function, or compound SQL (compiled) statement the following specific contexts (SQLSTATE 42887): ... CREATE PROCEDURE GETPHONENUMBERS (IN EMPID INTEGER, OUT NUMBERS PHONELIST) BEGIN SELECT …ARRAY_AGG The sole aggregation for arrays takes all the values in a column and aggregates them into one field. For example SELECT ARRAY_AGG (country_id) FROM city_array returns ARRAY_APPEND The ARRAY_APPEND function is used to append values to an array. Oct 6, 2017 · I am trying to use ARRAY_AGG with ORDINAL to select only the first two "Action"s for each user and restructure as shown in TABLE 2. SELECT UserId, ARRAY_AGG( STRUCT(A.Action, A.Visit, A.Order) ORDER BY A.Visit, A.Order, A.Action ) FROM `table` LEFT JOIN UNNEST(A) AS A GROUP BY UserId Table 1: (Sample output of above query ) PostgreSQL now has a function json_agg that can be used in lieu of array_to_json(array_agg( ...)) but actually behaves better in some cases. See "Array_agg in postgres selectively quotes" and the documentation: "Aggregate Functions".Here is the modified query: SELECT json_agg(row(t.*)) AS users FROM ( SELECT user_id, CASE …Example 2: Use ARRAY_AGG in a SELECT INTO statement to assign the values of the ESALARIES ordinary array to the array ARRAY2. SELECT ARRAY_AGG (T.VAL) INTO ARRAY2 FROM UNNEST (ESALARIES) AS T (VAL); Example 3: Use ARRAY_AGG to aggregate a set of phone numbers into an ordinary array.The ARRAY_AGG function can only be specified within an SQL procedure, compiled SQL function, or compound SQL (compiled) statement the following specific contexts (SQLSTATE 42887): The select-list of a SELECT INTO statement. The select-list of a fullselect in the definition of a cursor that is not scrollable.To create an array of multiple data values in SQL, you’ll likely leverage the ARRAY_AGG function (short for array aggregation), which puts your input column values into an array. How to use SQL ARRAY_AGGThe aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values.This ordering is unspecified by default, but can be controlled by writing an …Join solution. If your brands dataframe doesn't fit in memory, you can first left join df2 with brands to have a column containing brand if the brand is in brands dataframe, else null, then do your group by, and finally replace empty array due to advertisers without brands you want to filter by null:If you want to create a JSON array, then use JSON functions: select jsonb_agg(concat_ws(',', us.name, us.age, us.gender)) from users as us; If you want real JSON objects (using key/value pairs) rather than comma separated strings inside the array: select jsonb_agg(to_jsonb(u)) from ( select name, age, gender from users ) uThe listagg function transforms values from a group of rows into a list of values that are delimited by a configurable separator. Listagg is typically used to denormalize rows into a string of comma-separated values (CSV) or other comparable formats suitable for human reading.Description The ARRAY function returns an ARRAY with one element for each row in a subquery. If subquery produces a SQL table, the table must have exactly one column. Each element in the... 5. Presto has powerful array functions. This should work: select array_distinct (flatten (array_agg (pets))) all_pets from mytable. Basically this aggregates all arrays to together, as an array of arrays, then concatenates all elements together in a single array, and finally removes duplicates. Share.Then you can filter out by the rules you described in your notes. With temp as ( SELECT A, COALESCE (B, ''), ARRAY_AGG (DISTINCT C) FROM X GROUP BY A, COALESCE (B , '') ) Select * From temp Where only get rows that fit your criteria. @A Simple Programmer thanks for response. Your suggestion still leaves me with the same …PostgreSQL ARRAY_AGG () function is an aggregate function that accepts a set of values and returns an array where each value in the input set is assigned to an element of the array. Syntax: ARRAY_AGG (expression [ORDER BY [sort_expression {ASC | DESC}], [...]) The ORDER BY clause is an voluntary clause.1 Answer. Sorted by: 12. Aggregates accept an ORDER BY. So you can write: array_agg (thecol ORDER BY someothercol) e.g. array_agg ( DISTINCT row (league.id, league.name::varchar) ORDER BY league.name ) AS league_names, Share.or an array constructor from the results of a subquery: select u.usr_id, name, array ( select tag_id from tags t where t.usr_id = u.usr_id ) as tag_arr from users u. The second option is a simple one-source query while the first one is more generic, especially convenient when you need more than one aggregate from a related table.The SQL/JSON function JSON_ARRAYAGG is an aggregate function. It takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values.The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values.Syntax ARRAY_UNIQUE_AGG( <column> ) Arguments column The column containing the values. Returns The function returns an ARRAY containing the distinct values in the specified column. The values in the ARRAY are in no particular order, and the order is not deterministic. The function ignores NULL values in column.The goal is to have an aggregate giving for each identifier sum of the "summable" column and an array of all distinct elements of the array column. The only way I can find is to use unnest function on the array column in a subquery and than join it with another subquery aggregating the "summable" columns. A simple example is as follows:ARRAY_AGG Returns the input values, pivoted into an ARRAY. If the input is empty, an empty ARRAY is returned. Aliases: ARRAYAGG Syntax Aggregate function ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ] Window function ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ] OVER ( [ PARTITION BY <expr2> ] ) The ARRAY_AGG function aggregates a set of elements into an array. DB2 Version 10.1 for Linux, UNIX, and Windows. ARRAY_AGG aggregate ... The ARRAY_AGG function can only be specified within an SQL procedure, compiled SQL function, or compound SQL (compiled) statement the following specific contexts (SQLSTATE 42887):1 You could solve this, and many other future issues, by changing your schema and not using arrays for this. Stick to 3NF and you wouldn't have this issue. One record, one column, one value. – Frank Heikens 2 hours ago I have to stick with array columns, it's as per requirement – Md. Parvez Alam 2 hours agoExploring a powerful SQL pattern: ARRAY_AGG, STRUCT and UNNEST by Lak Lakshmanan It can be extremely cost-effective (both in terms of storage and in terms of query time) to use nested fields rather than flatten out all your data. Nested, repeated fields are very powerful, but the SQL required to query them looks a bit unfamiliar.49. In PostgreSQL 8.4 you cannot explicitly order array_agg but you can work around it by ordering the rows passed into to the group/aggregate with a subquery: SELECT id, array_to_string (array_agg (image), ',') FROM (SELECT * FROM test ORDER BY id, rank) x GROUP BY id; In PostgreSQL 9.0 aggregate expressions can have an …SQL Server 2008 is no longer supported. 2012 is the earliest supported version, although 2016 is the obvious choice since you get Columnstore indexes, compression, in-memory tables, partitioning even in SQL Server Express and LocalDB (with SP1). It may be cheaper to upgrade than code JSON generation in T-SQL – Panagiotis KanavosTo solve that, I want to use ARRAY_AGG to roll up the individual tag numbers into an array, against which I can (should) easily test values for membership. SELECT items.id, items.title FROM items INNER JOIN tags ON (tag.tag_id=items.id) GROUP BY items.id HAVING ANY(ARRAY_AGG(tags.tag_id)=27). SELECT (select array_agg (distinct val) from ( select unnest (:array_column) as val ) as u ) FROM :your_table; For people like me who still have to deal with postgres 8.2, this recursive function can eliminate duplicates without altering the sorting of the array.Jun 1, 2020 · Courses. Practice. PostgreSQL ARRAY_AGG () function is an aggregate function that accepts a set of values and returns an array where each value in the input set is assigned to an element of the array. Syntax: ARRAY_AGG (expression [ORDER BY [sort_expression {ASC | DESC}], [...]) しかし、 ARRAY_AGG と UNNEST の2つは少し複雑です。. 基本的な使い方を以下で紹介します。. 基本的にテーブルのある要素を配列化したい場合は、 ARRAY_AGG を使います（ARRAYはほとんど使いません）。. 重複項目の削除やソートしつつ配列化することもできます ...The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. Jul 13, 2023 · 1 You could solve this, and many other future issues, by changing your schema and not using arrays for this. Stick to 3NF and you wouldn't have this issue. One record, one column, one value. – Frank Heikens 2 hours ago I have to stick with array columns, it's as per requirement – Md. Parvez Alam 2 hours ago Fully managed database for MySQL, PostgreSQL, and SQL Server. Database Migration Service Serverless, minimal downtime migrations to the cloud. Bare Metal Solution for Oracle ... ARRAY_AGG: Returns an array of values. ARRAY_CONCAT_AGG: Concatenates arrays and returns a single array as a result. …The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values.This ordering is unspecified by default, but can be controlled by writing an …I've tried to bypass the ARRAY_AGG by selecting from UNNEST with OFFSET and then WHERE the offset would be the index. However, now there's a column limitation (not more than one column in inside a scalar sub-query SELECT clause) suggesting to use a SELECT AS STRUCT instead.SQLAlchemy, array_agg, and matching an input list. I am attempting to use SQLAlchemy more fully, rather than just falling back to pure SQL at the first sign of distress. In this case, I have a table in a Postgres database (9.5) which stores a set of integers as a group by associating individual items atom_id with a group identifier group_id.Is there a way to optimise an array of subquery in a SQL select? 5. DISTINCT with two array_agg (or one array_agg with tuple inside)? 2. Most efficient way to retrieve rows of related data: subquery, or separate query with GROUP BY? 1. Whether to appear in the group by clause OR be used in an aggregate function? 3.Oct 4, 2016 · 4 Answers Sorted by: 11 BigQuery finally implemented suggestion from Elliott Brossard and now you can do: SELECT user_id, ARRAY_AGG (DISTINCT field1 IGNORE NULLS) AS f1, ARRAY_AGG (DISTINCT field2 IGNORE NULLS) AS f2 FROM t GROUP BY user_id Share Improve this answer Follow answered Dec 23, 2020 at 16:31 Sebastian Kreft 7,789 3 23 40 Add a comment 5 To solve that, I want to use ARRAY_AGG to roll up the individual tag numbers into an array, against which I can (should) easily test values for membership. SELECT items.id, items.title FROM items INNER JOIN tags ON (tag.tag_id=items.id) GROUP BY items.id HAVING ANY(ARRAY_AGG(tags.tag_id)=27)The type of the array needs to have the same type as the column. Optionally the ORDER BY clause can be used to determine the order of the elements in the array. If it is not specified, the array elements are ordered non-deterministic. In the following example all elements of array id are sorted descending by column B.Oct 6, 2017 · I am trying to use ARRAY_AGG with ORDINAL to select only the first two "Action"s for each user and restructure as shown in TABLE 2. SELECT UserId, ARRAY_AGG( STRUCT(A.Action, A.Visit, A.Order) ORDER BY A.Visit, A.Order, A.Action ) FROM `table` LEFT JOIN UNNEST(A) AS A GROUP BY UserId Table 1: (Sample output of above query ) If you don't actually need an array, you can scrap array_agg (), return individual rows and declare the return type with RETURNS TABLE (...). Search SO for the plpgsql tag, you'll find many examples .. Remember to call a set-returning function with: SELECT * FROM foo (); Share.Oct 6, 2017 · I am trying to use ARRAY_AGG with ORDINAL to select only the first two "Action"s for each user and restructure as shown in TABLE 2. SELECT UserId, ARRAY_AGG( STRUCT(A.Action, A.Visit, A.Order) ORDER BY A.Visit, A.Order, A.Action ) FROM `table` LEFT JOIN UNNEST(A) AS A GROUP BY UserId Table 1: (Sample output of above query ) To create an array of multiple data values in SQL, you’ll likely leverage the ARRAY_AGG function (short for array aggregation ), which puts your input column values into an array. How to use SQL ARRAY_AGG The ARRAY_AGG function has the following syntax:Jul 13, 2023 · 1 You could solve this, and many other future issues, by changing your schema and not using arrays for this. Stick to 3NF and you wouldn't have this issue. One record, one column, one value. – Frank Heikens 2 hours ago I have to stick with array columns, it's as per requirement – Md. Parvez Alam 2 hours ago The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. Jul 13, 2023 · 1 You could solve this, and many other future issues, by changing your schema and not using arrays for this. Stick to 3NF and you wouldn't have this issue. One record, one column, one value. – Frank Heikens 2 hours ago I have to stick with array columns, it's as per requirement – Md. Parvez Alam 2 hours ago Some aggregate functions such as array_agg () produce different results depending on the order of input values. This ordering can be specified by writing an ORDER BY clause within the aggregate function: array_agg(x ORDER BY y DESC) array_agg(x ORDER BY x, y, z) Filtering during aggregation The sole aggregation for arrays takes all the values in a column and aggregates them into one field. Returns: array Example Arrays are currently only supported on tables from a live connection to an Athena database. You cannot use them with other databases or with tables derived from an Athena database. query:The SQL/JSON function JSON_ARRAYAGG is an aggregate function. It takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values. Table 2: (The format needed) So I need to: Get distinct "Action" values for each user. Preserve the order ( UserID, Visit, Order ) Show only the 1st and 2nd actions in one row. My attempted query strategy was to ORDER BY UserID, Visit, Order and get DISTINCT values of Action using something like: UserId, ARRAY_AGG (DISTINCT Action ORDER BY ...2 Answers. SELECT array_agg (table_name::text) FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'; You need to cast the table name to text. The subquery is unnecessary, and you need to use array_agg not the array pseudo-function. Personally I don't see why you need to …Then you can filter out by the rules you described in your notes. With temp as ( SELECT A, COALESCE (B, ''), ARRAY_AGG (DISTINCT C) FROM X GROUP BY A, COALESCE (B , '') ) Select * From temp Where only get rows that fit your criteria. @A Simple Programmer thanks for response. Your suggestion still leaves me with the same …the function array_length (anyarray, int) require two elements, array and dimension for example: If you are only dealing with a single dimension array, cardinality () is easier to use: SELECT cardinality (ARRAY_LENGTH (a.actors)) FROM ( SELECT ARRAY_AGG (first_name || ' ' || last_name) actors FROM film ) a;Jul 13, 2023 · 1 You could solve this, and many other future issues, by changing your schema and not using arrays for this. Stick to 3NF and you wouldn't have this issue. One record, one column, one value. – Frank Heikens 2 hours ago I have to stick with array columns, it's as per requirement – Md. Parvez Alam 2 hours ago Summary: in this tutorial, you will learn how to use the SQL Server STRING_AGG() function to concatenate rows of strings into one string with a specified separator.. SQL Server STRING_AGG() function overview. The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator. …If you want to create a JSON array, then use JSON functions: select jsonb_agg(concat_ws(',', us.name, us.age, us.gender)) from users as us; If you want real JSON objects (using key/value pairs) rather than comma separated strings inside the array: select jsonb_agg(to_jsonb(u)) from ( select name, age, gender from users ) u4 Answers Sorted by: 11 BigQuery finally implemented suggestion from Elliott Brossard and now you can do: SELECT user_id, ARRAY_AGG (DISTINCT field1 IGNORE NULLS) AS f1, ARRAY_AGG (DISTINCT field2 IGNORE NULLS) AS f2 FROM t GROUP BY user_id Share Improve this answer Follow answered Dec 23, 2020 at 16:31 Sebastian Kreft 7,789 3 23 40 Add a comment 5I'm trying to join a table that might have multiple entries for the given id and aggregate the rows corresponding to this id in an array. This looks as follows in the SQL query: SELECT * from data LEFT JOIN (select id, array_agg(row(foo, bar)) AS foo_bar_data from foo_bar_table group by id) AS temp using(id)To create an array of multiple data values in SQL, you’ll likely leverage the ARRAY_AGG function (short for array aggregation ), which puts your input column values into an array. How to use SQL ARRAY_AGG The ARRAY_AGG function has the following syntax: WITH t1 AS ( SELECT user_id, ARRAY_AGG(DISTINCT field1) AS f1 FROM t WHERE field1 IS NOT NULL GROUP BY user_id ), t2 AS ( SELECT user_id, ARRAY_AGG(DISTINCT field2) AS f2 FROM t WHERE field2 IS NOT NULL GROUP BY user_id ) SELECT t1.user_id, f1, f2 FROM t1 FULL JOIN t2 ON t1.user_id = t2.user_idSyntax ARRAY_UNIQUE_AGG( <column> ) Arguments column The column containing the values. Returns The function returns an ARRAY containing the distinct values in the specified column. The values in the ARRAY are in no particular order, and the order is not deterministic. The function ignores NULL values in column.The ARRAY_AGG function can only be specified within an SQL procedure, compiled SQL function, or compound SQL (compiled) statement the following specific contexts (SQLSTATE 42887): The select-list of a SELECT INTO statement. The select-list of a …Jul 30, 2009 · Functions abs acos acosh add_months aes_decrypt aes_encrypt aggregate and any any_value approx_count_distinct approx_percentile array array_agg array_append array_compact array_contains array_distinct array_except array_insert array_intersect array_join array_max array_min array_position array_remove array_repeat array_size array_sort array_union Description Returns expression for some row chosen from the group. Which row is chosen is nondeterministic, not random. Returns NULL when the input produces no rows. Returns NULL when expression is...PostgreSQL ARRAY_AGG() function is an aggregate function that accepts a set of values and returns an array where each value in the input set is assigned to an element of the array. Syntax: …The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. If you don't actually need an array, you can scrap array_agg (), return individual rows and declare the return type with RETURNS TABLE (...). Search SO for the plpgsql tag, you'll find many examples .. Remember to call a set-returning function with: SELECT * FROM foo (); Share.Description Returns expression for some row chosen from the group. Which row is chosen is nondeterministic, not random. Returns NULL when the input produces no rows. Returns NULL when expression is... 2 Answers. Sorted by: 3. Use unnest () in FROM clause (in a lateral join): select array_agg (distinct elem order by elem desc) as result from example cross join unnest (date_array) as elem where elem is not null. Test it in DB Fiddle. A general note. An alternative solution using an array constructor is more efficient, especially in cases as ...The ARRAY_AGG function can only be specified within an SQL procedure, compiled SQL function, or compound SQL (compiled) statement the following specific contexts (SQLSTATE 42887): The select-list of a SELECT INTO statement. The select-list of a fullselect in the definition of a cursor that is not scrollable.Edit 1: I have added another solution that shows how to simulate ARRAY_AGG on SQL Server (the last answer). Edit 2: For the solution number 4) I have added the third method for concatenation. I'm not sure I have I understood correctly your question. a) Instead of using arrays in SQL Server I would use table variables or XML.SQLite now has the JSON1 extension (which ships in the default distribution) that can group and create arrays of JSON objects. For example, select ot.MCode, json_group_array(json_object('tname', tk1.TName, 'ttime', ot.TTime)) as oujyu_name_list from TR_A as ot inner join MS_B as tk1 on (ot.Code = tk1.Code) where ot.Code in (select …The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values.This ordering is unspecified by default, but can be controlled by writing an …<order_clause> Optionally specify order of concatenated results using WITHIN GROUP clause: syntaxsql WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) <order_by_expression_list> A list of non …The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values.I'd like to create a column ITEMS_AGG which contains an aggregate of all the arrays from previous rows, i.e. something like: DATE ITEMS ITEMS_AGG 1 a, b a, b 2 a, c a, b, c 3 b, c a, b, c 4.Group by fruit value: COUNT () for distinct id s with each fruit group. if the id column is really empty, you could generate the id values for example with the row_number () window function: SELECT * FROM ( SELECT *, row_number () OVER () as id FROM example ) s, unnest (flavors) AS flavor.The ARRAY_AGG function can only be specified within an SQL procedure, compiled SQL function, or compound SQL (compiled) statement the following specific contexts (SQLSTATE 42887): The select-list of a SELECT INTO statement. The select-list of a fullselect in the definition of a cursor that is not scrollable.Jun 7, 2012 · 235 Use array_agg: http://www.sqlfiddle.com/#!1/5099e/1 SELECT s.name, array_agg (g.Mark) as marks FROM student s LEFT JOIN Grade g ON g.Student_id = s.Id GROUP BY s.Id By the way, if you are using Postgres 9.1, you don't need to repeat the columns on SELECT to GROUP BY, e.g. you don't need to repeat the student name on GROUP BY.