MongoDB: get documents by last element value in nested array-CodePudding

This question is slightly different from others since I need to get the whole documents and not just specific fields.

I need to filter documents(all of the document, not just specific fields), according to the last elements value of a nested array. (doc.array[i].innerArray[innerArray.length - 1].desiredField)

Documents are looking like this:

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

So if we filter according to innerName = '1234', this is the result:

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

CodePudding user response：

One option is:

db.collection.find({
  $expr: {
    $in: [
      "1234",
      {$reduce: {
          input: "$matches",
          initialValue: [],
          in: {$concatArrays: ["$$value", [{$last: "$$this.ids.innerName"}]]}
        }
      }
    ]
  }
})

See how it works on the playground example

CodePudding user response：

Another option:

db.collection.aggregate([
{
$match: {
  $expr: {
    $gt: [
      {
        $size: {
          $filter: {
            input: "$matches",
            cond: {
              $in: [
                {
                  $last: "$$this.ids.innerName"
                },
                [
                  "1234"
                ]
              ]
            }
          }
        }
      },
      0
    ]
  }
 }
}
])

Explained:

Match only documents where size of array is >0 for those who has "1234" in last nested array element.

Playground: