代码之家 › 专栏 › 技术社区 › Mario Ishac

如何在json中添加N个条目{b}_build_object其中N是表中的行数?

postgresql json sql

Mario Ishac · 技术社区 · 5 年前

给定此表设置:

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。

0 回复 | 直到 5 年前

ggordon 5 年前

您可以使用 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;

名字	json_object
上下快速移动	{“my_role_type”:“my_role”,“my_other_role_type”:“my_other_role”}

View on DB Fiddle

编辑1:

以下修改使用左联接和大小写表达式为包含null值的结果提供替代方案:

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;

View on DB Fiddle

让我知道这是否适合你。