PostgreSQL query: left outer join with "where" but also null values if not existing

Question

I have two tables in a PostgreSQL database I cannot modify (COTS software that I'm just trying to fetch information from).

One with user information:

| id | username | mail          |
+----+----------+---------------+
| 1  | admin    | admin@xzy.com |
| 2  | user     | user@xzy.com  |

...and an additional table with user attributes:

| user_id | attribute_name  | attribute_value   |
+---------+-----------------+-------------------+
| 1       | active          | true              |
| 1       | loginDateMillis | 1652176627        |
| 2       | active          | true              |

What I'd need is all users with their loginDateMillis, if existing, otherwise "null" or empty, e.g.:

| id | username | mail          | attribute_value |
+----+----------+---------------+-----------------+
| 1  | admin    | admin@xzy.com | 1652176627      |
| 2  | user     | user@xzy.com  | null            |

At first I tried to join the two tables with a where condition to select only the attribute_name = 'loginDateMillis', but then I'm filtering out the null rows:

> select u.id, u.username, u.mail, a.attribute_value
  from users u
  left outer join user_attributes a
  on a.user_id = u.id
  where a.attribute_name = 'loginDateMillis';

| id | username | mail          | attribute_value |
+----+----------+---------------+-----------------+
| 1  | admin    | admin@xzy.com | 1652176627      |

The only solution I could find to get one line for each user, but also the attributes, was to group by the user columns and use the array_agg function:

> select u.id, u.username, u.mail, array_agg(a.attribute_name), array_agg(a.attribute_value)
  from users u
  left outer join user_attributes a
  on a.user_id = u.id
  group by (u.id, u.username, u.mail);

| id | username | mail          | attribute_name            | attribute_value    |
+----+----------+---------------+---------------------------+--------------------+
| 1  | admin    | admin@xzy.com | {active, loginDateMillis} | {true, 1652176627} |
| 2  | user     | user@xzy.com  | {active}                  | {true}             |

Which is "okayish", because I can then filter the arrays in my application, but maybe I'm missing something obvious here? Many thanks in advance.

Answer 1

If you move the logic in the WHERE clause to the ON clause, it should work as you expect:

select u.id, u.username, u.mail, a.attribute_value
from users u
left join user_attributes a
on a.user_id = u.id and a.attribute_name = 'loginDateMillis';