From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Deven Phillips <deven(dot)phillips(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to insert/update a bunch of JSOB values? |
Date: | 2016-07-11 13:11:25 |
Message-ID: | CAKFQuwa7XJiPZSV1uLnYRNOQ4J1n9Y+AOzvxWOfppvSnEVhROw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Jul 11, 2016 at 8:45 AM, Deven Phillips <deven(dot)phillips(at)gmail(dot)com>
wrote:
> 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!!!
>
>
This is a limitation of SQL-based processing.
You will probably need to use pl/pgsql and a loop here. Within the loop
you execute jsonb_set(...) once for each path on temp_table.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2016-07-11 14:02:15 | Re: 9.6 beta2 win-x64 download links still point to beta1 |
Previous Message | Deven Phillips | 2016-07-11 12:45:31 | How to insert/update a bunch of JSOB values? |