Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE forms (id serial primary key, form jsonb);

INSERT INTO forms (id, form) VALUES
  (1, '{"id":"a uuid_v1 here",
        "widgets": [{"id":"1", "permissions": ["user1", "user2"]},
                    {"id":"2", "permissions": ["user1", "user3"]}]}')
, (2, '{"id":"a uuid_v1 here",
        "widgets": [{"id":"1", "permissions": ["user1", "user2"]},
                    {"id":"2", "permissions": ["user3", "user4"]}]}')
, (3, '{"id":"a uuid_v1 here",
        "widgets": [{"id":"1", "permissions": ["user1", "user2"]},
                    {"id":"2", "permissions": ["user5", "user2"]}]}')
, (4, '{"id":"a uuid_v1 here",
        "widgets": [{"id":"1", "permissions": ["user5", "user6"]},
                    {"id":"2", "permissions": ["user5", "user7"]}]}')  -- nothing to do here
, (5, '{"id":"a uuid_v1 here",
        "widgets": [{"id":"1", "permissions": []},
                    {"id":"2", "permissions": []}]}')                  -- empty arrays
;
5 rows affected

-- Step 1

SELECT f.id, w.widget, w.permissions, w.widget || w.permissions AS widget1
FROM   forms f
CROSS  JOIN LATERAL (
   SELECT *
   FROM   jsonb_array_elements(f.form ->'widgets') w(widget)
   LEFT   JOIN LATERAL (
      SELECT jsonb_build_object('permissions', jsonb_agg(p1.permission)) AS permissions
      FROM  (
         SELECT *
         FROM   jsonb_array_elements(w.widget->'permissions') p(permission)
         WHERE  p.permission <> jsonb '"user1"'       -- delete user1
         UNION
         SELECT jsonb '"user5"'                       -- add user5
         ) p1
      ) p ON true
   ) w;


id
widget
permissions
widget1


1
{"id": "1", "permissions": ["user1", "user2"]}
{"permissions": ["user2", "user5"]}
{"id": "1", "permissions": ["user2", "user5"]}

1
{"id": "2", "permissions": ["user1", "user3"]}
{"permissions": ["user3", "user5"]}
{"id": "2", "permissions": ["user3", "user5"]}

2
{"id": "1", "permissions": ["user1", "user2"]}
{"permissions": ["user2", "user5"]}
{"id": "1", "permissions": ["user2", "user5"]}

2
{"id": "2", "permissions": ["user3", "user4"]}
{"permissions": ["user4", "user3", "user5"]}
{"id": "2", "permissions": ["user4", "user3", "user5"]}

3
{"id": "1", "permissions": ["user1", "user2"]}
{"permissions": ["user2", "user5"]}
{"id": "1", "permissions": ["user2", "user5"]}

3
{"id": "2", "permissions": ["user5", "user2"]}
{"permissions": ["user2", "user5"]}
{"id": "2", "permissions": ["user2", "user5"]}

4
{"id": "1", "permissions": ["user5", "user6"]}
{"permissions": ["user6", "user5"]}
{"id": "1", "permissions": ["user6", "user5"]}

4
{"id": "2", "permissions": ["user5", "user7"]}
{"permissions": ["user7", "user5"]}
{"id": "2", "permissions": ["user7", "user5"]}

5
{"id": "1", "permissions": []}
{"permissions": ["user5"]}
{"id": "1", "permissions": ["user5"]}

5
{"id": "2", "permissions": []}
{"permissions": ["user5"]}
{"id": "2", "permissions": ["user5"]}


-- Step 2

   SELECT f.id, f.form || jsonb_build_object('widgets', jsonb_agg(w.widget1)) AS widgets
   FROM   forms f
   CROSS  JOIN LATERAL (
      SELECT w.widget || p.permissions AS widget1
      FROM   jsonb_array_elements(f.form ->'widgets') w(widget)
      LEFT   JOIN LATERAL (
         SELECT jsonb_build_object('permissions', jsonb_agg(p1.permission)) AS permissions
         FROM  (
            SELECT *
            FROM   jsonb_array_elements(w.widget->'permissions') p(permission)
            WHERE  p.permission <> jsonb '"user1"'       -- delete user1
            UNION
            SELECT jsonb '"user5"'                       -- add user5
            ) p1
         ) p ON true
      ) w
   GROUP  BY 1;


id
widgets


1
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user3", "user5"]}]}

2
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user4", "user3", "user5"]}]}

3
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user2", "user5"]}]}

4
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user6", "user5"]}, {"id": "2", "permissions": ["user7", "user5"]}]}

5
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user5"]}, {"id": "2", "permissions": ["user5"]}]}


-- UPDATE

UPDATE forms f
SET    form = u.form1
FROM  (
   SELECT f.id, f.form || jsonb_build_object('widgets', jsonb_agg(w.widget1)) AS form1
   FROM   forms f
   CROSS  JOIN LATERAL (
      SELECT w.widget || p.permissions AS widget1
      FROM   jsonb_array_elements(f.form ->'widgets') w(widget)
      LEFT   JOIN LATERAL (
         SELECT jsonb_build_object('permissions', jsonb_agg(p1.permission)) AS permissions
         FROM  (
            SELECT *
            FROM   jsonb_array_elements(w.widget->'permissions') p(permission)
            WHERE  p.permission <> jsonb '"user1"'       -- delete user1
            UNION
            SELECT jsonb '"user5"'                       -- add user5
            ) p1
         ) p ON true
      ) w
   GROUP  BY 1
   ) u
WHERE  u.id = f.id
AND    u.form1 IS DISTINCT FROM f.form
RETURNING f.*;


id
form


1
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user3", "user5"]}]}

2
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user4", "user3", "user5"]}]}

3
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user2", "user5"]}]}

4
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user6", "user5"]}, {"id": "2", "permissions": ["user7", "user5"]}]}

5
{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user5"]}, {"id": "2", "permissions": ["user5"]}]}

id	widget	permissions	widget1
1	{"id": "1", "permissions": ["user1", "user2"]}	{"permissions": ["user2", "user5"]}	{"id": "1", "permissions": ["user2", "user5"]}
1	{"id": "2", "permissions": ["user1", "user3"]}	{"permissions": ["user3", "user5"]}	{"id": "2", "permissions": ["user3", "user5"]}
2	{"id": "1", "permissions": ["user1", "user2"]}	{"permissions": ["user2", "user5"]}	{"id": "1", "permissions": ["user2", "user5"]}
2	{"id": "2", "permissions": ["user3", "user4"]}	{"permissions": ["user4", "user3", "user5"]}	{"id": "2", "permissions": ["user4", "user3", "user5"]}
3	{"id": "1", "permissions": ["user1", "user2"]}	{"permissions": ["user2", "user5"]}	{"id": "1", "permissions": ["user2", "user5"]}
3	{"id": "2", "permissions": ["user5", "user2"]}	{"permissions": ["user2", "user5"]}	{"id": "2", "permissions": ["user2", "user5"]}
4	{"id": "1", "permissions": ["user5", "user6"]}	{"permissions": ["user6", "user5"]}	{"id": "1", "permissions": ["user6", "user5"]}
4	{"id": "2", "permissions": ["user5", "user7"]}	{"permissions": ["user7", "user5"]}	{"id": "2", "permissions": ["user7", "user5"]}
5	{"id": "1", "permissions": []}	{"permissions": ["user5"]}	{"id": "1", "permissions": ["user5"]}
5	{"id": "2", "permissions": []}	{"permissions": ["user5"]}	{"id": "2", "permissions": ["user5"]}

id	widgets
1	{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user3", "user5"]}]}
2	{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user4", "user3", "user5"]}]}
3	{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user2", "user5"]}, {"id": "2", "permissions": ["user2", "user5"]}]}
4	{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user6", "user5"]}, {"id": "2", "permissions": ["user7", "user5"]}]}
5	{"id": "a uuid_v1 here", "widgets": [{"id": "1", "permissions": ["user5"]}, {"id": "2", "permissions": ["user5"]}]}