Search Postgresql Archives

Re: How to insert/update a bunch of JSOB values?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Jul 11, 2016 at 8:45 AM, Deven Phillips <deven.phillips@xxxxxxxxx> 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="" 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux