Merge results from multiple joins into single row using postgresql

Question

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?

Are you looking something like that: array_agg(distinct sub2.lang)? — AlbertK
– AlbertK, Commented Jan 29, 2018 at 11:43
@Albert Ah yes, I think you are right! (I am new to SQL, so it makes sense I would have missed something so simple!). Thanks! (if you want to submit an answer, I can mark as correct) — alexc
– alexc, Commented Jan 29, 2018 at 11:47
Unrelated, but: array_to_json(array_agg(sub1.lang)) can be simplified to json_agg(sub1.lang) — user330315
– user330315, Commented Jan 29, 2018 at 11:48

AlbertK · Accepted Answer · 2018-01-29 11:51:39Z

5

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)

answered Jan 29, 2018 at 11:51

AlbertK

13.5k5 gold badges45 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Merge results from multiple joins into single row using postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related