DocumentDb equivalent of Mongo's arrayElemAt, expr and eq

Question

I have following structure in documentdb

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

I was trying to find documents where first two element in array have same email

Now with mongo db the following query works (answered here )

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

However this does not work with DocumentDb. The document db engine version is 5.0

Is there an alternative query in documentDb for the same?

Answer 1

I think I have a solution for you, using $let which is supported in DocumentDB and allows for evaluation operators. Is not as straightforward as using $expr, but it does the job:

db.collection.aggregate([
  // Add two new fields corresponding to the first 2 array elements
  {
    $addFields: {
      firstEmail: { $arrayElemAt: ["$passengers.email", 0] },
      secondEmail: { $arrayElemAt: ["$passengers.email", 1] }
    }
  },
  // New field added to compare the added fields using $let
  {
    $addFields: {
      emailsMatch: {
        $let: {
          vars: {
            first: "$firstEmail",
            second: "$secondEmail"
          },
          in: { $eq: ["$$first", "$$second"] }
        }
      }
    }
  },
  // Filter where the match in previous stage is true
  {
    $match: {
      emailsMatch: true
    }
  },
  // Exclude the added fields to output your original docs
  {
    $project: {
      emailsMatch: 0,
      firstEmail: 0,
      secondEmail: 0
    }
  }
])

Answer 2

Problem is not the $arrayElemAt operator. However, operator $expr is not supported by DocumentDB, see Evaluation Query Operators

Workaround could be this one:

db.collection.aggregate([
   {
      $set: {
         equal: {
            $eq: [
               { $arrayElemAt: ["$passengers.email", 0] },
               { $arrayElemAt: ["$passengers.email", 1] }
            ]
         }
      }
   },
   { $match: { equal: true } },
   // { $unset: "equal" } <- not supported either!
])