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

Question

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!

$pullAll will not work for this try this db.getCollection("sandbox.sample").update({},{$pull: {"Widgets": {"WidgetIdLegacy": "ptp-0004"}}},{multi:true}); — turivishal
– turivishal, Commented Aug 7, 2020 at 15:28
I pasted the wrong query in and you're right, $pullAll doesn't work, but $pull only finds the array objects, it's not removing them as expected. I've made the appropriate edit to my question. Thank you! — Kevin O.
– Kevin O., Commented Aug 7, 2020 at 15:35
Ah! Yes, I see it now, thank you! I'll post the answer, much appreciated! — Kevin O.
– Kevin O., Commented Aug 7, 2020 at 15:45

Kevin O. · Accepted Answer · 2020-09-09 15:44:39Z

1

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

edited Sep 9, 2020 at 15:44

answered Aug 7, 2020 at 15:47

Kevin O.

792 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kunal Mukherjee · Accepted Answer · 2020-08-07 15:46:14Z

0

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);

answered Aug 7, 2020 at 15:46

Kunal Mukherjee

5,8733 gold badges29 silver badges55 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related