0

Hi I have a document with the structure as shown below:

{
  "_id": {
    "$oid": "5bc7a1d14cedfd0006445b10"
  },
  "externalId": {
    "$numberLong": "70285"
  },
  "passengers": [
    {
      "_id": {
        "$numberLong": "3757"
      },
      "name": "abc",
      "email": "abc@xyz.com"
    },
    {
      "_id": {
        "$numberLong": "398"
      },
      "name": "abc n",
      "email": "abc@xyz.com"
    }
  ]
}

Here I want to find all the documents where the first element of the passenger email is the same as the second element.

In MongoDB Compass I could use the filter to do like this:

{"passengers.0.email": "abc@xyz.com"}

This gives me all the emails where the passenger with the first item has "abc@xyz.com" as email. The same case is if I search for the second element.

But when I try to do the following

{"passengers.0.email": "passengers.1.email"}

It does not give me all docs where the first two items in the array have the same email. Is this possible using the MongoDB Compass?

Yong Shun
  • 35,286
  • 4
  • 24
  • 46
Sandeep Nair
  • 436
  • 2
  • 15

1 Answers1

1

You can work with $arrayElemAt operator to get the specific element in the array by index.

db.collection.find({
  $expr: {
    $eq: [
      {
        $arrayElemAt: [
          "$passengers.email",
          0
        ]
      },
      {
        $arrayElemAt: [
          "$passengers.email",
          1
        ]
      }
    ]
  }
})

Demo @ Mongo Playground

Yong Shun
  • 35,286
  • 4
  • 24
  • 46
  • Thank you so much for the answer and I will mark it so. One followup question. I ran this and i got in MongoDb compass MongoServerError: Feature not supported.. I have Amazon documentDb in prod, do you think this will not work with Document Db or is it more of a clilent thing – Sandeep Nair Jul 28 '23 at 08:07
  • Concern maybe is that Amazon DocumentDB doesn't support some MongoDB features. For your reference: [Comparing Amazon DocumentDB and MongoDB](https://www.mongodb.com/compare/documentdb-vs-mongodb) – Yong Shun Jul 28 '23 at 08:22
  • The `$arrayElemAt` operator should be able to work if the MongoDB version is [3.2 or above](https://www.mongodb.com/docs/v4.4/reference/operator/aggregation/arrayElemAt/). – Yong Shun Jul 28 '23 at 08:24
  • Ah.. with document db engine version 5.0, the query doesnt seem to work. Gives me "Feature not supported". By chance do you know of an alternative version for documentdb – Sandeep Nair Jul 28 '23 at 08:56
  • Hi I notice that the `$expr` operator doesn't support in query operator for DocumentDB version 5 ([Link](https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html#mongo-apis-query-evaluation-operators)). For your case, I think you should use the `.aggregate()` to filter the data. [Demo](https://mongoplayground.net/p/KKF0yj6T9Ue) – Yong Shun Jul 28 '23 at 09:03
  • Thanks.. Seems this too is not supported in document db – Sandeep Nair Jul 28 '23 at 09:13