Postgres Jsonb map of objects to map of simple values-CodePudding

I need to migrate jsonb (Flyway migration) column from

{
  "property1": 
  {
    "type": "A",
    "value": "value1"
  },
  "property2": 
  {
    "type": "B",
    "value" "value2"
  }
}

into simplified

{
  "property1": "value1",
  "property2": "value2"
}

So far, I have

update sms_notification n
set param2 = jsonb_object(
        array(select jsonb_object_keys(n.params)),
        'dummyValue'
    )

producing

{
  "property1": "dummyValue",
  "property2": "dummyValue"

but I cant figure out how to extract the values (value1/value2) from the property1/property2 object

Thanks for any help!

CodePudding user response：

you need to unnest all keys using jsonb_each() then aggregate the key/values back into a single object:

update sms_notification
  set param2 = (select jsonb_object_agg(m.key, m.item -> 'value')
                from jsonb_each(param2) as m(key, item));