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, Mar 09 '19 at 02:09

score 1 · Answer 1 · answered Mar 09 '19 at 20:56

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.

postgres query to update json field

1 Answers1