MongoDb: Delete index of an array within another array

Question

Assuming the following structure:

{
  'Tests': [ // Top Array
     {
        'Name': 'A',
         'Data': [ // Second Array
             {
                'Fact': '1'
             }
          ]
     }
  ]
}

When I try t to remove any Fact matching the value 1, I write the following query and it works!

This query is run using the C# driver

{$pull: {Tests: {Data: {Fact: '1'}}}}

The problem here is that, although it removes Fact: 1, the parent of the object is also deleted which in this case leave the Tests array empty.

In order to ditch this problem, I tried to change my query to:

{$pull: {'Tests.Data.Fact': '1'}}

Or

{$pull: {'Tests.$.Data.Fact': '1'}} // and also {$pull: {'Tests.Data.$.Fact': '1'}}

Or

{$pull: {'Tests.$.Data.$.Fact': '1'}}

but all failed.

The question is, what is the syntax that I should follow if I only and only want the Fact: '1' to be removed and get the following result:

{
  'Tests': [ // Top Array
     {
        'Name': 'A',
         'Data': [ // Second Array
             {
                //Empty
             }
          ]
     }
  ]
}

dnickless · Accepted Answer · 2018-05-14T13:43:05.297

Using MongoDB > v3.6 you can use the "all positional operator" $[] to achieve that:

db.collection.update({}, { $pull: { "Tests.$[].Data": { "Fact": "1" } } })

UPDATE in response to your comment:

If you want to pull all matching instances from the first matching entry in the Tests array then this can be done like so:

db.collection.update({"Tests.Data": { $elemMatch: { "Fact": "1" } } }, { $pull: { "Tests.$.Data": { "Fact": "1" } } })

Let's look at the following example document:

{
    "Tests" : [ 
        {
            "Name" : "A",
            "Data" : [
                { 'Fact': '1' }, // first matching entry in "A"
                { 'Fact': '1' }, // second matching entry in "A"
                { 'Fact': '2' },
             ]
        },
        {
            "Name" : "B",
            "Data" : [
                { 'Fact': '1' }, // first matching entry in "B"
                { 'Fact': '1' }, // second matching entry in "B"
                { 'Fact': '2' },
             ]
        }
    ]
}

Running the above query once will give you this:

{
    "Tests" : [ 
        {
            "Name" : "A",
            "Data" : [
                // all matching items gone from "A"
                { 'Fact': '2' }
             ]
        },
        {
            "Name" : "B",
            "Data" : [
                { 'Fact': '1' }, // first matching entry in "B"
                { 'Fact': '1' }, // second matching entry in "B"
                { 'Fact': '2' }
             ]
        }
    ]
}

Running this a second time will wipe out all instances from "B", too.

{
    "Tests" : [ 
        {
            "Name" : "A",
            "Data" : [
                // all matching items gone from "A"
                { 'Fact': '2' }
             ]
        },
        {
            "Name" : "B",
            "Data" : [
                // all matching items gone from "B"
                { 'Fact': '2' }
             ]
        }
    ]
}

If, however, you want to update only the very first matching instance inside the first matching entry in the Tests array then I do not think this can be done in a single operation. However, here's a bit of a hack that appears to work:

db.collection.update({"Tests.Data": { $elemMatch: { "Fact": "1" } } }, { $set: { "Tests.$.Data.0": { "delete_me": 1 } } }) // this will set the first found { Fact: "1" } document inside the Tests.Data arrays to { delete_me: 1 }
db.collection.update({}, { $pull: { "Tests.$[].Data": { "delete_me": 1 } } }) // this will just delete the marked records from all arrays

Running this query once will yield this:

{
    "Tests" : [ 
        {
            "Name" : "A",
            "Data" : [
                // first matching item gone from "A"
                { 'Fact': '1' }, // second matching entry in "A"
                { 'Fact': '2' }
             ]
        },
        {
            "Name" : "B",
            "Data" : [
                { 'Fact': '1' }, // first matching entry in Name "B"
                { 'Fact': '1' }, // second matching entry in Name "B"
                { 'Fact': '2' }
             ]
        }
    ]
}

The next time you run this, again, another entry will be deleted:

{
    "Tests" : [ 
        {
            "Name" : "A",
            "Data" : [
                // all matching items gone from "A"
                { 'Fact': '2' }
             ]
        },
        {
            "Name" : "B",
            "Data" : [
                { 'Fact': '1' }, // first matching entry in Name "B"
                { 'Fact': '1' }, // second matching entry in Name "B"
                { 'Fact': '2' }
             ]
        }
    ]
}

Third run:

{
    "Tests" : [ 
        {
            "Name" : "A",
            "Data" : [
                // all matching items gone from "A"
                { 'Fact': '2' }
             ]
        },
        {
            "Name" : "B",
            "Data" : [
                // first matching item gone from "B"
                { 'Fact': '1' }, // second matching entry in Name "B"
                { 'Fact': '2' }
             ]
        }
    ]
}

And finally, fourth run:

{
    "Tests" : [ 
        {
            "Name" : "A",
            "Data" : [
                // all matching items gone from "A"
                { 'Fact': '2' }
             ]
        },
        {
            "Name" : "B",
            "Data" : [
                // all matching items gone from "B"
                { 'Fact': '2' }
             ]
        }
    ]
}

Thanks, but how can I defined to the query to only remove the first encounter (item matching the condition) ? — Arnold Zahrneinder, May 14 '18 at 12:16
Amazing, thank you very very much indeed for the fantastic answer. — Arnold Zahrneinder, May 14 '18 at 14:11

score 0 · Answer 2 · answered May 14 '18 at 05:33

The $pull expression applies the condition to each element of the array as though it were a top-level document.Hence, it will Test array to null. To overcome this we would need some scripting.Assuming that the collection name is TestArray following javascript would work on the mongo shell :

db.TestArray.find({"_id":2}).forEach(function(doc){
    var TestArray = doc.Test ;
    for (var i =0 ; i < TestArray.length ; i++) {
        DataArray = TestArray[i].Data;
        for (var j =0 ; j < DataArray.length ; j++) {
            DataElement = DataArray[j];
            if (DataElement.fact == 1 )
                DataArray.splice(j,1) ;
    }
}
db.TestArray.save(doc); });

The above code looks for the nested element fact and then uses splice operator to remove that element from the inner array.Finally, the modified document is being saved to collection.

MongoDb: Delete index of an array within another array

2 Answers2