How to update multiple occurrence a specific value of a object present in array of object within Postgres JSON Field

Question

Here is my JSON field where has multiple users with the same name. I want to update all users whose name is Devang to Dev

JSON

{
    "user": [
        {
            "user_name": "Devang",
            "user_weight": 0.7676846955248864
        },
        {
            "user_name": "Meet",
            "user_weight": 0.07447325861051013
        },
        {
            "user_name": "Devang",
            "user_weight": 0.056163873153859706
        }
    ],
    "address": [
        {
            "address_name": "India"
        }
    ]
}

After Update The JSON would be

{
    "user": [
        {
            "user_name": "Dev",
            "user_weight": 0.7676846955248864
        },
        {
            "user_name": "Meet",
            "user_weight": 0.07447325861051013
        },
        {
            "user_name": "Dev",
            "user_weight": 0.056163873153859706
        }
    ],
    "address": [
        {
            "address_name": "India"
        }
    ]
}

Here I have tried this query but update only the first occurrence due to subquery.

with cte as (
  select  id,  ('{user,'||index-1||',user_name}')::text[] as json_path
  from user_table, jsonb_array_elements(json_field->'user') 
  with ordinality arr(vals,index) where arr.vals->>'user_name' ='Devang'
  )

 update user_table 
set json_field = jsonb_set(json_field,cte.json_path,'"Dev"',false) 
 from cte where user_table.id=cte.id;

Please also look at this DEMO

Any answer will be appreciated

This would be so much easier with a properly normalized data model. — user330315
– user330315, Commented Aug 4, 2021 at 6:05

Akina · Accepted Answer · 2021-08-04 05:39:41Z

1

You may use string function REPLACE:

UPDATE user_table
SET json_field = REPLACE(json_field :: TEXT, '"user_name": "Devang"', '"user_name": "Dev"') :: JSONB;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=fa36275977f85a1233bcbec150ada266

answered Aug 4, 2021 at 5:39

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

2 Comments

l.b.vasoya Over a year ago

what if I want to remove those object whose user_name is devang

Akina Over a year ago

Abandon JSONB altogether and use normalized structure with a couple of tables joined by foreign keys. Then the operations you need will be very simple.

Collectives™ on Stack Overflow

How to update multiple occurrence a specific value of a object present in array of object within Postgres JSON Field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related