Postgres - Update and restructure attribute of JSONB type colum

Question

I have a table which has a column named "data" of type JSONB. I am trying to create a migration script based on the recent model changes. (postgres 9.4)

"gstNotRegisteredReason","gstRegistered","isIndiaGSTComposition" in old model needs to be changed as "vatExemptionReason","vatExemption","auxiliaryProperties" in the new model.

Old Model:

 {  
   "requestId":531956,
   "requestVersion":5,
   "supplier":{  
      "taxClassificationId":null,
      "supplierScid":null,
      "gstNotRegisteredReason":null,      (boolean)
      "gstRegistered":null,               (boolean)
      "isIndiaGSTComposition":true        (boolean)
   },
   "createTime":"2017-07-17T06:48:52",    
}

New Model:

 {        
   "requestId":531956,
   "requestVersion":5,
   "supplier":{  
      "taxClassificationId":null,
      "supplierScid":null,
      "vatExemptionReason":null,         (boolean)
      "vatExemption":null,               (boolean)
      "AuxiliaryProperties":[
                   {
                    "id":"indiaGSTComposition",
                    "booleanValue":true
                    }
                  ]           
   },      
   "createTime":"2017-07-17T06:48:52",     
}

I came up with this query

update requests set data=regexp_replace(data::text, '"gstRegistered": ', '"vatExemption":', 'g')::jsonb;
update requests set data=regexp_replace(data::text, '"gstNotRegisteredReason": ', '"vatExemptionReason":', 'g')::jsonb;

But i am not sure how to convert isIndiaGSTComposition(boolean) to auxiliaryProperties(Array).

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 !

S-Man S-Man · Accepted Answer · 2018-09-11T10:10:07

Postgres 9.4:

db<>fiddle

With that version you could do this with the regexp_replace function as well:

UPDATE 
    requests
SET data =  
        regexp_replace(
            data::text,
            '"india"[\s]*:[\s]*(true|false|null)',                              -- A
            '"aux":' || CASE  
                            WHEN (data -> 'india')::text = 'null' THEN 'null'   -- B 
                            ELSE '[{"id":"india", "booleanValue":' || (data -> 'india')::text || '}]' -- C
                        END, 
            'g'
        )::jsonb;

A: Finding the whole india key/value pair. With [\s]* it is possible to act with the whitespaces if there are different formats.

B/C: Replace with the string '"aux":something'. "Something" is build by a CASE clause: If india has the string 'null' then give 'null' (B). Otherwise take the json array string with the origin india value (C).

(I was wondering why are you needing an array for the aux value...)

Postgres 9.6+

db<>fiddle

This version comes with a better json support so the problem can be solved without the text casts and regexp clauses - speaking in a more json-like way.

https://www.postgresql.org/docs/9.6/static/functions-json.html

UPDATE 
    requests
SET 
    data = jsonb_insert(                                      -- A
               data - 'india',                                -- B
               '{aux}',                                       -- C
               CASE                                           -- D
                   WHEN data -> 'india' = 'null' THEN 'null'
                   ELSE jsonb_insert(                         -- E
                            '[{"id":"india"}]'::jsonb,        -- F
                            '{0, booleanValue}',              -- G
                            data -> 'india'                   -- H
                   ) 
               END
            );

A: jsonb_insert inserts a new element into a given json element:

B: First parameter of the function is the json to put the new object in. Here I am taking the data object but removing the india element before.

C: Second parameter - the path where to insert the new element: The new json object will be inserted as value of the key "aux". If it does not exist (as in our case) it will be created.

D: Third parameter - the element to be inserted: Creating a new element. The CASE clause logic is similar to the version above. If the india value is 'null' then give 'null'. Otherwise:

E: Creating a new json element and inserting the old india value

F: The new element is the static json array '[{"id":"india"}]'.

G: This is a path: Taking the first element ("0") and then insert the new object into "booleanValue". Create this key if it does not exist.

H: Taking the old india value. The resulting element could be [{"id":"india", "booelanValue: true"}]. This is taken as value for the "aux" element in (C)

Postgres - Update and restructure attribute of JSONB type colum

1 Answers