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

Question

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

Please add some more explanation in your question. Similar to stackoverflow.com/a/64569848/7772483 — Akhilesh Mishra
– Akhilesh Mishra, Commented Jul 30, 2021 at 15:22

Akhilesh Mishra · Accepted Answer · 2021-07-30 15:35:32Z

1

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

answered Jul 30, 2021 at 15:35

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

1 Comment

l.b.vasoya Over a year ago

Thanks for the answer, I have another issue can please check it once. Here dbfiddle.uk/… in the second record has two Devang user_name it will update only once what should I do to change all match user_name

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related