PostgreSQL如何将关联表多行合并为单个jsonb_build_object键值对

报错原因 #

你当前使用的标量子查询仅支持返回单行结果，当账号关联2条及以上角色记录时，子查询返回多行，触发PostgreSQL语法约束报错。

解决方案 #

使用PostgreSQL内置的jsonb_object_agg聚合函数，可直接将多行的role_type（键）和role（值）合并为单个JSONB对象，修改后的查询语句如下：

SELECT
    first_name,
    COALESCE(
        (SELECT jsonb_object_agg(role_type, role) FROM roles WHERE account_id = accounts.id),
        '{}'::jsonb
    ) AS roles
FROM accounts 
WHERE id = 'abcd';

如果需要同时查询多个账号的角色信息，也可以用LEFT JOIN + 分组的写法，逻辑等价：

SELECT
    a.first_name,
    COALESCE(jsonb_object_agg(r.role_type, r.role), '{}'::jsonb) AS roles
FROM accounts a
LEFT JOIN roles r ON a.id = r.account_id
WHERE a.id = 'abcd'
GROUP BY a.id, a.first_name;

效果验证 #

上述语句执行后，无论账号有没有角色、有多少个角色，都能返回预期结果：

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

内容的提问来源于stack exchange，提问作者Mario Ishac