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

Question

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

Does this answer your question? [How do I modify fields inside the new PostgreSQL JSON datatype?](https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) — Kadet, Jun 20 '22 at 12:01
What if your `houses` array contains multiple elements? Should this be done for all of them? Only the first? Or the last? Or the 42nd? — , Jun 20 '22 at 13:06

score 0 · Answer 1 · answered Jun 20 '22 at 15:03

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.

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

1 Answers1