Update nested tag in JSON field in Postgresql

Question

Ihave the following JSON field:

{
    "Id": "64848e27-c25d-4f15-99db-b476d868b575",
    "Associations_": [
        "RatingBlockPinDatum"
    ],
    "RatingScenarioId": "00572f95-9b81-4f7e-a359-3df06b093d4d",
    "RatingBlockPinDatum": [
        {
            "Name": "mappedmean",
            "PinId": "I.Assessment",
            "Value": "24.388",
            "BlockId": "Score"
        },
        {
            "Name": "realmean",
            "PinId": "I.Assessment",
            "Value": "44.502",
            "BlockId": "Score"
        }]}

I want to update the Value from 24.388 to a new value in the nested array "RatingBlockPinDatum" where Name = "mappedmean".

Any help would be appreciated. I have already tried this but couldn't adapt it to work properly:

[Update nested key with postgres json field in Rails

Is the data type of your field `json` or `jsonb`. If not `jsonb` can you convert it to that, or does it need to work for `json`? — trincot, Jan 28 '18 at 15:34

score 0 · Accepted Answer · answered Jan 28 '18 at 15:56

You could first get one result per element in the RatingBlockPinDatum JSON array (using jsonb_array_length and generate_series) and then filter that result for where the Name key has the value "mappedmean". Then you have the records that need updating. The update itself can be done with jsonb_set:

with cte as (
    select id, generate_series(0, jsonb_array_length(info->'RatingBlockPinDatum')-1) i
    from   mytable
)
update mytable
   set info = jsonb_set(mytable.info, 
                 array['RatingBlockPinDatum', cte.i::varchar, 'Value'], 
                 '"99.999"'::jsonb)
from   cte
where  mytable.info->'RatingBlockPinDatum'->cte.i->>'Name' = 'mappedmean'
and    cte.id = mytable.id;

Replace "99.999" with whatever value you want to store in that Value property.

See it run on rextester.com

Update nested tag in JSON field in Postgresql

1 Answers1