CODEWITHSUNDEEP
mongodb
Kevin O.
Kevin O.

Reputation: 79

MongoDB Query to remove specific objects from an array based on object keyword

I'm trying to remove several hundred thousand array objects spread out over thousands of documents that were migrated in error but can't quite get my query to work. I need to remove the entire array object if a single field within that object matches. Here's sample data:

{
    "_id": {
        "$oid": "5f11d4c28663f32e940696e0"
    },
    "Widgets": [{
        "WidgetId": 1,
        "Name": "Basic Widget",
        "YearLaunched": 2011,
        "InitialProduction": 50000,
        "WidgetIdLegacy": "leg-0120"
    }, {
        "WidgetId": 2,
        "Name": "Advanced Widget",
        "YearLaunched": 2013,
        "InitialProduction": 35000,
        "WidgetIdLegacy": "leg-0150"
    }, {
        "WidgetId": 3,
        "Name": "Prototype Widget",
        "YearLaunched": 2009,
        "InitialProduction": 100,
        "WidgetIdLegacy": "ptp-0004"
    }],
    "Manufacturer": "WidgetCo",
    "Status": 3,
}
{
    "_id": {
        "$oid": "5f11d4c28663f32e940696e1"
    },
    "Widgets": [{
        "WidgetId": 10,
        "Name": "Widget 2.0",
        "YearLaunched": 2017,
        "InitialProduction": 150000,
        "WidgetIdLegacy": "leg-5099"
    }, {
        "WidgetId": 20,
        "Name": "Advanced Widget 2.0",
        "YearLaunched": 2018,
        "InitialProduction": 10000,
        "WidgetIdLegacy": "leg-5199"
    }, {
        "WidgetId": 3,
        "Name": "Prototype Widget",
        "YearLaunched": 2009,
        "InitialProduction": 100,
        "WidgetIdLegacy": "ptp-0004"
    }],
    "Manufacturer": "Acme Widgets",
    "Status": 3,
}

I want to remove the entire object from the Widgets array where the Widgets.WidgetIdLegacy: "ptp-0004".

Through reading other similar questions I write the following query which seems to find the objects but doesn't pull them so I know I'm missing something simple and fundamental

db.getCollection("sandbox.sample").update({},{$pull: {"WidgetIdLegacy": "ptp-0004"}},{multi:true});

The final data should look like:

{
    "_id": {
        "$oid": "5f11d4c28663f32e940696e0"
    },
    "Widgets": [{
        "WidgetId": 1,
        "Name": "Basic Widget",
        "YearLaunched": 2011,
        "InitialProduction": 50000,
        "WidgetIdLegacy": "leg-0120"
    }, {
        "WidgetId": 2,
        "Name": "Advanced Widget",
        "YearLaunched": 2013,
        "InitialProduction": 35000,
        "WidgetIdLegacy": "leg-0150"
    }],
    "Manufacturer": "WidgetCo",
    "Status": 3,
}
{
    "_id": {
        "$oid": "5f11d4c28663f32e940696e0"
    },
    "Widgets": [{
        "WidgetId": 10,
        "Name": "Widget 2.0",
        "YearLaunched": 2017,
        "InitialProduction": 150000,
        "WidgetIdLegacy": "leg-5099"
    }, {
        "WidgetId": 20,
        "Name": "Advanced Widget 2.0",
        "YearLaunched": 2018,
        "InitialProduction": 10000,
        "WidgetIdLegacy": "leg-5199"
    }],
    "Manufacturer": "Acme Widgets",
    "Status": 3,
}

Thanks for any help!

Upvotes: 0

Views: 42

Answers (2)

Kevin O.
Kevin O.

Reputation: 79

Working solution!

db.getCollection("sandbox.sample").update({},{$pull: {"Widgets": {"WidgetIdLegacy": "ptp-0004"}}},{multi:true});

I needed to define the name of the array first, that was the missing component

Upvotes: 1

Kunal Mukherjee
Kunal Mukherjee

Reputation: 5853

Try using the $pull operator to remove the sub-document.

var filter = Object.create(null);

var update = {
    $pull: {
        Widgets: {
            WidgetIdLegacy: "ptp-0004"
        }
    }
};

var options = { 
    multi: true 
};

db.collection.update(filter, update, options);

Upvotes: 0

Related Questions