These scripts allow for:
1) identifying Source Record Storage records that do not have an instance ID in the external_id column but have it in 999 ff $i subfield.
2) correcting that problem, by retrieving that value from the 999 ff $i value of the SRS MARC Bibliographic record and populate it into the instanceID field.
These scripts are relevant for Lotus version of mod-source-record-storage module schema
Script 1. Retrieve IDs of source records that contain instance ID in 999 ff $i subfield but do not have it in "external_id" column.
SELECT records_lb.id AS record_id, records_lb.external_id, record_instance_ids.instance_id
FROM {tenantId}_mod_source_record_storage.records_lb
JOIN (
SELECT id, ids_subfields ->> 'i' AS instance_id
FROM (
SELECT id, fields
FROM {tenantId}_mod_source_record_storage.marc_records_lb, jsonb_array_elements(content -> 'fields') as fields
WHERE fields ? '999'
) AS ids_fields, jsonb_array_elements(fields -> '999' -> 'subfields') AS ids_subfields
WHERE ids_subfields ? 'i'
) AS record_instance_ids ON records_lb.id = record_instance_ids.id
WHERE external_id IS NULL;
Script 2. Populate instance ID from 999 ff $i subfield of parsed content into the "external_id" column.
UPDATE {tenantId}_mod_source_record_storage.records_lb as rec
SET external_id = (record_instance_ids.instance_id)::uuid
FROM (
SELECT id, ids_subfields ->> 'i' AS instance_id
FROM (
SELECT id, fields
FROM {tenantId}_mod_source_record_storage.marc_records_lb, jsonb_array_elements(content -> 'fields') as fields
WHERE fields ? '999'
) AS ids_fields, jsonb_array_elements(fields -> '999' -> 'subfields') AS ids_subfields
WHERE ids_subfields ? 'i'
) AS record_instance_ids
WHERE rec.id = record_instance_ids.id AND external_id IS NULL;