From: | Deven Phillips <deven(dot)phillips(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | How to insert/update a bunch of JSOB values? |
Date: | 2016-07-11 12:45:31 |
Message-ID: | CAJw+4NBmmeFWVZ2bR2U71sOMpNAegx8Zqd4kcutYr0Dnfeywgw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-07-11 13:11:25 | Re: How to insert/update a bunch of JSOB values? |
Previous Message | hamann.w | 2016-07-11 09:11:32 | Re: Running query without trigger? |