how to split a column that is created with array_agg

Question

I'm trying to split up an arrayed column that was created using an array_agg. the following is severely cut down, original query is something like 210 lines

select 
 distinct on (visitor.id)
 id,
 array_agg(distinct item.code::text)

from
 8xfullouter joins

where
exists(
select
distinct on(visitor.id)
item.code::text
from
3 full outer join that all appear in the first query

group by
visit.id, 
item.code

order by 
visit.id
)

I need to break the array_agg(distinct item.code::text) into multiple columns. I've tried split_part(array_agg(distinct item.code::text), ',', 1) but received the following

> [Err] ERROR:  function split_part(character varying[], unknown,
> integer) does not exist LINE 159: split_part (array_agg(distinct
> "public".procedure_group_cpt_...

thanks!!!

Answer 1

Use an index of the array, e.g.:

select (array_agg(code::text))[1] 
from (values ('code1'), ('code2')) as codes(code)

 array_agg 
-----------
 code1
(1 row)

Alternatively, you can use string_agg() instead of array_agg(), e.g.:

select split_part(string_agg(code::text, ','), ',', 1)
from (values ('code1'), ('code2')) as codes(code)

 split_part 
------------
 code1
(1 row)