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
– trincot, Commented Jan 28, 2018 at 15:34

trincot · Accepted Answer · 2018-01-28 15:56:34Z

0

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

answered Jan 28, 2018 at 15:56

trincot

357k38 gold badges282 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update nested tag in JSON field in Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related