Script to Fix pieces and po_line records which don't have tenantId populated

When to apppy?

After migrating from non-ecs data to ecs with central ordering enabled, there will be records that don’t have tenantId populated

mod_orders_storage.po_line (no tenantId in locations)

{
  "id": "556abc25-ebbf-3fb2-b478-1bfaff0af4dc", 
  "locations": [
    {
      "quantity": 2,
      "locationId": "758258bc-ecc1-41b8-abca-f7b610822ffd",
      "quantityPhysical": 2,
    }
  ],
  "publisher": "American Bar Association",
}

mod_orders_storage.pieces (no receivingTenantId)

{
  "id": "1cb31605-45da-40ec-ba0e-f1e1bb1b1528",
  "format": "Physical",
  "poLineId": "63a7493d-2337-4f3d-b375-2672a57dfa90",
  "statusUpdatedDate": "2025-03-20T09:12:19.339+00:00"
}

Steps to fix for po_line table

First Clarify which records to fix for mod_orders_storage.po_line

SELECT 
    id,
    jsonb->'poLineNumber' AS po_line_number,
    jsonb->'locations' AS locations,
    (
        SELECT bool_and(elem ? 'tenantId')
        FROM jsonb_array_elements(jsonb->'locations') AS elem
    ) AS all_locations_have_tenant_id,
	jsonb
FROM diku_mod_orders_storage.po_line
WHERE -- WHERE query will find at least one location exists and tenantId is missing 
    jsonb->'locations' @> '[{}]'  -- has at least one location
    AND NOT (
        SELECT bool_and(elem ? 'tenantId')
        FROM jsonb_array_elements(jsonb->'locations') AS elem
    );  -- at least one location is missing tenantId

Next run query , it should update all records as we found previously
Imporant: Change <tenant> to desired tenant in TWO places in script

UPDATE <tenant>_mod_orders_storage.po_line
SET jsonb = jsonb_set(
  jsonb,
  '{locations}',
  (
    SELECT jsonb_agg(
      CASE
        WHEN location ? 'tenantId' THEN location
        ELSE jsonb_set(location, '{tenantId}', '"<tenant>"'::jsonb)  -- value '<tenant>' need to be changes
      END
    )
    FROM jsonb_array_elements(jsonb->'locations') AS location
  )
)
WHERE 
    jsonb->'locations' @> '[{}]'
    AND NOT (
        SELECT bool_and(elem ? 'tenantId')
        FROM jsonb_array_elements(jsonb->'locations') AS elem
    );

Finally check again if there are no corrupted records after update

SELECT 
    id,
    jsonb->'poLineNumber' AS po_line_number,
    jsonb->'locations' AS locations,
    (
        SELECT bool_and(elem ? 'tenantId')
        FROM jsonb_array_elements(jsonb->'locations') AS elem
    ) AS all_locations_have_tenant_id,
	jsonb
FROM <tenant>_mod_orders_storage.po_line
WHERE -- WHERE query will find at least one location exists and tenantId is missing 
    jsonb->'locations' @> '[{}]' 
    AND NOT (
        SELECT bool_and(elem ? 'tenantId')
        FROM jsonb_array_elements(jsonb->'locations') AS elem
    );

Steps to fix for pieces table

First Clarify which records to fix for mod_orders_storage.pieces

SELECT 
    id,
    jsonb->'id' AS json_id,
    jsonb->'poLineId' AS po_line_id,
    jsonb -> 'receivingTenantId' as receivingTenantId,
	jsonb
FROM <tenant>_mod_orders_storage.pieces
WHERE 
    NOT (jsonb ? 'receivingTenantId');

Next run query , it should update all records as we found previously
Imporant: Change <tenant> to desired tenant in TWO places in script

UPDATE <tenant>_mod_orders_storage.pieces
SET jsonb = jsonb_set(
    jsonb,
    '{receivingTenantId}',
    '"<tenant>"'::jsonb -- value "<tenant>" needs to be changes
)
WHERE NOT (jsonb ? 'receivingTenantId');

Finally check again if there are no corrupted records after update

SELECT 
    id,
    jsonb->'id' AS json_id,
    jsonb->'poLineId' AS po_line_id,
    jsonb -> 'receivingTenantId' as receivingTenantId,
	jsonb
FROM <tenant>_mod_orders_storage.pieces
WHERE 
    NOT (jsonb ? 'receivingTenantId');