clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601568 fiddles created (47975 in the last week).

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
 hidden batch(es)


-- 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"]}
 hidden batch(es)


-- 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"]}]}
 hidden batch(es)


-- 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"]}]}
 hidden batch(es)