I have a collection in MongoDB that looks something like this:

[
  {
    "machine": 1,
    "status": true,
    "comments": [
      {
        "machine": 1,
        "status": false,
        "emp": "158",
        "comment": "testing the latest update"
      },
      {
        "machine": 1,
        "status": false,
        "emp": "007",
        "comment": "2nd comment"
      },
    ]
  },
  {
    "machine": 2,
    "status": true,
    "comments": [
      {
        "machine": 2,
        "status": true,
        "emp": "158",
        "comment": "checking dcm 2"
      }
    ]
  }
]

I would like to return ALL of the top level documents (machines 1 & 2), but only comments by emp "007". I also only want to return the latest comment, not all. I got that part working with this line:

await db.collection('status').find().project( { "comments": { "$slice": -1 } }).toArray()

But I cannot for the life of me get it to then filter by 'emp' in the nested array.

CodePudding user response：

db.collection.aggregate([
  {
    $match: {
      "comments.machine": {
        $in: [
          1,
          2
        ]
      },
      "comments.emp": "007"
    }
  },
  {
    "$addFields": {//addFields with same name overwrites the array
      "comments": {
        "$filter": { //Filter the array elements based on condition
          "input": "$comments",
          "as": "comment",
          "cond": {
            $eq: [
              "$$comment.emp",
              "007"
            ]
          }
        }
      }
    }
  }
])

Playground

If you have a date, sort by that and get the top one.

CodePudding user response：

You can use a simple aggregation with $filter to get a clean output:

db.collection.aggregate([
  {
    $project: {
      machine: 1,
      status: 1,
      comments: {
        $slice: [
          {
            $filter: {
              input: "$comments",
              as: "item",
              cond: {$eq: ["$$item.emp", "007"]}}
          }, -1
        ]
      },
      _id: 0
    }
  },
  {$set: {comments: {$arrayElemAt: ["$comments", 0]}}},
])

Playground example