I have two tables users and rooms. I'm trying to join both tables and get list of user names related to the room. I'm getting results only if both columns have values. If any one of the column is null, it's not giving the value of non-null column. Please find the details below.
users schema:
id name
---------
1 X
3 Y
4 Z
rooms schema:
id active_users inactive_users
-----------------------------------
101 [1] [3]
102 [3] null
103 null [4]
Tried query:
SELECT
r.id, json_agg(u.name) as active_users, json_agg(u1.name) as inactive_users
FROM rooms r,
json_array_elements(active_users) as elems
LEFT OUTER JOIN users u
ON u.id = elems::TEXT::INT,
json_array_elements(inactive_users) as elems1
LEFT OUTER JOIN users u1 ON
u1.id = elems1::TEXT::INT
GROUP BY r.id
Query output:
id active_users inactive_users
----------------------------------
101 ["X"] ["Y"]
Expected output:
id active_users inactive_users
----------------------------------
101 ["X"] ["Y"]
102 ["Y"] NULL
103 NULL ["Z"]
Your main issue here is that you're doing an inner join with the json_array_elements(active_users) function, which produces null when active_users is null. Therefore, that join is excluding those lines you want to include. It will work if you make those joins into outer joins as well:
SELECT
r.id,
json_agg(u.name) FILTER (WHERE u.name IS NOT NULL) as active_users,
json_agg(u1.name) FILTER (WHERE u1.name IS NOT NULL) as inactive_users
FROM rooms r
LEFT JOIN json_array_elements(active_users) as elems
ON TRUE
LEFT JOIN json_array_elements(inactive_users) as elems1
ON TRUE
LEFT JOIN users u
ON u.id = elems::TEXT::INT
LEFT JOIN users u1 ON
u1.id = elems1::TEXT::INT
GROUP BY r.id;
You'll notice that I also added FILTERs in the select, that's so null usernames are excluded from the aggregation.