BF-318 - script to remove identifiers with invalid identifier type from instance records

The bugfest data contains instance records that have identifiers with invalid identifier types.

In Kiwi, Lotus and Morning Glory bugfests those invalid identifiers have been replaced with temporary values.

To get instances ids with incorrect identifiers execute:

instances ids with incorrect idntifiers

SELECT DISTINCT id
  FROM   {tenant}_mod_inventory_storage.instance t, jsonb_array_elements(t.jsonb->'identifiers') elem
  WHERE  elem->>'identifierTypeId' 
  IN ('59378436-9645-4add-b05f-6afc69bdc8d0', '9024e225-7a68-4f2c-bcf1-81013fb8a6f0', 'fe19bae4-da28-472b-be90-d442e2428ead')

According BF-318 it is necessary to :

Identify the instance records with invalid identifiers type
Remove the reference to the invalid identifiers from the instance record

The script to do this actions is :

script to remove identifiers with invalid identifier type

WITH instances_without_wrong_identifiers AS (
  SELECT id, array_to_json(array_agg(elem))::jsonb AS identifiers
  FROM   {tenant}_mod_inventory_storage.instance t, jsonb_array_elements(t.jsonb->'identifiers') elem
  WHERE  elem->>'identifierTypeId' 
  NOT IN ('59378436-9645-4add-b05f-6afc69bdc8d0', '9024e225-7a68-4f2c-bcf1-81013fb8a6f0', 'fe19bae4-da28-472b-be90-d442e2428ead')
  GROUP BY 1
), instances_with_wrong_identifiers_to_empty AS (
  SELECT DISTINCT id, '[]'::jsonb AS identifiers
  FROM   {tenant}_mod_inventory_storage.instance t, jsonb_array_elements(t.jsonb->'identifiers') elem
  WHERE  elem->>'identifierTypeId' 
  IN ('59378436-9645-4add-b05f-6afc69bdc8d0', '9024e225-7a68-4f2c-bcf1-81013fb8a6f0', 'fe19bae4-da28-472b-be90-d442e2428ead')
)
UPDATE {tenant}_mod_inventory_storage.instance i
SET   jsonb = jsonb_set(jsonb, '{identifiers}', i2.identifiers)
FROM  (
  SELECT id, identifiers FROM instances_without_wrong_identifiers
  UNION 
  SELECT id, identifiers FROM instances_with_wrong_identifiers_to_empty WHERE id NOT IN (SELECT id FROM instances_without_wrong_identifiers)
)  i2
WHERE  i2.id = i.id
AND    jsonb_array_length(i2.identifiers) < jsonb_array_length(i.jsonb->'identifiers');

Where {tenant} - the owner (tenant) for which the script should be run. Used ids in the script are ids of invalid identifier types.