Postgres - Aggregate Two Columns into One Item

Postgres - aggregate two columns into one item

You can concatenate the values together prior to feeding them into the array_agg() function:

SELECT user_id, array_agg('[' || friend_id || ',' || confirmed || ']') as friends
FROM friends_map
WHERE user_id = 1
GROUP BY user_id

Demo: SQL Fiddle

How to group multiple columns into a single array or similar?

(t.id, t.tag_name, t.tag_color) is short syntax for ROW(t.id, t.tag_name, t.tag_color) - and a ROW constructor does not preserve nested attribute names. The manual:

By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type —
either the row type of a table, or a composite type created with
CREATE TYPE AS.

Bold emphasis mine. To also get proper key names in the result, cast to a registered composite type as advised in the quote, use a nested subselect, or simply use json_build_object() in Postgres 9.4 or newer (effectively avoiding the ROW constructor a priori):

SELECT trm.target_record_id
     , json_agg(json_build_object('id', t.id
                                , 'tag_name', t.tag_name
                                , 'tag_color', t.tag_color)) AS tags
FROM   tags_record_maps trm
JOIN   tags             t USING (site_id)
WHERE  t.id = trm.tag_id
GROUP  BY trm.target_record_id
HAVING count(*) > 1;

I use original column names, but you can chose your key names freely. In your case:

       json_agg(json_build_object('id', t.id
                                , 'name', t.tag_name
                                , 'color', t.tag_color)) AS tags

Detailed explanation:

• Return multiple columns of the same row as JSON array of objects

How to aggregate values in two columns in multiple records into one

You can unpivot and aggregate:

select firstname, lastname, string_agg(pt, ', ') as points
from (select t.*, v.pt,
             row_number() over (partition by firstname, lastname, pt order by pt) as seqnum
      from t cross apply
           (values (t.startpoint), (t.endpoint)) as v(pt)
     ) t
where seqnum = 1
group by firstname, lastname;

Unfortunately, string_agg() doesn't support distinct. However, this is easily remedied by using row_number().

Edit:

If you wanted to identify each separate connected component, then you can use a recursive CTE:

with cte as (
      select id, firstname, lastname,
             convert(varchar(max), concat(startpoint, ', ', endpoint)) as points,
             endpoint
      from t
      where not exists (select 1 from t t2 where t2.endpoint = t.startpoint)
      union all
      select cte.id, cte.firstname, cte.lastname,
             concat(cte.point, ', ', cte.endpoint), t.endpoint
      from cte join
           t
           on t.startpoint = cte.endpoint and t.id = cte.id
    )
select *
from cte;

Here is a db<>fiddle.

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

Create an array from the two columns, the aggregate the array:

select id, array_agg(array[x,y])
from the_table
group by id;

Note that the default text representation of arrays uses curly braces ( {..}) not square brackets ([..])

Aggregate functions across multiple columns in postgres

With a subquery you have all rows at your disposal:

SELECT sum(val) FROM (
    SELECT unnest(array[a1,a2,a3]) as val FROM table) alias;

You can also group your rows, for example:

SELECT field, sum(val) FROM (
    SELECT field, unnest(array[a1,a2,a3]) as val FROM table) alias
GROUP BY field;

Aggregate key column and multiple value columns to JSON object

Your query should be:

SELECT 
    name, 
    json_object_agg(key, json_build_object('x', x, 'y', y)) 
FROM data
GROUP BY name

demo:db<>fiddle

json_object_agg does exactly what you want.

More efficient way to aggregate results in one table when grouping by different columns in SQL

You can LEFT JOIN the results of the two separate aggregation queries, using the two columns id and category:

SELECT * FROM

(SELECT 
  id,
  category,
  subcategory,
  AVG(value1) AS avgvalue1,
  AVG(value2) AS avgvalue2
FROM your_table
GROUP BY 1, 2, 3)

LEFT JOIN

(SELECT 
  id,
  category,
  AVG(value0) AS avgvalue0
FROM your_table
GROUP BY 1, 2)

USING (id, category)

---

Related Topics