Postgresql - возвращает столбец, полученный в результате соединения, как один столбец json

У меня много отношений между таблицами пользователи и роли (типичный вариант использования). Они связаны между собой с помощью таблицы user_role. Я выполняю следующий запрос:

select 
    u.id, u.first_name, u.last_name, u.middle_name,
    u.email_address, r.id, r.role_code
from 
master.user u
left join
master.user_role ur 
    on ur.user_id = u.id
    and ur.is_void = false
left join
master.role r
    on r.id = ur.role_id
    and r.is_void = false
where u.id = 7  and u.is_void = false

Это приводит к

7;"Multi Role";"First";"Middle";"[email protected]";1;"ST"
7;"Multi Role";"First";"Middle";"[email protected]";2;"TC"

Как агрегировать роли в один столбец json или массива, чтобы в результате получилось:

7;"Multi Role";"First";"Middle";"[email protected]";[{id: 1, role_code : ST}, {id: 2, role_code: TC}]

Исправьте ваш пример: в вашем запросе в столбце «first_name» указано «Multi Role». Я думаю, это неправильно, не так ли?

— 27.10.2018 09:12

sql json postgresql join

27.10.2018 07:11

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

демонстрация: db <> рабочий пример

SELECT 
    id, 
    first_name, 
    last_name, 
    middle_name, 
    email_address, 
    jsonb_agg(
        jsonb_build_object('id', r_id, 'role_code', role_code)
    )
FROM 
    result_table
GROUP BY id, first_name, last_name, middle_name, email_address