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

Question

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);
  }
}

IGNORE MOST OF WHAT'S ABOVE

Just realized how poorly written the old question was. Let's start from the beginning.

Consider the collection "sessions":

[
    {
      status: "WAITING",
      type: "TEXT",
      members: [
        "adam"
      ]
    },
    {
      status: "WAITING",
      type: "TEXT",
      members: [
        "john"
      ]
    },
    {
      status: "WAITING",
      type: "VOICE",
      members: [
        "alex"
      ]
    },
    {
      status: "ENDED",
      type: "VOICE",
      members: [
        "adam",
        "alex"
      ]
    },
    {
      status: "TIMEOUT",
      type: "TEXT",
      members: [
        "adam",
        "TARGET"
      ]
    }
]

I'm making a match-matching system. Let's say "TARGET" wants to match. I'm trying to write a MongoDB aggregation that does the following.

Find all documents with query { type: "TEXT", status: "WAITING" }
Iterate through each document: check if members[0] and TARGET have ever matched before
If members[0] and TARGET have matched before (check entire collection, any type/status), then it will not be included in the final array

It should return the following:

[
    {
      status: "WAITING",
      type: "TEXT",
      members: [
        "john"
      ]
    },
]

Notice how there were 3 "WAITING" rooms in the collection. But TARGET had already matched with adam. And TARGET cannot match with alex, because alex is in a "VOICE" session. So in this case, john would be the only appropriate match.

Please provide your requested result for the sample documents. What is "TARGET_USER_ID" in this case? — nimrod serok
– nimrod serok, Commented Jan 31, 2023 at 6:28
Please check the answer and let me know does it works or not. — Srijon Chakraborty
– Srijon Chakraborty, Commented Jan 31, 2023 at 7:13

nimrod serok · Accepted Answer · 2023-02-01 07:25:08Z

1

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

db.sessions.aggregate([
  {$match: {
      status: "WAITING",
      type: "TEXT",
      "members.0": {$ne: target}
  }},
  {$lookup: {
      from: "sessions",
      let: {member: {$first: "$members"}},
      pipeline: [{$match: {$expr: {$setIsSubset: [["$$member", target], "$members"]}}}],
      as: "found"
  }},
  {$match: {"found.0": {$exists: false}}},
  {$group: {
      _id: 0,
      members: {$push: {$arrayElemAt: ["$members", 0]}},
      status: {$first: "$status"},
      type: {$first: "$type"}
  }}
])

See how it works on the playground example

edited Feb 1, 2023 at 7:25

answered Jan 31, 2023 at 13:25

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

haste Over a year ago

This does not work, unfortunately. If I changed "ID2" to "ID9", then it should have returned all of the waiting rooms, but instead, it returns no response.

haste Over a year ago

I've edited my question a second time. This time it should be much more clear as to what I'm trying to achieve. @nimrodserok

haste Over a year ago

This is perfect, removed the last stage however. I got stuck when I realized I couldn't use use $all within the $lookup $match pipeline, did not know $setIsSubset was a thing! Thank you.

Srijon Chakraborty · Accepted Answer · 2023-01-31 07:44:32Z

0

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-

edited Jan 31, 2023 at 7:44

answered Jan 31, 2023 at 7:10

Srijon Chakraborty

2,1622 gold badges12 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related