postgres query to update json field

Question

In PostgreSQL databse, I have a table called hotel, which has a column called hotel_info. This hotel_info column stores json object like below

{
"hotel":{
    "room_details":{
        "customer_detail":{
            "first_name" : "aaa"
        }
    }
}}

I need an update query to update value for the 'first_name' field in 'hotel_info' column. please help me to build query for this.

Thanks in advance!

There is no JSON type in 9.1. Upgrade to Postgres 11, especially that the 9.1 version is no longer supported. — klin
– klin, Commented Mar 9, 2019 at 2:09

rocksteady · Accepted Answer · 2019-03-09 20:56:38Z

1

Something like this should work (assuming you are on a version of Postgres that does indeed support JSON operators). Note that the 'set' function here only works on JSONB, so you may need to cast the column before/after:

SELECT JSONB_SET(
    hotel_info #> '{hotel, room_details, customer_detail}'
    , '{first_name}'
    , '"bbb"'
    , create_missing FALSE) AS hotel_info_modified
FROM table

Here I'm changing the name to 'bbb' for the sake of an example. Check that this is indeed the intended behaviour via a SELECT and then you can change to an UPDATE where needed.

answered Mar 9, 2019 at 20:56

rocksteady

2,3821 gold badge16 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

postgres query to update json field

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related