PostgreSQL JSONB update array elements query Given a table in PostgreSQL 10:

CREATE TABLE forms (id serial, 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"]}]');
INSERT INTO forms (id, form) VALUES (
  2, '"id":"a uuid_v1 here",
      "widgets": [ {"id":"1", "permissions": ["user1", "user2"] },
                   {"id":"2", "permissions": ["user3", "user4"]}]');


I need to remove a user, say "user1" from all assigned permissions in any widget. I also need to add a user, say "user5" to all assigned permissions in any widget.
This is not a trivial task.

Assuming the two nested JSON arrays reliably exist in every row, this works:

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 -&gt;'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-&gt;'permissions') p(permission)
            WHERE  p.permission &lt;&gt; 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;


But it is complex and expensive. I'd rather not use it. Instead, I would aim to normalize the DB schema if at all possible.

db&lt;>fiddle here - with intermediate steps to help understanding.

There is too much to explain here ... 

Most importantly:


It works even with empty permissions arrays because we add at least one user. Else we would get NULL instead of an empty array, and might need to do more.
The manual about the concatenation operator ||:


  Note
  
  The || operator concatenates the elements at the top level of each
  of its operands. It does not operate recursively. For example, if both
  operands are objects with a common key field name, the value of the
  field in the result will just be the value from the right hand
  operand.

Avoid updating rows where nothing changes with the last line added to the WHERE clause. See:


How do I (or can I) SELECT DISTINCT on multiple columns?

While you only delete a user and most rows don't have that user to begin with, we could make it much faster with a condition:

WHERE  f.form @&gt; '{"widgets": [{"permissions":["user1"]}]}'


Checks whether any permissions array has a "user1" element, and can use a matching index. If the table is big you'll want a matching index. See:


Find rows containing a key in a JSONB array of records

To identify rows where not all permissions arrays have "user5", the check is not as simple, and index support even less. Though still possible.