How to string_agg an array in SQL?

Question

I want to aggregate rows using the string aggregate function, however one column is an array.

I have three columns: Employee, Year, Grades. The column Grade is an array. Example:

Row (1) : Employee: Tom | Year: 2019 | Grades: 11,33
Row (2) : Employee: Tom | Year: 2018 | Grades: 45,24,54

'Grade' column is an array. It represents grades for the different tests he took that year

select 
Employee, 
string_agg (distinct Year, ','),
string_agg (distinct Grades, ',')
from Employee_grades
group by 1

ERROR:

function string_agg(bigint[], unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Lukasz Szozda · Accepted Answer · 2019-09-10 15:10:03Z

4

You should use casting:

select Employee,
      string_agg(distinct Year::text, ','),
      string_agg(distinct Grades::text, ',') 
from Employee_grades 
group by Employee  -- GROUP BY 1 is a bad practice

answered Sep 10, 2019 at 15:10

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hamza Khalid Over a year ago

Thank you. also thanks for mentioning good/bad practices. helped a lot

Gordon Linoff · Accepted Answer · 2019-09-10 15:23:56Z

4

If you don't really care about removing duplicates, you can convert to a string using array_to_string():

select employee, array_agg(distinct year),
       string_agg(array_to_string(grades, ','), ',')
from (values ('Tom', 2019, array[11, 33]),
             ('Tom', 2018, array[10, 20, 30])
     ) v(employee, year, grades)
group by employee;

If you actually want to remove duplicates, this is a bit trickier. Unnesting and de-duping may be the only approach:

with t as (
      select *
      from (values ('Tom', 2019, array[11, 33]),
                   ('Tom', 2018, array[11, 20, 30])
           ) v(employee, year, grades)
     )
select t.*,
       (select array_agg(distinct grade)
        from t t2 cross join lateral
             unnest(grades) grade
        where t2.employee = t.employee
       ) as grades
from (select employee, array_agg(distinct year) as year
      from t
      group by t.employee
     ) t;

I prefer to leave the results as arrays, but the results can easily be converted to strings if you prefer.

edited Sep 10, 2019 at 15:23

answered Sep 10, 2019 at 15:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Hamza Khalid Over a year ago

Wow this was really helpful. Thanks : )

Collectives™ on Stack Overflow

How to string_agg an array in SQL?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related