Delete JSON property in Postgres

Delete JSON property in Postgres
Photo by Jan Antonin Kolar / Unsplash

When migrating JSON data in Postgres, it's often necessary to delete properties within a JSON object. There's a neat operator for it #-.

In this example the property comment was stored as an empty string instead of the property just missing. So we delete the property for all domain messages where it is an empty string:

UPDATE domain_messages
SET payload = payload::jsonb #- '{reason,comment}'
WHERE event_type = 'app.domain.user_management.delete_user.user_was_deleted'
	AND payload -> 'reason' ->> 'comment' = '';