Postgresql - SQL to Update a JSONB column type

Question

I have a table which has a column named "data" of type JSONB. I am trying to come up with a SQL statement to accomoodate the recent model changes. (postgres 9.4)

"isIndiaGSTComposition" in old model needs to be changed as "auxiliaryProperties" data structure (below) in the new model.

 {  
   "test":{  
      "isIndiaGSTComposition":true        (boolean)
   }
}

New model :

{  
   "test":{  
      "AuxiliaryProperties":[
                   {
                    "id":"indiaGSTComposition",
                    "booleanValue":true
                    }
                  ]        
   }
}

Please note that, if isIndiaGSTComposition is null, it should be auxiliaryProperties : null. But if it has true or false, it needs to be in the above data structure format(like in "new model" example above).

Any help is appreciated. Thanks in advance !

Evgeny Nozdrev Evgeny Nozdrev · Accepted Answer · 2018-09-13T12:38:05

You can write conversion function and use

UPDATE table
SET field = conv_func(field);

All JSON operations are here (choose your PostgreSQL version).

Use json->'test' operator to get subobject of 'test':

{  
      "isIndiaGSTComposition":true        (boolean)
}

If you have multiple fields, you can iterate over them with

FOR _rec IN
SELECT t."key", t."value"
FROM jsonb_each(subobject) t
LOOP
...
END LOOP;

Declare new JSONB variable for new "AuxiliaryProperties" subobject and init it with '{}'::jsonb

Use jsonb_insert() to populate "AuxiliaryProperties" with "id" and "booleanValue".

Then use jsonb_insert() again to insert new subobject with key "AuxiliaryProperties" and use #- operator to delete old subobject.

Postgresql - SQL to Update a JSONB column type

1 Answers