How to match string within embedded array or doc in MongoDB?

Question

After searching for a whole day, I am doubting whether MongoDB can fulfill below requirement:

Q: How can I filter out documents that meet below conditions ?

• In last array element of students_replies, there is a reply from a student whose name containing string 'ason'.

id_1: first_school, students_replies: [
    {Date:20210101, replies: [
        {name: jack, reply: 'I do not like this idea'},
        {name: jason, reply: 'I would rather stay at home'},
        {name: charles, reply: 'I have an plan to improve'},
        ]}, 
    {Date:20210401, replies: [
        ...]}, 
    {Date:20210801, replies: [
        ...]},
]

id_2: second_shool, students_replies: [..]
id_3: third_shool, students_replies: [...]

Mongoplayground

Answer 1

Use $slice and $regex

For your example this becomes:

db.collection.aggregate([
  // project only the last reply
  {
    "$project": {
      key: 1,
      last_reply: {
        "$slice": [
          "$students_replies",
          -1
        ]
      }
    }
  },
  // filter the documents
  {
    "$match": {
      "last_reply.replies.name": {
        "$regex": "ason"
      }
    }
  }
])

https://mongoplayground.net/p/a9piw2WQ8n6

Answer 2

Since you need last array element of students_replies, use $arrayElemAt

db.collection.aggregate([
  {
    "$match": {
      $expr: {
        $regexMatch: {
          input: {
            $reduce: {
              input: {
                $arrayElemAt: [
                  "$students_replies.replies",
                  -1
                ]
              },
              initialValue: "",
              in: {
                $concat: [
                  "$$value",
                  "$$this.name",
                  ","
                ]
              }
            }
          },
          regex: "ason"
        }
      }
    }
  },
  {
    "$project": {
      "students_replies": 0
    }
  }
])

mongoplayground

---

another answer

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $ne: [
          {
            $filter: {
              input: {
                $map: {
                  input: {
                    $arrayElemAt: [
                      "$students_replies.replies",
                      -1
                    ]
                  },
                  as: "r",
                  in: "$$r.name"
                }
              },
              as: "s",
              cond: {
                $regexMatch: {
                  input: "$$s",
                  regex: "ason"
                }
              }
            }
          },
          []
        ]
      }
    }
  },
  {
    "$project": {
      "students_replies": 0
    }
  }
])

mongoplayground