How to Update Specific Entities in a Postgres JSON Array

Question

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?

Does this answer your question? Search and replace key's value in nested JSON — user3738870
– user3738870, Commented Nov 16, 2022 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
– Toaster, Commented Nov 16, 2022 at 11:44

user3738870 · Accepted Answer · 2022-11-16 12:28:30Z

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.

answered Nov 16, 2022 at 12:28

user3738870

1,6502 gold badges16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Toaster Over a year ago

Wow, amazing. Thank you, I learned a lot from reading your solution. I would upvote more if it were possible :-)

Collectives™ on Stack Overflow

How to Update Specific Entities in a Postgres JSON Array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related