:
Last modified: Feb 9, 2024

Sample queries

Sample queries for PostgreSQL

Update json property

  • Generic need: update json property (not top level)
  • Example case: undelete soft deleted instance by updating the IsSoftDeleted boolean property
update storage.instances set instance = jsonb_set(instance, '{Status, IsSoftDeleted}', 'false')
	where org = 'serviceowner' and instance -> 'InstanceOwner' ->> 'PersonNumber' in ('ssn1', 'ssn2')

Delete json property

  • Generic need: delete json field (not top level)
  • Example case: undelete soft deleted instance by removing the SoftDeleted timestamp property
update storage.instances set instance = instance::jsonb #- '{Status, SoftDeleted}'
	where org = 'serviceowner' and instance -> 'InstanceOwner' ->> 'PersonNumber' in ('ssn1', 'ssn2')