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

CodePudding user response：

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

CodePudding user response：

Use $regex

For your example this becomes:

db.collection.find({
  "students_replies.replies.name": {
    "$regex": "ason"
  }
})

https://mongoplayground.net/p/z5_FyPate-x