i am using Postgresql 11. I have two tables:
Report_template
id (int) |
template_blocks_id (int[]) |
|---|---|
| 1 | 1,2,3 |
| 2 | 3,2,1 |
| 3 | 2,2,3,1 |
| 4 | 3,1 |
Report_template_block
id (int) |
code (text) |
|---|---|
| 1 | Block #1 |
| 2 | Block #2 |
| 3 | Block #3 |
| 4 | Block #4 |
How can I get the aggregate of all report_template_block code's indicated in the array of the report_template in the same order?
I want to get that:
report_template_id |
aggregated_code |
|---|---|
| 1 | "Block #1, Block #2, Block #3" |
| 2 | "Block #3, Block #2, Block #1" |
| 3 | "Block #2, Block #2, Block #3, Block #1" |
| 4 | "Block #3, Block #1" |
For now i have next SQL query, but that query don't consider repeat of report_pattern_block.id:
SELECT report_template.id,
(select string_agg(code, '\n')
from report_template_block
where id = unnest(report_template.template_blocks_id)) as generated_template
FROM report_template
CodePudding user response：
You need to join to the result of the unnest in order to repeat the rows:
select rt.id as report_template_id,
string_agg(rtb.code, ',' order by b.idx) as aggregated_code
from report_template rt
cross join unnest(rt.template_blocks_id) with ordinality as b(template_id, idx)
join report_template_block rtb on rtb.id = b.template_id
group by rt.id
order by rt.id;