Search code examples
mongodbmongodb-queryaggregateaggregation

How can I match on the comparison of two fields in the same document in MongoDB?


I am at a stage in my Mongo aggregation where I have documents like this:

{
  "_id": ObjectId('648031bd784fbf6081de41cf'),
  "orgId": 1,
  "applications": {
    "_id": ObjectId('6479ddda073ced427d04e9dd'),
    "orgId": 1,
    "firstTimeInstalled": [
      {
        "refId": ObjectId('648031bd784fbf6081de41cf'),
        "installDate": "2023-06-08T09:18:49.233+00:00"
      },
      {
        "refId": ObjectId('6479ddda073ced427d04e9dd'),
        "installDate": "2023-06-08T09:18:49.233+00:00"
      }
    ]
  }
}

I want to only match on documents where applications.firstTimeInstalled does not contain an object where refId is equal to the _id in the root of the document.

I've tried to do this:

{
  $match: {
    "applications.firstTimeInstalled.refId": {
      $ne: "$_id"
    }
  }
}

But I still get documents back that doesn't meet the criteria so doesn't seem to work.

The thing that confuses me even more is that even if I do this:

{
  'applications.firstTimeInstalled.refId': {
    $nin: [ ObjectId('647746cfd9baa823f877c08f') ]
  }
}

As you can see I inject the _id manually, but this still returns the document... How is that possible?


Solution

  • a possible solution is to $map through the firstTimeInstalled array and if the mapped array has $anyElementTrue it means there is an element with _id === refId.
    So take the negation with $not

    db.collection.aggregate([
      {
        $match: {
          $expr: {
            $not: {
              $anyElementTrue: {
                $map: { 
                  input: "$applications.firstTimeInstalled",
                  in: { $eq: [ "$$this.refId", "$_id" ] }
                }
              }
            }
          }
        }
      }
    ])
    

    playground

    EDIT - better solution
    a better solution here thanks to turivishal
    quoting

    The $match can’t allow checking the internal fields condition directly, you need to use $expr operator, $not and $in operator to match your condition,

    db.collection.aggregate([
      {
        $match: {
          $expr: {
            $not: {
              $in: ["$_id", "$applications.firstTimeInstalled.refId"]
            }
          }
        }
      }
    ])
    

    playground