We need to update a JSONB data structure in our tables to include an 'is_valid' flag. I was able to build a CTE which I use to create a temp table containing the 'is_valid' value, the path where it needs to be set, and the join criteria. Let's say that the temp table looks like:
id TEXT,
time_inserted TIMESTAMPTZ,
path TEXT[],
is_valid BOOLEAN
PRIMARY KEY (id, time_inserted)
Let's say that the table with the data I want to modify has a JSONB structure which needs to be updated in multiple paths:
{
"path1": {
"invalid_data": "here"
},
"path2: {
"valid_data": "here",
},...
}
For each path needing updates, I have a single entry in the temp table... How do I structure the WITH..UPDATE..FROM query to apply all changes from the temp table?
I've tried to use a simple case:
UPDATE target AS d SET data="" n.path, is_valid::JSONB, true)
FROM temp_table n
WHERE d.time_inserted=n.time_inserted AND d.data->>'id'=n.id
But this results in just the first update for a given PK being applied.
Any assistance would be appreciated!!!
Deven Phillips
