MongoDB aggregation: Count documents in a query for each array field-CodePudding

I have a collection called "sessions" with the following documents:

[
 {
  _id: ObjectId,
  status: "WAITING",
  members: ["ID1"]
 },
 {
  _id: ObjectId,
  status: "WAITING",
  members: ["ID1"]
 },
 {
  _id: ObjectId,
  status: "ENDED",
  members: ["ID1", "ID2"]
 }
]

I want to query all the documents with status "WAITING", which I'd use:

{ 
  $match: { status: "WAITING" }
}

But then I want to go through every value in the members field, which I'd probably use $unwind for. But I'm not sure how to move forward.

Here's an example through JS code of what I'm trying to achieve:

let waiting = findSessions()  // regular query for status "WAITING"
let results = [];
for (let w of waiting) {

  // Only push it to results if the w.members[0] and TARGET_USER_ID have never matched before.
  // Meaning, in the "session" collection, there are no documents that have these 2 IDs in the members field
  if (!hasMatchedBefore(w.members[0], "TARGET_USER_ID")) {
    results.push(w);
  }
}

EDIT, IGNORE MOST OF THE QUESTION ABOVE

Just realized how poorly written the old question was.

Consider the collection “sessions”:

[
 {
  _id: ObjectId,
  status: "WAITING",
  members: ["ID1"]
 },
 {
  _id: ObjectId,
  status: "WAITING",
  members: ["ID4"]
 },
 {
  _id: ObjectId,
  status: "ENDED",
  members: ["ID1", "ID2"]
 }
]

I’d like to query all the rooms that are “WAITING” first. Then iterate through each of those documents and check if targetID has already matched with members[0]: if they have already matched, then that session will not be returned in the aggregation.

Example: targetID = “ID2”

Intended response:

[
{
  _id: ObjectId,
  status: “WAITING”,
  members: [“ID4”],
},
]

Earlier you saw that ID1 & ID4 are both in waiting, but ID1 and targetID have already been matched previously, so it should only return ID4.

CodePudding user response：

I think, I have a solution for you.

Data

  [
  {
    _id: "ObjectId1",
    status: "WAITING",
    "members": [
      "ID1"
    ]
  },
  {
    _id: "ObjectId2",
    status: "WAITING",
    "members": [
      "ID2"
    ]
  },
  {
    _id: "ObjectId3",
    status: "ENDED",
    "members": [
      "ID1",
      "ID2"
    ]
  }
]

Query

    db.collection.find({
  status: "ENDED",
  members: {
    $elemMatch: {
      $eq: "ID2"
    }
  }
})

Output

[
  {
    "_id": "ObjectId3",
    "members": [
      "ID1",
      "ID2"
    ],
    "status": "ENDED"
  }
]

Note: Please check mongoplayground link.
Main Solution: https://mongoplayground.net/p/xkyyW8gsWV6
Other Solution: https://mongoplayground.net/p/1ndltdDU38-

CodePudding user response：

One option is to use $lookup on the same collection:

db.sessions.aggregate([
  {$match: {members: "ID2"}},
  {$group: {_id: 0, data: {$push: "$members"}}},
  {$set: {
      joined: {$reduce: {
          input: "$data",
          initialValue: [],
          in: {$setUnion: ["$$value", "$$this"]}
      }}
  }},
  {$lookup: {
      from: "sessions",
      let: {joined: "$joined"},
      pipeline: [
        {$match: {
            $and: [
              {status: "WAITING"},
              {$expr: {$not: {$in: [{$first: "$members"}, "$$joined"]}}}
            ]
        }}
      ],
      as: "res"
  }},
  {$project: {res: 1, _id: 0}}
])

See how it works on the playground example