Создание нескольких необязательных объединений в одной таблице с использованием массива json в postgres

У меня есть две таблицы users и rooms. Я пытаюсь присоединиться к обеим таблицам и получить список имен пользователей, связанных с комнатой. Я получаю результаты, только если оба столбца имеют значения. Если какой-либо из столбцов имеет значение null, он не дает значения ненулевого столбца. Пожалуйста, ознакомьтесь с подробностями ниже.

схема пользователей:

id  name
---------
1   X
3   Y
4   Z

схема комнат:

id  active_users    inactive_users
-----------------------------------
101     [1]         [3]
102     [3]         null
103     null        [4]

Проверенный запрос:

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

Вывод запроса:

id  active_users    inactive_users
----------------------------------
101     ["X"]       ["Y"]

Ожидаемый результат:

id  active_users    inactive_users
----------------------------------
101     ["X"]       ["Y"]
102     ["Y"]       NULL
103     NULL        ["Z"]

postgresql postgresql-11
person ahamdsuhail    schedule 30.01.2021    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Ваша основная проблема здесь заключается в том, что вы выполняете внутреннее соединение с функцией json_array_elements(active_users), которая возвращает значение null, когда active_users равно нулю. Следовательно, это соединение исключает те строки, которые вы хотите включить. Это будет работать, если вы также сделаете эти соединения внешними соединениями:

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;

Вы заметите, что я также добавил FILTERs в выборку, поэтому пустые имена пользователей исключаются из агрегации.

person Blue Star    schedule 30.01.2021