Using and parameterising json updates in postgresql

I had a problem where two jobs were updating the same field like this:

const updatedWorkflowState[workflowId] = {...oldState[workflowId], finish_time: "foo"}
 
await db.runQuery("workflow/update_clausepart_workflow_state", [
	this.data.clausepart.id,
	this.data.clausepart.workflow_state,
]);

The query was like

update clausepart
set workflow_state = $2
where id = $1
returning clausepart.workflow_state

And the problem was that job 1 didn’t know about the updated state from job 2 and vice versa, so one of them would overwrite the workflow state which meant only one of the keys in the json in the database were updated.

I thought maybe we could lock the table, get the workflow_state, then update the jobs workflow_state, then write it back. But this seemed overkill. Introduce jsonb_set! The function takes a object, a path of the value to set, and the new value, like so

set workflow_state = jsonb_set(workflow_state, '{"6"}', '{"ahoy": "there"}')

so it would transofrm the following workflow state

{"6": "foo"}

into

{"6": {"ahoy": "there!"}}

Parameterisation

The path