PostgreSQL JSONB update array elements query

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

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<>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 @> '{"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.