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

Here is my JSON field value. which is stored in the PostgreSQL table. I want to search and update the specific user_name in the user key

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

whose name is Devang to Dev using Django JSONField for example

 "user": [
        {
            "user_name": "Dev",
            "user_weight": 0.7676846955248864
        },
....

I have tried the RAWQuery for the find. This is the query.

select json_field->user from user_table where json_field @> '{"user": [{"user_name": "Devang"}]}';

It will return like this

        {
            "user_name": "Devang",
            "user_weight": 0.7676846955248864
        },
        {
            "user_name": "Meet",
            "user_weight": 0.07447325861051013
        },
        {
            "user_name": "L.b.vasoya",
            "user_weight": 0.056163873153859706
        }
]

I have also tried JSON_SET to update the user_name but JSON_SET only accepts. It will update the upper level, not a nesting level

Answer

Using JSONB_SETis a bit tricky in case of nested element updation. If you want to set the value of nested element first you need to get the exact path of element to be updated. Please try below query:

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' --put the value to be replaced
  )
 update user_table 
set json_field = jsonb_set(json_field,cte.json_path,'"Dev"',false) --put the new value in '""' here
 from cte where user_table.id=cte.id;
  

DEMO