Eliminating redundant subdocuments in MongoDB

Question

Eliminating redundant subdocuments in MongoDB

This particular schema represents a single document out of thousands in the database, all housed within the same collection.

Document 1:

{
    pageNumber: 0,
    results: [
        {
            jobkey: "AAA", 


        },
           {
            jobkey: "BBB",


        },
           {
            jobkey: "CCC",


        }
    ]
}

Document 2:

{
    pageNumber: 0,
    results: [
        {
            jobkey: "RRR", 


        },
           {
            jobkey: "VVV",


        },
           {               //This Entire Object needs to be removed
            jobkey: "AAA", //Duplicate jobkey value of document 1
                           //remaining objects in array should stay

        }
    ]
}

In this structure, each document contains a key called "results" that holds an array of objects. These objects consist of a jobkey and its corresponding value. Importantly, no two jobkeys within the same results array can have the same value.

The Problem:

If there is a duplicate jobkey value across different documents or within the same document, I require one of the duplicates to be deleted from the database. This situation might occur where the same jobkey exists in multiple result arrays.

Despite efforts in the mongo shell and through mongoose, a solution to remove these duplicate values has proven elusive.

javascript mongodb mongoose mongodb-query aggregation-framework

Answer 1

Answer №1

This concept of "duplicates" seems slightly unconventional to me, especially considering that the values are stored in separate documents. As a result, enforcing this definition in future operations would require querying the entire collection each time to check if a value already exists before adding it to a target document.

To detect and eliminate these so-called "duplicates," you may need to execute an operation like the following:

db.collection.aggregate([
    // Filter out arrays with no content
    { "$match": { "results.0": "$exists } },

    // Unwind the array
    { "$unwind": "$results" },

    // Group keys by count and store corresponding doc _id's
    { "$group": {
        "_id": "$results.jobkey",
        "_ids": { "$push": "$_id" },
        "count": { "$sum": 1 }
    }},

    // Identify duplicate matches only
    { "$match": { "count": { "$gt": 1 } }
]).forEach(function(doc) {
    doc._ids.shift();    // remove the first element
    db.collection.update(
        { "_id": { "$in": doc._ids } },
        { "$pull": { "results": { "jobkey": doc._id } } },
        { "multi": true }
    )
})

In essence, this process involves identifying a list of terms designated as "duplicates" and then iteratively removing instances of such duplicates within other documents' arrays that contain a matching value.

It's important to note that this method assumes the designation of the "first" document containing the duplicated value as the definitive location for retention. If alternative criteria exist for determining where a duplicate should be retained, consider using $sort before $group.

The approach retains a list because only non-"first" documents require updates. The subsequent $match filter excludes cases where a grouped key has just one occurrence of the same value.

By cycling through these results, you essentially exclude the "first" document from the list since it is considered the original. The subsequent .update() function targets the "duplicate" documents in the list using $pull to remove array elements matching the specified jobkey value across all matched documents.

If your objective is to retain sub-document elements with unique jobkey values, consider storing these documents in a separate collection and referencing them within the parent array. In a distinct collection, apply a "unique constraint" on the index to prevent duplicate values from being inserted.

Answer 2

This concept of "duplicates" seems slightly unconventional to me, especially considering that the values are stored in separate documents. As a result, enforcing this definition in future operations would require querying the entire collection each time to check if a value already exists before adding it to a target document.

To detect and eliminate these so-called "duplicates," you may need to execute an operation like the following:

db.collection.aggregate([
    // Filter out arrays with no content
    { "$match": { "results.0": "$exists } },

    // Unwind the array
    { "$unwind": "$results" },

    // Group keys by count and store corresponding doc _id's
    { "$group": {
        "_id": "$results.jobkey",
        "_ids": { "$push": "$_id" },
        "count": { "$sum": 1 }
    }},

    // Identify duplicate matches only
    { "$match": { "count": { "$gt": 1 } }
]).forEach(function(doc) {
    doc._ids.shift();    // remove the first element
    db.collection.update(
        { "_id": { "$in": doc._ids } },
        { "$pull": { "results": { "jobkey": doc._id } } },
        { "multi": true }
    )
})

In essence, this process involves identifying a list of terms designated as "duplicates" and then iteratively removing instances of such duplicates within other documents' arrays that contain a matching value.

It's important to note that this method assumes the designation of the "first" document containing the duplicated value as the definitive location for retention. If alternative criteria exist for determining where a duplicate should be retained, consider using $sort before $group.

The approach retains a list because only non-"first" documents require updates. The subsequent $match filter excludes cases where a grouped key has just one occurrence of the same value.

By cycling through these results, you essentially exclude the "first" document from the list since it is considered the original. The subsequent .update() function targets the "duplicate" documents in the list using $pull to remove array elements matching the specified jobkey value across all matched documents.

If your objective is to retain sub-document elements with unique jobkey values, consider storing these documents in a separate collection and referencing them within the parent array. In a distinct collection, apply a "unique constraint" on the index to prevent duplicate values from being inserted.

Eliminating redundant subdocuments in MongoDB

Answer №1

Similar questions

Navigating through pages using React Native is a straightforward process that involves utilizing

Possible Rewrite: "Is it possible to dynamically adjust the `<p>` value when modifying the range meter in JavaScript?"

Summarize the array of objects and find the average value for each distinct object name

Using Firebase with Angular 4 to fetch data from the database and show it in the browser

Closing the Main Dropdown Navbar in Bootstrap 4: What happens once you click on a Menu Option?

How can I troubleshoot the overflow-y problem in a tab modal from w3 school?

Prevent onClick event in jQuery and extract parameters from a function call

Utilizing React Redux Loading Bar: Error - Unable to access property 'default' of an undefined object

Synchronize numerous PouchDB databases with a single CouchDB database

The method `collectionGroup` is not recognized as a function within the context of _firebase__WEBPACK_IMPORTED_MODULE_10__.usersCollection

Counting occurrences of characters in a string: A simple guide

`The functionalities of classList.add and classList.remove aren't behaving as anticipated.`

javascript tabs not functioning as expected in HTML

Tips for displaying complex JSON structures in VueJS

Sign up process through AngularJS

Using toLocaleDateString method in Node for handling dates

Instructions for enabling the touch slider feature in the Igx carousel component with Angular 6 or higher

What is the best way to loop through unsorted JSON data and organize it into an array?

Pattern matching to exclude specific characters

Size attribute set to 0 for an HTML input element