0

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":"mailbox@foobar.com"
   }
]

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":"mailbox@foobar.com.SBX"
   }
]

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

Toaster
  • 1,911
  • 2
  • 23
  • 43
  • Does this answer your question? [Search and replace key's value in nested JSON](https://stackoverflow.com/questions/74444265/search-and-replace-keys-value-in-nested-json) – user3738870 Nov 16 '22 at 11:34
  • @user3738870 It's very close. Can you show me how to update the query to replace all matching occurrences in a json array? – Toaster Nov 16 '22 at 11:44
  • Yes, I've added it as an answer – user3738870 Nov 16 '22 at 12:28

1 Answers1

1

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.

user3738870
  • 1,415
  • 2
  • 12
  • 24
  • Wow, amazing. Thank you, I learned a lot from reading your solution. I would upvote more if it were possible :-) – Toaster Nov 16 '22 at 14:54