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=jsonb_set(d.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