I create a migration to to convert a jsonb column into a one-to-many table.
-- upgrade
insert into device_component (
warranty_request_uuid,
serial_number,
component_type,
description
)
select
warranty_request.uuid,
value->>'serial_number',
value->>'type',
value->>'description'
from
warranty_request,
jsonb_array_elements(warranty_request.device_components)
I need to provide a corresponding downgrade statement. To revert the migration, I am trying something like the below.
-- downgrade
update
warranty_request
set
device_components = jsonb_set(
device_components,
'{}',
jsonb_build_object(
'serial_number', device_component.serial_number,
'type', device_component.component_type,
'description', device_component.description
)
)
from
device_component
where
warranty_request.uuid = device_component.warranty_request_uuid
The problem is that the device_components column contains only null values after downgrading. So nothing is inserted.
How, should the downgrade statement look like to make this work?
I want to upgrade and downgrade between these 2 formats.
Upgrade:
# warranty_request
uuid
-----
abc
# device_compoent
uuid | warranty_request_uuid | serial_number | device_type | description
-----|-----------------------|---------------|-------------|------------
efg | abc | 1 | foo | bar
hij | abc | 2 | foo | bar
Downgrade:
# warranty_request
uuid | device_components
------|----------------------------------------------------------------------------------------------------------------------
abc | [{"serial_number": 1, "type": "foo", "description": bar}, {"serial_number": 2, "type": "foo", "description": bar}]
You can try this :
see the demo result in dbfiddle.