How to Update Specific Entities in a Postgres JSON Array-CodePudding

I have a Postgres 11.12 database with a table called workflows_emails with a json column called to. The to column has the following format:

[
   {
      "type":"employee",
      "assignment":{
         "..."
      }
   },
   {
      "type":"external",
      "email":"[email protected]"
   }
]

I would like to update the json array to obfuscate the email field in all entities where type field is external. The resulting array would look like:

[
   {
      "type":"employee",
      "assignment":{
         "..."
      }
   },
   {
      "type":"external",
      "email":"[email protected]"
   }
]

Is there a way to do this with a Postgres query?

CodePudding user response：

Assuming your workflows_emails table has an id primary key column, you can update the appropriate JSON objects in the array by running the following update:

update workflows_emails set "to" = new_to.new_to
from (
    select id,
  json_agg(
    case
      when element ->> 'type' = 'external' then 
             jsonb_set(element, '{email}', to_jsonb(element ->> 'email' || '.SBX'))
      else element
    end
  ) as new_to
from workflows_emails,
     jsonb_array_elements("to") as element
group by id
) new_to
where new_to.id = workflows_emails.id

The subquery in new_to iterates over the elements in the JSON array and transforms the objects where type is external. The iteration is done by unpacking the JSON array with jsonb_array_elements so that we can evaluate a condition and transformation on the individual elements. After the transformation of the appropriate elements, we recreate the JSON array using json_agg. For the transformation, we use jsonb_set, which sets the email field of the existing JSON object to a new value.

The result of the subquery is then used to update the original table's "to" column.

You can see the results on dbfiddle.