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
;

• RockNinja: There will be keywords having the position 0, meaning that they will not be stored in the DB on project_report, or project_competitor_reports what do I need to change to capture those keywords as well? Thank you for your time.