CODEWITHSUNDEEP
sqlpostgresql
alexc
alexc

Reputation: 1310

Merge results from multiple joins into single row using postgresql

I'm trying use join queries to construct a single row of results which wrap the rows from joined tables into a json array.

Here is an example scheme;

CREATE TABLE main(id int);
INSERT INTO main values(1);

create TABLE sub1(id int, main_id int, lang int);
insert into sub1 values (1, 1, 1);
insert into sub1 values (1, 1, 2);

create TABLE sub2(id int, main_id int, lang int);
insert into sub2 values(1, 1, 1);

My query:

select main.id, 
       array_to_json(array_agg(sub1.lang)) as sub1, 
       array_to_json(array_agg(sub2.lang)) as sub2
from main
     inner join sub1 on main.id = sub1.main_id
     inner join sub2 on main.id = sub2.main_id
where main.id = 1
group by main.id

At the moment, this returns;

| id | sub1  | sub2  | 
|----|-------|-------| 
| 1  | [1,2] | [1,1] |

I would like the result to look like this; (This is just taking the lang from the table)

| id | sub1  | sub2 | 
|----|-------|------| 
| 1  | [1,2] | [1]  |

I have an sql fiddle here:

http://sqlfiddle.com/#!17/60c3f/2

I've looked into using a UNION query, but that returned multiple rows and duplicate data. I'm hoping that I will be able to do this in a similar way to what I've tried above, but I'm not sure how/if it is possible?

Upvotes: 6

Views: 1844

Answers (1)

AlbertK
AlbertK

Reputation: 13227

You can use the distinct keyword inside array_agg to remove duplicates:

array_to_json(array_agg(distinct sub2.lang))

And as @a_horse_with_no_name suggested it can be simplified to:

json_agg(distinct sub1.lang)

Upvotes: 5

Related Questions