title: PostgreSQL ARRAY_AGG return separate arrays
body: <p>The use case is this: each user can create their own games, and keep track in which country they played a game.</p>
  <p>I would like to create one query where I can get a list of all games for that user and in which country that game was played. I am only interested in the country id.</p>
  <p>I have 4 tables: users, games, countries and a games_countries_xref table.</p>
  <pre><code>CREATE SEQUENCE countries_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;
  
  CREATE TABLE &quot;public&quot;.&quot;countries&quot; (
      &quot;id&quot; integer DEFAULT nextval('countries_id_seq') NOT NULL,
      &quot;name&quot; character varying(200) NOT NULL,
      CONSTRAINT &quot;countries_pkey&quot; PRIMARY KEY (&quot;id&quot;)
  ) WITH (oids = false);
  
  
  INSERT INTO &quot;countries&quot; (&quot;id&quot;, &quot;name&quot;) VALUES
  (1, 'USA'),
  (2, 'Japan'),
  (3, 'Australia');
  
  CREATE SEQUENCE games_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 3 CACHE 1;
  
  CREATE TABLE &quot;public&quot;.&quot;games&quot; (
      &quot;id&quot; integer DEFAULT nextval('games_id_seq') NOT NULL,
      &quot;user_id&quot; integer NOT NULL,
      &quot;name&quot; character varying(200) NOT NULL,
      CONSTRAINT &quot;games_pkey&quot; PRIMARY KEY (&quot;id&quot;)
  ) WITH (oids = false);
  
  INSERT INTO &quot;games&quot; (&quot;id&quot;, &quot;user_id&quot;, &quot;name&quot;) VALUES
  (1, 1,  'Monopoly'),
  (2, 1,  'Zelda'),
  (3, 2,  'Hide &amp; Seek');
  
  CREATE TABLE &quot;public&quot;.&quot;games_countries_xref&quot; (
      &quot;game_id&quot; integer NOT NULL,
      &quot;country_id&quot; integer NOT NULL
  ) WITH (oids = false);
  
  INSERT INTO &quot;games_countries_xref&quot; (&quot;game_id&quot;, &quot;country_id&quot;) VALUES
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 2),
  (3, 1);
  
  CREATE SEQUENCE users_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 2 CACHE 1;
  
  CREATE TABLE &quot;public&quot;.&quot;users&quot; (
      &quot;id&quot; integer DEFAULT nextval('users_id_seq') NOT NULL,
      &quot;name&quot; character varying(200) NOT NULL,
      CONSTRAINT &quot;users_pkey&quot; PRIMARY KEY (&quot;id&quot;)
  ) WITH (oids = false);
  
  INSERT INTO &quot;users&quot; (&quot;id&quot;, &quot;name&quot;) VALUES
  (1, 'Jack'),
  (2, 'Jason');
  </code></pre>
  <p>when querying the data, I tried using ARRAY_AGG:</p>
  <pre><code>WITH country_ids AS (
      SELECT g.user_id, ARRAY_AGG(gcx.country_id) AS country_ids
      FROM games AS g
      LEFT JOIN games_countries_xref AS gcx ON g.id = gcx.game_id
      GROUP BY g.user_id
  )
  SELECT g.name, country_ids
  FROM games AS g
      NATURAL LEFT JOIN country_ids
  WHERE g.user_id = 1
  </code></pre>
  <p>but that gives me this output:</p>
  <pre><code>name     | country_ids
  ------------------
  Monopoly | {1,2,3,2}
  Zelda    | {1,2,3,2}
  </code></pre>
  <p>while I am looking for this:</p>
  <pre><code>name     | country_ids
  ------------------
  Monopoly | {1,2,3}
  Zelda    | {2}
  </code></pre>
  <p>I know I am likely doing something wrong in the subquery, but I can't figure out what.
  Any ideas?</p>