Failing query with arrayElemAt

Working with Data
,
1

I have a query that has been unchanged for years that has just started failing with the message:

$arrayElemAt’s first argument must be an array, but is int

I can reproduce it at the Mongo sh, e.g.

db.transaction.find({ $expr: { “$eq” : [ { “$arrayElemAt” : [ “$TransactionStatus.StatusType”, -1 ] }, 1 ] } })

I can see several groups (paging with “it”), but eventually it errors:

Error: command failed: {
“ok” : 0,
“errmsg” : “Executor error during getMore :: caused by :: $arrayElemAt’s first argument must be an array, but is int”,
“code” : 28689,
“codeName” : “Location28689”,
“$clusterTime” : {
“clusterTime” : Timestamp(1738928181, 1),
“signature” : {
“hash” : BinData(0,“LfnwaDc1kPPHyBoQxwK4VWqGA+I=”),
“keyId” : NumberLong(“7416883275756470273”)
}
},
“operationTime” : Timestamp(1738928181, 1)
} : getMore command failed: {
“ok” : 0,
“errmsg” : “Executor error during getMore :: caused by :: $arrayElemAt’s first argument must be an array, but is int”,
“code” : 28689,
“codeName” : “Location28689”,
“$clusterTime” : {
“clusterTime” : Timestamp(1738928181, 1),
“signature” : {
“hash” : BinData(0,“LfnwaDc1kPPHyBoQxwK4VWqGA+I=”),
“keyId” : NumberLong(“7416883275756470273”)
}
},
“operationTime” : Timestamp(1738928181, 1)
}

How can I find the record that is causing the error? There are 80,000 matches in the DB.

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
3

Not necessarily :thinking:.

Let’s say you want to ignore the problematic documents but still want to do the search on valid documents: Join the type=array and lastValue=1 checks with an $and so that only documents which have "$TransactionStatus.StatusType" as an array get checked.

db.transaction.find({
  $expr: {
    $and: [
      {
        "$eq": [
          { "$type": "$TransactionStatus.StatusType" },
          "array"
        ]
      },
      {
        "$eq": [
          { "$arrayElemAt": ["$TransactionStatus.StatusType", -1] },
          1
        ]
      }
    ]
  }
})

Mongo Playground example D

You could also make your query more permissive by accommodating the incorrect structure (those non-arrays) by wrapping that $and clause with an $or, like this. But that could lead to errors when processing the result documents in your application code, where it’s expected to be an array.

Consider adding Schema Validation to prevent future inserts or updates which incorrectly change the structure.

1 Like
4

Thanks so much for your script! You are right, I found two examples of records that didn’t follow the format: TransactionStatus contained a single object rather than an array. Problem fixed; thanks for your help!