Merging json objects when grouping in postgres

Question

I'm trying to build a user permissions structure in Postgres 11.5.

The basic idea is a user can belong to multiple groups and a group can have permissions for multiple applications. The user's permissions (if any) will override any permissions set at group level.

Permissions at user and usergroup level will be stored as json objects which I want to merge together with user permissions overwriting usergroup permissions if there is any overlap.

Example: Brendan, James and n other users are in the exact same usergroups, but James should not be able to access app2.

Set up:

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}}');

Query:

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;

Actual query results:

+---------+----------------------------------------------------------------------------------------------------------+
|   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}}] |
+---------+----------------------------------------------------------------------------------------------------------+

This kind of works, but I would want the object to be flattened and keys merged.

Desired result:

+---------+---------------------------------------------------------------------------+
|   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

Other info: The actual permissions object set at usergroup level for each app will be more complex than in the example, e.g featureA is enabled, featureB is disabled etc and in fact more applications will be added in the future so I don't want to hardcode any references to specific apps or features if possible.

I suppose technically, if easier, the desired output would be the permissions object for just a single user so could replace the GROUP BY uid with a WHERE uid = 'x'

Question/Problem: How can I modify the query to produce a flattened/merged permissions json object?

edit: fixed json

Answer 1

Your indicated desired output is not syntactically valid JSON. If I make a guess as to what you actually want, you can get it with jsonb_object_agg rather than jsonb_agg. You have to first unnest the values you select so that you can re-aggregate them together, which is done here by a lateral join against 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;

Yields:

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

Your select of "group" as type is confusing as it is never used.