PostgreSQL ARRAY_AGG return separate arrays

Question

The use case is this: each user can create their own games, and keep track in which country they played a game.

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.

I have 4 tables: users, games, countries and a games_countries_xref table.

CREATE SEQUENCE countries_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."countries" (
    "id" integer DEFAULT nextval('countries_id_seq') NOT NULL,
    "name" character varying(200) NOT NULL,
    CONSTRAINT "countries_pkey" PRIMARY KEY ("id")
) WITH (oids = false);


INSERT INTO "countries" ("id", "name") VALUES
(1, 'USA'),
(2, 'Japan'),
(3, 'Australia');

CREATE SEQUENCE games_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 3 CACHE 1;

CREATE TABLE "public"."games" (
    "id" integer DEFAULT nextval('games_id_seq') NOT NULL,
    "user_id" integer NOT NULL,
    "name" character varying(200) NOT NULL,
    CONSTRAINT "games_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

INSERT INTO "games" ("id", "user_id", "name") VALUES
(1, 1,  'Monopoly'),
(2, 1,  'Zelda'),
(3, 2,  'Hide & Seek');

CREATE TABLE "public"."games_countries_xref" (
    "game_id" integer NOT NULL,
    "country_id" integer NOT NULL
) WITH (oids = false);

INSERT INTO "games_countries_xref" ("game_id", "country_id") 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 "public"."users" (
    "id" integer DEFAULT nextval('users_id_seq') NOT NULL,
    "name" character varying(200) NOT NULL,
    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

INSERT INTO "users" ("id", "name") VALUES
(1, 'Jack'),
(2, 'Jason');

when querying the data, I tried using ARRAY_AGG:

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

but that gives me this output:

name     | country_ids
------------------
Monopoly | {1,2,3,2}
Zelda    | {1,2,3,2}

while I am looking for this:

name     | country_ids
------------------
Monopoly | {1,2,3}
Zelda    | {2}

I know I am likely doing something wrong in the subquery, but I can't figure out what. Any ideas?

Belayer · Accepted Answer · 2021-10-13 02:53:33Z

1

You are on the right track with ARRAY_AGG, but just a little over aggressive with the joins. You just need a simple join (1 left, 1 inner) on the 3 tables

    select g.name,array_agg(gcx.country_id) as country_ids  
      from games g 
      join users u on u.id = g.user_id
      left join games_countries_xref gcx on gcx.game_id = g.id
     where u.id = 1
     group by g.name;
 
+----------+-------------+
|   name   | country_ids |
+----------+-------------+
| Monopoly | {1,2,3}     |
| Zelda    | {2}         |
+----------+-------------+

answered Oct 13, 2021 at 2:53

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL ARRAY_AGG return separate arrays

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