update jsonb column from values in another table

Question

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}]

Answer 1

You can try this :

UPDATE
    warranty_request AS w
SET
    device_components = a.device_components
FROM
   ( SELECT d.warranty_request_uuid
          , jsonb_agg (jsonb_build_object
                             ( 'serial_number', d.serial_number
                             , 'type', d.device_type
                             , 'description', d.description
                             )
                      ) AS device_components
       FROM device_component AS d
      GROUP BY d.warranty_request_uuid
   ) AS a
WHERE w.uuid = a.warranty_request_uuid

see the demo result in dbfiddle.