Блог

Учитывая эту настройку таблицы:

 create table accounts (
    id char(4) primary key,
    first_name varchar not null
);

create table roles (
    account_id char(4) references accounts not null,
    role_type varchar not null,
    role varchar not null,

    primary key (account_id, role_type)
);
 

и первоначальная вставка учетной записи:

 insert into accounts (id, first_name) values ('abcd', 'Bob');
 

Я хочу получить всю информацию об учетной записи кого-либо, а также о ролях, которые у них есть в качестве пар ключ-значение. Использование соединения для этого отношения «один ко многим» будет дублировать информацию об учетной записи в каждой строке, содержащей роль, поэтому вместо этого я хочу создать объект JSON. Используя этот запрос:

 select
    first_name,
    coalesce(
        (select jsonb_build_object(role_type, role) from roles where account_id = id), 
        '{}'::jsonb
    ) as roles
from accounts where id = 'abcd';
 

Я получаю этот ожидаемый результат:

  first_name | roles 
------------ -------
 Bob        | {}
(1 row)
 

После добавления первой роли:

 insert into roles (account_id, role_type, role) values ('abcd', 'my_role_type', 'my_role');
 

Я получаю еще один ожидаемый результат:

  first_name |            roles            
------------ -----------------------------
 Bob        | {"my_role_type": "my_role"}
(1 row)
 

Но после добавления второй роли:

 insert into roles (account_id, role_type, role) values ('abcd', 'my_other_role_type', 'my_other_role');
 

Я получаю:

 ERROR:  more than one row returned by a subquery used as an expression
 

Как мне заменить эту ошибку на

  first_name |            roles            
------------ -----------------------------
 Bob        | {"my_role_type": "my_role", "my_other_role_type": "my_other_role"}
(1 row)
 

?

Я нахожусь на Postgres v13.

Вы можете использовать json_object и array_agg с группой для достижения этого результата. Смотрите пример с рабочей скрипкой ниже:

Запрос № 1

 select
    a.first_name,
    json_object(
         array_agg(role_type),
         array_agg(role)
    )
from accounts a
inner join roles r on r.account_id = a.id
where a.id = 'abcd'
group by a.first_name;
 

Просмотр на скрипке БД

Изменить 1:

Следующая модификация использует левое соединение и выражение регистра, чтобы предоставить альтернативу результатам, содержащим нулевые значения:

 select
    a.first_name,
    CASE 
        WHEN COUNT(role_type)=0 THEN '{}'::json
        ELSE
            json_object(
                array_agg(role_type),
                array_agg(role)
            )
    END as role
from accounts a
left join roles r on r.account_id = a.id
group by a.first_name;
 

Просмотр на скрипке БД

Дайте мне знать, если это сработает для вас.