在postgres中分组时合并json对象

Question

我正试图在Postgres11.5中构建一个用户权限结构。

基本思想是一个用户可以属于多个组，一个组可以拥有多个应用程序的权限。用户的权限(如果有(将覆盖在组级别设置的任何权限。

用户和用户组级别的权限将存储为json对象，我希望将其与用户权限合并在一起，如果有任何重叠，则覆盖用户组权限。

示例：Brendan、James和其他n个用户位于完全相同的用户组中，但James应该无法访问app2。

设置：

CREATE TABLE public.users
(
    uid character varying COLLATE pg_catalog."default" NOT NULL,
    ugid character varying[],
    permissions json,
    CONSTRAINT users_pkey PRIMARY KEY (uid)
);
INSERT INTO public.users VALUES
('brendan','{default,gisteam}','{}'),
('james','{default,gisteam}','{"app2":{"enabled":false}}');

CREATE TABLE public.usergroups
(
    ugid character varying COLLATE pg_catalog."default" NOT NULL,
    permissions json,
    CONSTRAINT usergroups_pkey PRIMARY KEY (ugid)
);
INSERT INTO public.usergroups VALUES
('default','{"app1":{"enabled":true}}'),
('gisteam','{"app2":{"enabled":true},"app3":{"enabled":true}}');

查询：

SELECT uid, json_agg(permissions)
FROM (
    SELECT 
        u.uid,
        ug.permissions,
        'group' AS type
    FROM public.users u
    JOIN public.usergroups ug
    ON ug.ugid = ANY(u.ugid)
    UNION ALL
    SELECT 
        uid,
        permissions,
        'user' AS type
    FROM public.users u2
) a
GROUP BY uid;

实际查询结果：

+---------+----------------------------------------------------------------------------------------------------------+
|   uid   |                                            final_permissions                                             |
+---------+----------------------------------------------------------------------------------------------------------+
| brendan | [{"app1":{"enabled":true}},{"app2":{"enabled":true},"app3":{"enabled":true}},{}]                         |
| james   | [{"app1":{"enabled":true}},{"app2":{"enabled":true},"app3":{"enabled":true}},{"app2":{"enabled":false}}] |
+---------+----------------------------------------------------------------------------------------------------------+

这种方法很有效，但我希望对象被展平并合并关键帧。

所需结果：

+---------+---------------------------------------------------------------------------+
|   uid   |                             final_permissions                             |
+---------+---------------------------------------------------------------------------+
| brendan | {"app1":{"enabled":true},"app2":{"enabled":true},"app3":{"enabled":true}} |
| james   | {"app1":{"enabled":true},"app2":{"enabled":false},"app3":{"enabled":true}}|
+---------+---------------------------------------------------------------------------+

DB Fiddle：https://www.db-fiddle.com/f/9kb1v1T82YVxWERxnWLThL/3

其他信息：在用户组级别为每个应用程序设置的实际权限对象将比示例中更复杂，例如功能A被启用，功能B被禁用等，事实上，未来将添加更多的应用程序，因此如果可能的话，我不想硬编码对特定应用程序或功能的任何引用。

我想，从技术上讲，如果更容易的话，所需的输出将是单个用户的权限对象，因此可以用WHERE uid = 'x' 替换GROUP BY uid

问题/问题：如何修改查询以生成扁平/合并的权限json对象？

编辑：修复json

Answer 1

您指示的所需输出在语法上不是有效的JSON。如果我猜测你真正想要什么，你可以用jsonb_object_agg而不是jsonb_agg来获得它。你必须首先取消测试你选择的值，这样你就可以将它们重新聚合在一起，这是通过对json_each的横向连接来完成的：

select uid, jsonb_object_agg(key,value) 
from (
    SELECT 
        u.uid,
        ug.permissions,
        'group' AS type
    FROM public.users u
    JOIN public.usergroups ug
    ON ug.ugid = ANY(u.ugid)
    UNION ALL
    SELECT 
        uid,
        permissions,
        'user' AS type
    FROM public.users u2) a 
CROSS JOIN LATERAL json_each(permissions) 
GROUP BY uid;

收益率：

   uid   |                                  jsonb_object_agg                                  
---------+------------------------------------------------------------------------------------
 brendan | {"app1": {"enabled": true}, "app2": {"enabled": true}, "app3": {"enabled": true}}
 james   | {"app1": {"enabled": true}, "app2": {"enabled": false}, "app3": {"enabled": true}}

您对"group" as type的选择令人困惑，因为它从未使用过。