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

Your desired output is not a valid syntax for JSON.

jjanes
– jjanes

2020-03-06 17:19:21 +00:00
Commented Mar 6, 2020 at 17:19 — jjanes
– jjanes, Commented Mar 6, 2020 at 17:19

jjanes · Accepted Answer · 2020-03-06 17:35:50Z

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.

answered Mar 6, 2020 at 17:35

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BStone Over a year ago

Thanks, that does the job nicely! The type column was just for testing the subquery before the aggregation so can be removed now.

Collectives™ on Stack Overflow

Merging json objects when grouping in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related