2

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.

2

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:

  • 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:

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

  • Vielen Dank for the complete answer Erwin. Is it safe to assume that the recommended usage of JSON with PostgreSQL should be confined in shallow JSON structures that avoid nested arrays and deep object containments? As your answer suggests the first can be a real issue, especially in light of performance but the second not that much of a problem? If I may ask, what would be your opinion on that? – Vagelis Savvas Dec 19 '18 at 7:41
  • @VagelisSavvas: If you have many updates on data, deeply nested document types are probably not the best choice. I wrote more here, some time ago: stackoverflow.com/a/26704636/939860 – Erwin Brandstetter Dec 19 '18 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.