Delete Entries in MongoDB Collection According to Unique User Pairs

Question

Delete Entries in MongoDB Collection According to Unique User Pairs

I have a collection of messages stored in MongoDB and I need to keep only the latest 500 records for each pair of users. Users are identified by their sentBy and sentTo attributes.

/* 1 */
{
    "_id" : ObjectId("5f1c1b00c62e9b9aafbe1d6c"),
    "sentAt" : ISODate("2020-07-25T11:44:00.004Z"),
    "readAt" : ISODate("1970-01-01T00:00:00.000Z"),
    "msgBody" : "dummy text",
    "msgType" : "text",
    "sentBy" : ObjectId("54d6732319f899c704b21ef7"),
    "sentTo" : ObjectId("54d6732319f899c704b21ef5"),
}

// More document examples here...

/* and more... assume it's over 10,000 documents */

I'm thinking of an algorithm that involves:

Grouping based on user pairs using the OR operator
Sorting the records in descending order based on timestamp
Limited to 500 records per user pair
Extract the array of _id values to be preserved
Use these IDs in a new query with .deleteMany() and $nin condition

I've been struggling with this issue and would greatly appreciate any help or guidance. Thank you so much :)

javascript arrays mongodb sorting

Answer 1

Answer №1

Based on the size of the collection, two different approaches could be considered:

If the scale is manageable and you can group the entire collection within a reasonable timeframe, a similar method to your suggestion can be implemented:

db.collection.aggregate([
    {
        $sort: {
            sentAt: 1
        }
    },
    {
        $group: {
            _id: {
                $cond: [
                    {$gt: ["$sentBy", "$sentTo"]},
                    ["$sendBy", "$sentTo"],
                    ["$sentTo", "$sendBy"],
                ]
            },
            roots: {$push: "$$ROOT"}
        }
    },
    {
        $project: {
            roots: {$slice: ["$roots", -500]}
        }
    },
    {
        $unwind: "$roots"
    },
    {
        $replaceRoot: {
            newRoot: "$roots"
        }
    },
    {
        $out: "this_collection"
    }
])

The sorting stage should precede other operations as you cannot sort an inner array post grouping. The $cond in the group stage emulates the logic of the $or operator. Instead of fetching the result and using deleteMany with $nin, the $out operator can be used to rewrite the current collection.

If the scale is too large for the previous approach, iterating user by user and following your initial suggestion would be more efficient. Here's a simple example:


let userIds = await db.collection.distinct("sentBy");

let done = [1];
for (let i = 0; i < userIds.length; i++) {
    
    let matches = await db.collection.aggregate([
        {
            $match: {
                $and: [
                    {
                        $or: [
                            {
                                "sentTo": userIds[i]
                            },
                            {
                                "sendBy": userIds[i]
                            }
                        ]
                    },
                    {  
                        $or: [
                            {
                                sendTo: {$nin: done}
                            },
                            {
                                sendBy: {$nin: done}
                            }
                        ]   
                    }
                ]
            }
        },
        {
            $sort: {
                sentAt: 1
            }
        },
        {
            $group: {
                _id: {
                    $cond: [
                        {$eq: ["$sentBy", userIds[i]]},
                        "$sendTo",
                        "$sentBy"
                    ]
                },
                roots: {$push: "$$ROOT"}
            }
        },
        {
            $project: {
                roots: {$slice: ["$roots", -500]}
            }
        },
        {
            $unwind: "$roots"
        },
        {
            $group: {
                _id: null,
                keepers: {$push: "$roots._id"}
            }
        }
    ]).toArray();
    
    if (matches.length) {
        await db.collection.deleteMany(
            {
                $and: [
                    {
                        $or: [
                            {
                                "sentTo": userIds[i]
                            },
                            {
                                "sendBy": userIds[i]
                            }
                        ]
                    },
                    {  
                        $or: [
                            {
                                sendTo: {$nin: done}
                            },
                            {
                                sendBy: {$nin: done}
                            }
                        ]
                    },
                    {
                        _id: {$nin: matches[0].keepers}
                    }
                ]
            }
        )
    }
    
    done.push(userIds[i])
}

Answer 2

Based on the size of the collection, two different approaches could be considered:

If the scale is manageable and you can group the entire collection within a reasonable timeframe, a similar method to your suggestion can be implemented:

db.collection.aggregate([
    {
        $sort: {
            sentAt: 1
        }
    },
    {
        $group: {
            _id: {
                $cond: [
                    {$gt: ["$sentBy", "$sentTo"]},
                    ["$sendBy", "$sentTo"],
                    ["$sentTo", "$sendBy"],
                ]
            },
            roots: {$push: "$$ROOT"}
        }
    },
    {
        $project: {
            roots: {$slice: ["$roots", -500]}
        }
    },
    {
        $unwind: "$roots"
    },
    {
        $replaceRoot: {
            newRoot: "$roots"
        }
    },
    {
        $out: "this_collection"
    }
])

The sorting stage should precede other operations as you cannot sort an inner array post grouping. The $cond in the group stage emulates the logic of the $or operator. Instead of fetching the result and using deleteMany with $nin, the $out operator can be used to rewrite the current collection.

If the scale is too large for the previous approach, iterating user by user and following your initial suggestion would be more efficient. Here's a simple example:


let userIds = await db.collection.distinct("sentBy");

let done = [1];
for (let i = 0; i < userIds.length; i++) {
    
    let matches = await db.collection.aggregate([
        {
            $match: {
                $and: [
                    {
                        $or: [
                            {
                                "sentTo": userIds[i]
                            },
                            {
                                "sendBy": userIds[i]
                            }
                        ]
                    },
                    {  
                        $or: [
                            {
                                sendTo: {$nin: done}
                            },
                            {
                                sendBy: {$nin: done}
                            }
                        ]   
                    }
                ]
            }
        },
        {
            $sort: {
                sentAt: 1
            }
        },
        {
            $group: {
                _id: {
                    $cond: [
                        {$eq: ["$sentBy", userIds[i]]},
                        "$sendTo",
                        "$sentBy"
                    ]
                },
                roots: {$push: "$$ROOT"}
            }
        },
        {
            $project: {
                roots: {$slice: ["$roots", -500]}
            }
        },
        {
            $unwind: "$roots"
        },
        {
            $group: {
                _id: null,
                keepers: {$push: "$roots._id"}
            }
        }
    ]).toArray();
    
    if (matches.length) {
        await db.collection.deleteMany(
            {
                $and: [
                    {
                        $or: [
                            {
                                "sentTo": userIds[i]
                            },
                            {
                                "sendBy": userIds[i]
                            }
                        ]
                    },
                    {  
                        $or: [
                            {
                                sendTo: {$nin: done}
                            },
                            {
                                sendBy: {$nin: done}
                            }
                        ]
                    },
                    {
                        _id: {$nin: matches[0].keepers}
                    }
                ]
            }
        )
    }
    
    done.push(userIds[i])
}

Delete Entries in MongoDB Collection According to Unique User Pairs

Answer №1

Similar questions

What could be the reason for the page scrolling upwards when clicking on href="#"?

I can't seem to get anything to show up on my React

Information backed by the HTML5 Local Storage feature

What is the best way to ensure that JavaScript form errors disappear as soon as new input is entered?

Updating/Timer feature for a single feed out of two -- Combining data with $q.all()

Having trouble with your Ajax post request?

Error: The options object provided for CSS Loader is not valid and does not match the API schema. Please make sure to provide the correct options when

Unable to locate the Chart object within the chartjs-plugin-labels.js file

A method for highlighting duplicate rows in a DataTable by formatting them with the same color

Navigating through various div elements in Javascript and sending parameters to a script

Transform an array of integers into a mapping of values and corresponding IDs for selected values

Extracting information from a JSON file using the array name provided in the URL

Masonry layout organizes images in a vertical stack, with one image per row

Generate a JavaScript File/Blob from an image's URI

Dealing with problematic hover behaviors in Cypress: A guide

The antithesis of jQuery's .parents() selector

What is the most foolproof method for detecting when a checkbox has been marked as checked, regardless of the circumstances?

HTML/PHP/JS - Submit Form and Upload File Simultaneously

Cease making commitments or declarations

Utilizing Reactjs to efficiently transmit name and value to Material UI autocomplete