Reference ARRAY_AGG - length in WHERE clause

Question

My question is similar to possible reference jsonb_array_length in a where clause, however I cannot use ORDER BY.

I have following tables:

CREATE TABLE IF NOT EXISTS games
(
 id SERIAL PRIMARY KEY,
 ...
)

CREATE TABLE IF NOT EXISTS users
(
 id SERIAL PRIMARY KEY,
 username CHARACTER VARYING(100) NOT NULL UNIQUE,
 ...
)

CREATE TABLE IF NOT EXISTS game_users
(
 game_id INTEGER REFERENCES games (id) NOT NULL,
 user_id INTEGER REFERENCES users (id) NOT NULL,
 joined_at TIMESTAMPTZ NOT NULL,
 UNIQUE (game_id, user_id)
);

I want to query games that have less than 4 participants.

SELECT gm.*,
 array_agg(usr.id) AS users_id,
 array_agg(gmUsr.joined_at) AS users_joined,
 array_agg(usr.username) AS users_username
FROM games AS gm
 LEFT JOIN game_users AS gmUsr ON gmUsr.game_id = gm.id
 LEFT JOIN users AS usr ON gmUsr.user_id = usr.id
WHERE cardinality(array_length(users_id)) < 4
GROUP BY gm.id

I already saw in an answer that the WHERE clause is executed before the SELECT one. So my query will not work. However I want to keep GROUP BY user id because I want aggregated data as:

type GamesJoinedUsers = {
 id: number,
 users_id: number[],
 users_joined: Date[],
 users_username: string[],
}

How to query such data at least somehow?

Is it possible to do it in one query without subquery?

• user1822: You need to use having to apply filters on the result of aggregate functions
• Alexey Vol: HAVING also evaluated before the SELECT clause postgresqltutorial.com/postgresql-tutorial/postgresql-having
• Laurenz Albe: That is not true. Try it! And rely on the real documentation.
• Charlieface: @LaurenzAlbe It most definitely is true: HAVING is evaluated before SELECT, this is part of the SQL standard. Here's a simple proof dbfiddle.uk/ZsF7NFLp
• Laurenz Albe: @Charlieface You are right, I should have been more careful. Still, HAVING is the solution to the original question.

Answer 1

It seems to me you just need to replace your WHERE clause with a HAVING clause like this:

HAVING
 COUNT(isr.id) < 4

The HAVING clause is the appropriate place for a filter that needs to apply to an aggregated result. In this case, you want a filter on the number of users per game, which is an aggregate result given your GROUP BY. Therefore, use HAVING.

• Alexey Vol: Thanks! This works. Using HAVING with cardinality(array_length(users_id)) still does not work. However the trick with COUNT(usr.id) is more elegant in this case!