Postgres: Query to remove a single JSON key from an array object

Please read this if you are getting confused by the query jargon: Postgres: Manipulate JSON rows (https://app.clickup.com/2496230/docs/2c5q6-69975/2c5q6-292815)

Real-world example

TMS database is only used as an example here. These instructions apply to all databases, so no need to move this under City Devops documentation.

tms_fleet=> SELECT index, uuid, vehicle, created, updated, deleted_state FROM vehicle WHERE vehicle->'org_uuid' = '"6b1de742-525b-4945-8d0a-136d5574583f"' AND uuid = '312ec249-a9a1-4a89-84f1-6dceab01ccce' AND vehicle ? 'car_id';
-[ RECORD 1 ]-+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
index         | 2067
uuid          | 312ec249-a9a1-4a89-84f1-6dceab01ccce
vehicle       | {"type": "taxi", "uuid": "312ec249-a9a1-4a89-84f1-6dceab01ccce", "car_id": "3006", "org_uuid": "6b1de742-525b-4945-8d0a-136d5574583f", "euroclass": 5, "drive_type": 1, "reg_number": "<hidden-by-miro>", "company_uuid": "f80b3161-3ba8-4584-b463-3ef6b6e31fc5", "phone_number": "<hidden-by-miro>", "vehicle_type": 1}
created       | 2022-03-30 19:37:34.469198
updated       | 2022-04-07 07:38:30.510728
deleted_state | 0

Using the query SET vehicle = vehicle - 'car_id' we can delete single JSON keys from an array:

tms_fleet=> BEGIN ;
BEGIN

tms_fleet=*> UPDATE vehicle SET vehicle = vehicle - 'car_id' WHERE vehicle->'org_uuid' = '"6b1de742-525b-4945-8d0a-136d5574583f"' AND uuid = '312ec249-a9a1-4a89-84f1-6dceab01ccce' RETURNING index, uuid, vehicle, created, updated, deleted_state;

-[ RECORD 1 ]-+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
index         | 2067
uuid          | 312ec249-a9a1-4a89-84f1-6dceab01ccce
vehicle       | {"type": "taxi", "uuid": "312ec249-a9a1-4a89-84f1-6dceab01ccce", "org_uuid": "6b1de742-525b-4945-8d0a-136d5574583f", "euroclass": 5, "drive_type": 1, "reg_number": "<hidden-by-miro>", "company_uuid": "f80b3161-3ba8-4584-b463-3ef6b6e31fc5", "phone_number": "<hidden-by-miro>", "vehicle_type": 1}
created       | 2022-03-30 19:37:34.469198
updated       | 2024-12-20 12:46:34.323698
deleted_state | 0

tms_fleet=*> COMMIT ;
COMMIT

You can notice that the JSON key car_id inside of the vehicle row has now disappeared.