PostgreSQL JSONB update array elements query

Question

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.

Answer 1

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.

• Vagelis Savvas: 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?
• Erwin Brandstetter: @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