Created
June 22, 2015 05:20
-
-
Save adamanthil/c03c99a72a47abf2b448 to your computer and use it in GitHub Desktop.
An adjusted query to return nested objects in a brand database. Fixes issue where two separate joins caused additional values in aggregates.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--use images and logos tables | |
with images as ( | |
select | |
im.brand, | |
json_agg( | |
row_to_json( | |
(select r from (select im.name, im.path as poster) r) | |
) | |
) as images | |
from images im | |
group by im.brand | |
), | |
logos as ( | |
select | |
lg.brand, | |
json_agg( | |
row_to_json( | |
(select x from (select lg.brand as name, lg.path as poster) x) | |
) | |
) as logos | |
from logos lg | |
group by lg.brand | |
) | |
--select brand id and name | |
select | |
b.id, | |
b.name, | |
im.images, | |
lg.logos | |
from brand b | |
inner join images im on im.brand = b.id | |
inner join logos lg on lg.brand = b.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment