how to add more key/value item into a json field in psql/postgres

user_19374266 0
Viewed 31

I have a column named SALE in a table named HOUSE which contains below JSON:

{
   "houses":[
      {
         "houseId":"house100",
         "houseLocation":"malvern",
         "attribute":{
            "colour":[
               "white",
               "grey"
            ],
            "openForInspection":{
               "fromTime":"0001",
               "toTime":"2359"
            }
         },
         "priceRange":null
      }
   ]
}

I need to replace "priceRange":null with {"fromAmount": "100","toAmount": "1000"}

{
   "houses":[
      {
         "houseId":"house100",
         "houseLocation":"malvern",
         "attribute":{
            "colour":[
               "white",
               "grey"
            ],
            "openForInspection":{
               "fromTime":"0001",
               "toTime":"2359"
            }
         },
         "priceRange":{
            "fromAmount":"100",
            "toAmount":"1000"
         }
      }
   ]
}

I tried many different queries but was unable to get the desired results

postgresql sql json
1 Answers

You can use jsonb_array_elements:

select jsonb_build_object('houses', 
  (select array_agg(case when (v.value -> 'priceRange')::text = 'null' 
      then v.value::jsonb || '{"priceRange":{"fromAmount": "100","toAmount": "1000"}}'::jsonb 
      else v.value::jsonb end) 
  from json_array_elements(h.sale -> 'houses') v)) 
from house h

See fiddle.

user_73267380
Related