MongoDB query/script for Swimlane to parse all records for an application and automatically remove inaccessible attachments (10.x)
Below is the query to clean up records with corrupted or inaccessible attachments by validating FileIds against the GridFS (fs.files) collection.
The query will:
- Remove invalid attachments from both Values and ValuesDocument.
- Retain valid attachments in the respective fields.
- Ensure no valid data is inadvertently removed.
const BATCH_SIZE = 1000; // Define batch size for updates
let totalProcessed = 0; // Track total processed records
// Fetch all Attachment Fields
const attachmentFields = db.Applications.aggregate([
{ $unwind: "$Fields" },
{ $match: { "Fields._t": "AttachmentField" } },
{ $project: { FieldId: "$Fields._id", ApplicationId: "$_id" } }
]).toArray();
print(`Found ${attachmentFields.length} attachment fields to process.`);
// Iterate through each Attachment Field
attachmentFields.forEach(({ FieldId }) => {
const bulkOps = [];
let fieldProcessed = 0;
print(`Processing records for attachment field: ${FieldId}`);
db.Records.find({
"Values.k": FieldId
}).forEach(record => {
const fieldData = record.Values.find(value => value.k === FieldId);
const attachments = fieldData?.v?._v || [];
if (!attachments.length) {
print(`No attachments found for Field ID ${FieldId} in record ${record._id}`);
return;
}
// Validate Files in GridFS
const validAttachments = attachments.filter(attachment => {
const fileExists = db.fs.files.findOne({ _id: attachment.FileId });
if (!fileExists) {
print(`Invalid FileId: ${attachment.FileId} in Record ID: ${record._id}`);
}
return !!fileExists;
});
if (validAttachments.length !== attachments.length) {
const updatedValues = record.Values.map(value => {
if (value.k === FieldId) {
return validAttachments.length > 0
? { k: FieldId, v: { _t: "System.Object[]", _v: validAttachments } }
: null;
}
return value;
}).filter(v => v !== null);
const updatedValuesDocument = { ...record.ValuesDocument };
if (validAttachments.length > 0) {
updatedValuesDocument[FieldId] = { _t: "System.Object[]", _v: validAttachments };
} else {
delete updatedValuesDocument[FieldId];
}
const updateDoc = {
$set: {
Values: updatedValues,
ValuesDocument: updatedValuesDocument
}
};
bulkOps.push({
updateOne: {
filter: { _id: record._id },
update: updateDoc
}
});
print(`Prepared update for Record ID: ${record._id}`);
}
fieldProcessed++;
totalProcessed++;
// Execute bulk updates in batches
if (bulkOps.length === BATCH_SIZE) {
db.Records.bulkWrite(bulkOps);
print(`Processed ${fieldProcessed} records for Field ID: ${FieldId} (Batch completed).`);
bulkOps.length = 0;
}
});
if (bulkOps.length > 0) {
db.Records.bulkWrite(bulkOps);
print(`Processed ${fieldProcessed} records for Field ID: ${FieldId} (Final Batch).`);
}
print(`Finished processing Field ID: ${FieldId}, Total Processed: ${fieldProcessed}`);
});
print(`Completed processing. Total records processed: ${totalProcessed}`);
When to Run the Script
This query is resource-intensive and should be run only when absolutely necessary. Below are the scenarios in which you may consider running it:
- Critical Business Impact:
- If inaccessible attachments are causing disruptions or critical workflow failures, run this script.
- Large Scale Corruption:
- If a significant number of records contain corrupted attachments and resolving them manually is not feasible.
Preconditions
To ensure the script runs efficiently and safely:
- Indexing:
- Ensure the following indexes are in place:
- Values.k in the Records collection.
- _id in the fs.files collection.
- Testing:
- Test the script on a small subset of records to verify its behavior before running it on the full dataset.
- Batch Execution:
- If you have a large dataset, split the execution into smaller chunks, such as processing records by application or by attachment field.
- Monitoring:
- Monitor database performance during execution to ensure minimal disruption to other operations.
Summary
Time Complexity:
- The query has a time complexity of: O(R * (A_r + U_r))
- R : Total records in Records.
- A_r : Average attachments per record.
- U_r : Average fields updated per record.
Performance Considerations:
- Linear complexity with respect to records and attachments makes it feasible but resource-intensive for large datasets.
Recommended Usage:
- Run only when necessary to address severe business or functional issues.
- Avoid running as part of routine maintenance.