join - Merging multiple rows into single row PostgreSQL -

i have 1 main table called deliveries , has 1 many relationship deliveries_languages dl, deliveries_markets dm , deliveries_tags dt having delivery_id foreign key. these 3 tables have 1 one relation languages , markets , tags respectively. additionaly, deliveries, table have 1 one relation companies , have company_is foreign key. following query have written:

select deliveries.*, languages.display_name, markets.default_name, tags.default_name, companies.name deliveries join deliveries_languages dl on dl.delivery_id = deliveries.id  join deliveries_markets dm on dm.delivery_id = deliveries.id  join deliveries_tags dt on dt.delivery_id = deliveries.id  join languages on languages.id = dl.language_id  join markets on markets.id = dm.market_id  join tags on tags.id = dt.tag_id  join companies on companies.id = deliveries.company_id   deliveries.name ilike '%new%' ,  deliveries.created_by = '5f331347-fb58-4f63-bcf0-702f132f97c5' ,  deliveries.deleted_at null  limit 10 

here getting redundant delivery_ids because each delivery_id there multiple languages, markets , tags. want use limit on distinct delivery_ids , @ same time, want multiple languages, markets , tags grouped , populate in single row.

currently looks like:

delivery_id | name |languages | markets  | tags ------------|------|----------|----------|----------- 1           | d1   |en        | au       | tag1 1           | d1   |de        | sw       | tag2 2           | d2   |en        | au       | tag1 2           | d2   |de        | sw       | tag2 3           | d3   |en        | au       | tag1 3           | d3   |de        | sw       | tag2 

is tere way can have data below:

delivery_id | name |languages | markets  | tags ------------|------|----------|----------|----------- 1           | d1   |en, de    | au,sw    | tag1, tag2 2           | d2   |en, de    | au,sw    | tag1, tag2 3           | d3   |en, de    | au,sw    | tag2, tag3 

p.s. above tables contain part of data, actual query returns many more columns above important 1 here. can please me resolve issue.

you can use group by string_agg this:

select deliveries.deliver_id, deliver.name,         string_agg(distinct languages.display_name, ',' order languages.display_name) langs,         string_agg(distinct markets.default_name, ',' order markets.default_name) markets,        string_agg(distinct tags.default_name, ',' order tags.default_name) tags,        string_agg(distinct companies.name, ',' order companies.name) companies     ...     group deliveries.deliver_id, deliver.name;