Find documents by the last element of nested array

[orpt (Or)]

I need to filter documents according to the value of the last element of a nested array using find
The reason I need it with find and not aggregation is the fact that the endpoint I’m sending the query to handle it is using only find. pretty weird but I gotta work with that at the moment.

I tried to use $arrayElemAt which is what I’ve found so far to handle it with find and I managed to get the first arrays value, but I can’t figure out how to select the ids array and to act according to innerName value.
Any suggestions?

Working query for the first array:

db.collection.find({
  $or: [
    {
      $expr: {
        "$eq": [
          {
            "$arrayElemAt": [
              "$matches.name",
              -1
            ]
          },
          "match 5"
        ]
      }
    }
  ]
})

Mock data of the use case:

[
  {
    "matches": [
      {
        "name": "match 1",
        "ids": [
          {
            "innerName": "12"
          },
          {
            "innerName": "3"
          }
        ]
      }
    ]
  },
  {
    "matches": [
      {
        "name": "match 5",
        "ids": [
          {
            "innerName": "123"
          },
          {
            "innerName": "1234"
          }
        ]
      },
      {
        "name": "match 5",
        "ids": [
          {
            "innerName": "1"
          },
          {
            "innerName": "1234"
          },
          
        ]
      },
      
    ]
  }
]

[Tarun_Gaur]

Hello @orpt ,

Welcome to The MongoDB Community Forums!

I notice you haven’t had a response to this topic yet - were you able to find a desired solution?
If not, could you please share the desired output with respect to the provided mock data?

Regards,
Tarun

[steevej (Steeve Juneau)]

If you are running 5.0 or more recent, you use $getField together with 2 calls to $arrayElemAt. Something along the following untested lines that results in an expression equals to the innerName of the last ids of the last matches:

{ "$getField" : {
    "field" : "innerName" ,
    "input" : { "$arrayElemAt" : [
        { "$getField" : {
            "field" : "ids" ,
            "input" : { "$arrayElemAt" : [
                "$matches" , 
                -1
            ] }
        } } ,
        -1
    ] }
} }

Note that with 4.0 and above you may use $last rather than $arrayElemAt which gives more concise code that should look like

{ "$getField" : {
    "field" : "innerName" ,
    "input" : { "$last" : { "$getField" : {
        "field" : "ids" ,
        "input" : { "$last" : "$matches" }
    } } }
} }