finding element inside array of object mongodb

Question

I want to find inside array of object based upon 2 conditions, in mongodb collection. Please refer the attached image for schema.

My query is like this:

Find inside toUsers array, where _id == given ObjectId AND user=given_user_object_id, AND visited == false

Basically I want to filter out all users with visited == false, for a given _id.

 [{
  "_id": {
    "$oid": "651548277423d019b0c251f6"
  },
  "toUsers": [
    {
      "user": {
        "$oid": "65014c6303b5bf048f627b72"
      },
      "visited": true,
      "createdAt": {
        "$date": "2023-09-28T09:32:23.352Z"
      },
      "updatedAt": {
        "$date": "2023-09-28T11:44:00.673Z"
      }
    },
    {
      "user": {
        "$oid": "650167123cc7410860126076"
      },
      "visited": false,
      "createdAt": {
        "$date": "2023-09-28T09:32:23.352Z"
      },
      "updatedAt": {
        "$date": "2023-09-28T09:32:23.352Z"
      }
    }
  ],
  "notification": {
    "message": "ccc - Skumar required approval for this workbook. This is very long text that might truncate",
    "action": {
      "workbook": "6511674ed0a5e9db5823051b",
      "actionText": "Goto Workbook"
    }
  },
  "expiryTime": {
    "$date": "2022-03-06T11:00:00.000Z"
  },
  "createdBy": {
    "$oid": "65014c6303b5bf048f627b72"
  },
  "updatedBy": "65014c6303b5bf048f627b72",
  "createdAt": {
    "$date": "2023-09-28T09:32:23.352Z"
  },
  "updatedAt": {
    "$date": "2023-09-28T11:44:00.673Z"
  }
},
{
  "_id": {
    "$oid": "6515493994822bf9866ee885"
  },
  "toUsers": [
    {
      "user": {
        "$oid": "65014c6303b5bf048f627b72"
      },
      "visited": true,
      "createdAt": {
        "$date": "2023-09-28T09:36:57.827Z"
      },
      "updatedAt": {
        "$date": "2023-09-28T11:18:59.759Z"
      }
    },
    {
      "user": {
        "$oid": "650167123cc7410860126076"
      },
      "visited": false,
      "createdAt": {
        "$date": "2023-09-28T09:36:57.828Z"
      },
      "updatedAt": {
        "$date": "2023-09-28T09:36:57.828Z"
      }
    }
  ],
  "notification": {
    "message": "zzz - SKumar required approval for this workbook. This is very long text that might truncate",
    "action": {
      "workbook": "6511674ed0a5e9db5823051b",
      "actionText": "Goto Workbook"
    }
  },
  "expiryTime": {
    "$date": "2022-03-06T11:00:00.000Z"
  },
  "createdBy": {
    "$oid": "65014c6303b5bf048f627b72"
  },
  "updatedBy": {
    "$oid": "65014c6303b5bf048f627b72"
  },
  "createdAt": {
    "$date": "2023-09-28T09:36:57.828Z"
  },
  "updatedAt": {
    "$date": "2023-09-28T11:18:59.759Z"
  }
}]

Answer 1

• you can use $filter to filter the toUsers array based on your condition

1. First filter out the main document using the _id
2. Filter the toUsers of the filtered document

by using the $addFields you won't lose the other fields so you have freedom to $project any unwanted fields.

db.collection.aggregate([
  { $match: { "_id": ObjectId("651548277423d019b0c251f6") } },
  {
    $addFields: {
      toUsers: {
        $filter: {
          input: "$toUsers",
          cond: {
            $and: [
              { $eq: [ "$$this.user", ObjectId("650167123cc7410860126076") ] },
              { $eq: [ "$$this.visited", false ] }
            ]
          }
        }
      }
    }
  },
  { $match: { toUsers: { $ne: [] }  } } //to avoid document when toUsers empty
])

demo

• Using an $unwind->$match-> $group strategy. I prefer the former approach since here you will lose the other fields unless you explicitly specify them in the $group using $first

db.collection.aggregate([
  { $match: { "_id": ObjectId("651548277423d019b0c251f6") } },
  { $unwind: "$toUsers" },
  { $match: { "toUsers.user": ObjectId("650167123cc7410860126076"), "toUsers.visited": false } },
  { $group: { _id: "$_id", toUsers: { $push: "$toUsers" } } }
])

demo