Postgresql view how to do the query

Question

I have 4 tables:

Project: id name

Keyword: id, name

ProjectKeyword: project_id, keyword_id (project has many keywords, keywords has many projects)

ProjectReport: project_id, keyword_id, position(keyword, project has many project_reports)

ProjectCompetitorReport: project_report, keyword_id, position, competitor_idposition`(keyword, project, competitor has many project_reports)

With the help of those tables I need to produce something like this output:

Keyword.name, project_report.position, project_competitor_report.position

The problem that I have is repetition, I don't now how to group the result.

SELECT
    k.name,
    pr1.position,
    pr2.position
from
    project_keywords p1
    JOIN
    keywords k on p1.keyword_id = k.id
    JOIN
    project_reports pr1 on
        p1.project_id = pr1.project_id AND p1.keyword_id = pr1.keyword_id
    JOIN
    project_competitor_reports pr2 on
        p1.project_id = pr2.project_id AND p1.keyword_id = pr2.keyword_id;

I need to group the result produced by that query so that for each keyword i get all the positions from project_reports and all positions from project_position_reports.

As an exemple: keyword1 has position with value: 1 and 2 on project_report keyword1 has position whth value: 2 on project_competitor_reports

For this the output should be:

keyword1 1,2 2

At the moment I get them like this:

keyword1 1, 2

keyword1 2, 2

Answer 1

Use array_agg or string_agg

select
    k.name,
    array_agg(pr.position order by pr.position) as pr_position,
    array_agg(pcr.position order by pcr.position) as pcr_position
from
    project_keywords pk
    inner join
    keywords k on pk.keyword_id = k.id
    inner join
    project_reports pr using (project_id,  keyword_id)
    inner join
    project_competitor_reports pcr using (project_id, keyword_id)
group by k.name
;