Q:

Duplicate arrays on complex join

I want to create denormalized view of a complex structure in my postgresql db.  I have a main entity games, and a few one-to-many relations, such as tags (two types) and files.  The problem is arrays containes items with duplicate ids regardless of distinct clause inside subqueries.  I'll narrow query to the related part:
select
  array_to_json(array_remove(array_agg((
    select distinct t.id where t.type = 1
  )), null))::jsonb as genre_ids
from games g
left outer join game_tags gt on gt.game_id = g.id -- one-to-many
left outer join tags t on gt.tag_id = t.id
left outer join game_files gf on gf.game_id = g.id -- one-to-many as well
left outer join files f on gf.file_id = f.id
left outer join ratings r on r.game_id = g.id
group by g.id;

Let's say I have game with id=1 and relations
# game_tags (game_id, tag_id)
1, 100

# game_files (game_id, file_id)
1, 1000
1, 1001
1, 1002
1, 1003

Then in query result I'll see array [100, 100, 100, 100].  The same situation when tags amount is greater than one.  I don't know if this because of wrong aggregating section or because of wrong grouping.  I hoped that distinct keyword will do the trick, but it don't.  Of course I can filter items before sending it to the frontend, but I believe this is a wrong way. 
I sticked to pg 9.5. 

A:

The problem you have is that you need to group on different bases: once for the game tags and once for the files.  You can solve this by sub-queries as row sources.  The grouping takes place in the sub-queries so they do not influence each other:
select g.*, t.genre_ids, f.file_ids
from games g
left join (
  select gt.game_id, jsonb_agg(t.id) as genre_ids
  from game_tags gt
  join tags t on gt.tag_id = t.id
  where t."type" = 1
  group by gt.game_id) t on t.game_id = g.id
left join (
  select game_id, jsonb_agg(file_id) as file_ids
  from game_files
  group by game_id) f on f.game_id = g.id
left join ratings r on r.game_id = g.id -- not used
;

