Return first element in array_agg()
Very simply, do not aggregate the team_name
but GROUP BY
it:
SELECT team_id, team_name, array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
JOIN players ON team_id = player_team
GROUP BY team_id, team_name;
It was actually a mistake from my part... the answer to my first question lies in that query itself. I just have to enclose the (array_agg(team_name))[1]
, earlier I tried it without the brackets.
SELECT team_id, (array_agg(team_name))[1] AS teamname,
array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id