2

Your query is fine. The problem is that you have a document where "$TransactionStatus.StatusType" doesn’t resolve to an array.

Here’s an example of two documents which match the criteria and give results:
(Either TransactionStatus or StatusType has to be an array, you haven’t given an example doc so I’ve used both options below.)

[
    {
      // doc 1
      TransactionStatus: [
        { StatusType: 2 },
        { StatusType: 1 }
      ]
    },
    {
      // doc 2
      TransactionStatus: {
        StatusType: [2, 1]
      }
    }
]

Mongo Playground example A

However, if any document was structured like this - as sub-documents instead of an array, then you get the $arrayElemAt's first argument must be an array error, even though the other docs are fine:

{
  TransactionStatus: {
    StatusType: 1
  }
}

Mongo Playground example B

You’ll need to find and fix the documents where "$TransactionStatus.StatusType" doesn’t resolve to an array. To do that, use a query where you check the $type is not an array, like this:

db.transaction.find({
  $expr: {
    "$ne": [
      { "$type": "$TransactionStatus.StatusType" },
      "array"
    ]
  }
})

Mongo Playground example C

1 Like