时间戳

首页 > 解决方案 > 如何向 json{b}_build_object 添加 N 个条目，其中 N 是表中的行数？

sql - 如何向 json{b}_build_object 添加 N 个条目，其中 N 是表中的行数？

问题描述

鉴于此表设置：

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 上。

标签： sqljsonpostgresql

解决方案

您可以使用json_objectandarray_agg与 group by 来实现此结果。请参阅下面的工作小提琴示例：

查询 #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"}

在 DB Fiddle 上查看

编辑1：

以下修改使用左连接和 case 表达式为包含空值的结果提供替代方案：

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;

在 DB Fiddle 上查看

让我知道这是否适合您。

推荐阅读