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

Tips for pausing keyframes animation on its final animation frame

What is the reason behind this Uncaught TypeError that is happening?

transform the PHP object array string into a PHP variable object

``What is the best way to handle CRUD operations in an Express application?

Create a basic single page application with Node.js and Express

Passing all emitted events from Vue 3 child component to its parent - A complete guide

Eliminating the bottom border of all buttons, except for the last three buttons in the list, solely using pure JavaScript, unless alternative methods are available

Discovering the smallest, largest, and average values across all properties in an array of objects

Determine the amount of unused vertical space within a block of text

What is the most effective way to update the React state based on an if-else condition within a

Display a dropdown menu when hovering over with a delay

sending an array from one CodeIgniter view to another view via Ajax

The communication hub in a Vue.js application

I am looking to integrate my information into the user interface using Angular

Why is it that this JavaScript isn't working as intended in the popup form?

Is it possible to set up a PHP variable within a JavaScript function?

If the checkbox is selected, the textbox will receive the class "form-input validate required" upon Jquery validation

Using a variable as an argument for a DOM function in JavaScript

The grid fails to apply remote filtering values when an additional Nested ajax call is incorporated alongside the current HttpProxy configuration

Disable, Hide, or Remove Specific Options in a Single Dropdown Selection