How do I search for all elements in a nested array NOT matching a certain parameter?

[Ajay_Pillay]

Hi, I have some data that looks like this:

{
  _id: "someID"
  array1: [
    { _id: "id1", array2: [
         {_id: "a", other_data: "data"},
         {_id: "b", other_data: "data"},
         {_id: "c", other_data: "data"}
       ]
    }.
    { _id: "id2", array2: [
         {_id: "d", other_data: "data"},
         {_id: "e", other_data: "data"},
         {_id: "a", other_data: "data"}
       ]
    }.
    { _id: "id3", array2: [
         {_id: "x", other_data: "data"},
         {_id: "y", other_data: "data"},
         {_id: "z", other_data: "data"}
       ]
    }
  ]
}

I wish to find all elements in array1 which do not have an element in array2 with the ID of “a”. So, I wish to retrieve an array as such, since only id3's array2 field does not contain an element with _id “a”.

[
  {
    _id: "id3"
  }
]

If I repeat this for searching for “b”, I should get the following since only id2 and id3 do not contain “b”:

[
  {
    _id: "id2"
  },
  {
    _id: "id3"
  }
]

If I search for “q”, I should get the following since they all do not contain “q” in array2:

[
  {
    _id: "id1"
  },
  {
    _id: "id2"
  },
  {
    _id: "id3"
  }
]

I tried db.findOne({ _id: mainID, "array1.array2._id" : { $ne: selectedID } })) but this isn’t giving me the expected result.

How should I go about this?

Any help is appreciated, thank you!

[Ajay_Pillay]

I tried using an aggregate but have only managed to get this far:

db.getCollection('myDB').aggregate([
    { $match: { _id: "someID" }},
    { $project: { "array1": {
            $filter: {
                    input: "$array1",
                    as: "item",
                    cond: { $ne: ["$$item.array2._id", "id"]}
            }
        }
      }
    }
])

But this isn’t quite giving me the expected result. I think this is going down the right path but I’m not sure how I’m supposed to be checking the condition correctly. It seems to evaluate to true regardless of what the input string is on the right.

I think I need to somehow include another projection filter in the first bit after $ne but I’m not quite sure how to do this correctly.

[Ajay_Pillay]

Managed to solve this!

db.getCollection('myDB').aggregate([
    { $match: { _id: "someID" } },
    { $unwind: "$array1"},
    {
        $project: {
            "array1._id": 1,
            "array2": {
                $filter: {
                    input: "$array1.array2",
                    as: "item",
                    cond: { $ne: ["$$item._id" , "someOtherID"] }
                }
            }
        }
    }
])

First I match the document’s ID, then I unwind array1. Next, in the projection I keep the unique _id tied to array1 and filter its array2 to look for all items not equal to my chosen "someOtherID".

Works like a charm.

[Ajay_Pillay]

So as a final follow-up, this didn’t quite give me the data I required (it missed a little extra bit, where it returns the _id of the array1 element.

The final version is as such:

db.getCollection("myDB").aggregate([
  { $match: { _id: "someID" } },
  { $unwind: "$array1" },
  {
    $project: {
      "array1._id": {
        $cond: [{ $in: [{ _id: "someIDToCheck" }, "$array1.array2"] }, null, "$array1._id"]
      },
      "array2": {
        $filter: {
          input: "$array1.array2",
          as: "item",
          cond: { $ne: ["$$item._id", "someIDToCheck"] }
        }
      }
    }
  }
])

This gets me my desired data, the first result is when some element with the ID of someIDToCheck exists in array2, and the second is when it does NOT exist. The returned array2 will never contain the element with ID of someIDToCheck:

[{
    "_id" : "someID",
    "array1" : {
        "_id" : null
    },
    "array2" : [ 
        {
            "_id" : "someOtherIDNotMatched"
        }, ...
    ]
},
{
    "_id" : "someOtherID",
    "array1" : {
        "_id" : "someHashedID"
    },
    "array2" : [ 
        {
            "_id" : "someOtherIDNotMatched2"
        }, ...
    ]
}]

For my use case I actually don’t need the array2 result, so I removed that bit. I just need the array1 IDs which do NOT contain someIDToCheck in their respective array2's. If it does contain, it just returns null in the array1._id field.

EDIT: Small correction, for the array1._id projection it should be:

"array1._id": {
            $cond: [{ $in: ["someIDToCheck" , "$array1.array2._id"] }, null, "$array1._id"]
          },

The first argument should be the string, not an object with the _id as the key and the string as the value. The second argument should be querying on the _id field of the array element.