Skip to content

Instantly share code, notes, and snippets.

@adamanthil
Created June 22, 2015 05:20
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save adamanthil/c03c99a72a47abf2b448 to your computer and use it in GitHub Desktop.
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.
--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