Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Postgres Jsonb map of objects to map of simple values

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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!

>Solution :

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));
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading