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

Question

I would like my query to return a result structured like this, where tags is an array of arrays or similar:

id | name | tags
1    a      [[1, "name1", "color1"], [2, "name2", color2"]]
2    b      [[1, "name1", "color1"), (3, "name3", color3"]]

I expected this query to work, but it gives me an error:

SELECT  i.id, i.name, array_agg(t.tag_ids, t.tag_names, t.tag_colors) as tags 
FROM    ITEMS
LEFT OUTER JOIN (
  SELECT      trm.target_record_id
            , array_agg(tag_id) as tag_ids
            , array_agg(t.tag_name) as tag_names
            , array_agg(t.tag_color) as tag_colors
  FROM        tags_record_maps trm
  INNER JOIN  tags t on t.id = trm.tag_id
  GROUP BY    trm.target_record_id
) t on t.target_record_id = i.id;

Error:

PG::UndefinedFunction: ERROR:  function array_agg(integer[], character varying[], character varying[]) does not exist
LINE 1: ..., action_c2, action_c3, action_name, action_desc, array_agg(...
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

This query works and produces similar results (but not quite what I want):

SELECT  i.id, i.name, t.tag_ids, t.tag_names, t.tag_colors as tags  as tags 
FROM    ITEMS
LEFT OUTER JOIN (
  SELECT      trm.target_record_id, array_agg(tag_id) as tag_ids, array_agg(t.tag_name) as tag_names, array_agg(t.tag_color) as tag_colors
  FROM        tags_record_maps trm
  INNER JOIN  tags t on t.id = trm.tag_id
  GROUP BY    trm.target_record_id
) t on t.target_record_id = i.id;

Result:

id | name | tag_ids | tag_names         | tag_colors          
1    a      [1, 2]    ["name1, "name2"]   ["color1", "color2"]
1    a      [1, 3]    ["name1, "name3"]   ["color1", "color3"]

Edit:

This query almost produces what I'm looking for, except it names the json keys f1, f2, f3. It would be perfect if I could name them id, name, color:

  SELECT        trm.target_record_id, json_agg( (t.id, t.tag_name, t.tag_color) )
  FROM          tags_record_maps trm
  INNER JOIN    tags t on t.site_id = trm.site_id and t.id = trm.tag_id
  GROUP BY      trm.target_record_id
  having count(*) > 1;

Result:

[{"f1":1,"f2":"name1","f3":"color1"},{"f1":2,"f2":"name2","f3":"color2"}]

@GordonLinoff PostgreSQL 11.0 on x86_64-apple-darwin16.0.0, compiled by Apple LLVM version 8.1.0 (clang-802.0.42), 64-bit — ab11, Feb 11 '19 at 19:33
`array_agg()` only takes a single argument (the column to be aggregated). There is no version of that function that takes three arguments. It seems you are looking for some JSON aggregation. — , Feb 11 '19 at 19:35
Are the "results" at the end of your question an example input that should generate a result like the one in the second line of your question? — Dimitar Spasovski, Feb 11 '19 at 19:49

Erwin Brandstetter · Accepted Answer · 2022-05-05T01:39:45.207

(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

score 2 · Answer 2 · answered Feb 11 '19 at 19:51

2

array_agg() puts one argument into an array. You could try to concatenate the values together:

array_agg(t.tag_ids || ':' || t.tag_names || ':' || t.tag_colors)

Or perhaps use a row constructor:

array_agg( (t.tag_ids, t.tag_names, t.tag_colors) )

answered Feb 11 '19 at 19:51

Gordon Linoff

1,242,037
58
646
786

using a row constructor with `json_agg` almost works for me, but i couldn't figure out how to name the columns in my row constructor (how to name the keys in my json object). could you take a look at my edit? – ab11 Feb 11 '19 at 21:13
convert rows to array: select array_agg(a.*) from item a; – Charlie 木匠 Oct 21 '20 at 18:01

score 0 · Answer 3 · edited Feb 12 '19 at 04:04

0

Why not try a Json_Agg()?

SELECT 
     json_agg(tag_ids, tag_names, tag_colors)
FROM items

Etc...

edited Feb 12 '19 at 04:04

dwir182

1,539
10
20

answered Feb 11 '19 at 19:30

Rupert

150
1
6

any suggestion how to use json_agg for my case? with your example, i get this error `ERROR: function json_agg(integer[], character varying[], character varying[]) does not exist LINE 1: ..., i.id, i.name, json_agg(t...` – ab11 Feb 11 '19 at 20:44

score 0 · Answer 4 · answered Mar 21 '22 at 13:55

DB fiddle
let's play with composite type.
create type tags as(tag_id bigint, tag_name text,tag_color text);

using array_agg:

select item_id,name,  array_agg(row(trm.tag_id, tag_name, tag_color)::tags) as tags
from items i join tags_record_maps trm on i.item_id = trm.target_record_id
group by 1,2;

to json.

select item_id,name, to_json( array_agg(row(trm.tag_id, tag_name, tag_color)::tags)) as tags
from items i join tags_record_maps trm on i.item_id = trm.target_record_id
group by 1,2;

access individual/base element of composite type:

with a as(
select item_id,name,  array_agg(row(trm.tag_id, tag_name, tag_color)::tags) as tags
from items i join tags_record_maps trm on i.item_id = trm.target_record_id
group by 1,2)
select a.item_id, a.tags[2].tag_id from a;

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

4 Answers4

Linked