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!

Answer 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.