MongoDb Aggregation: How can I group an array-1 based on another array-2 when given array-1 and array-2?

Question

EDIT: My original question was

MongoDb Aggregation: Can you $unwind an input document variable in the pipline of a $lookup stage?

Consider the code below:

{$lookup: {
    from:"mydoc", 
    let: {"c":"$myArray"}, 
    pipeline: [ 
        {$unwind: "$$c"},
    ]
    as:"myNewDoc"
 }}

How would I unwind c if I wanted to?

/////END OF ORIGINAL QUESTION

-----EDIT-----

From Tom Slabbaert's comment we now know that it is possible to $unwind an input document variable in the pipline of a $lookup stage. But it is not recommended.

What am I trying to achieve?

Consider these collections, poll and castedvote from this answer from a question I had asked.

I am trying to get an output like below:

numberOfVotes: 6,
hasThisUserVoted: true,
numberOfComments: 12,
castedVotesPerChoice:{
    "choiceA": [
        {"_id": ObjectId("..."), "voter": "Juzi", "choice": 0, "pollId": 100 },
        {"_id": ObjectId("..."), "voter": "Juma", "choice": 0, "pollId": 100 },
        {"_id": ObjectId("..."), "voter": "Jane", "choice": 0, "pollId": 100 },
    ],
    "choiceB": [
        {"_id": ObjectId("..."), "voter": "Jamo", "choice": 1, "pollId": 100 },
        {"_id": ObjectId("..."), "voter": "Juju", "choice": 1, "pollId": 100 },
        {"_id": ObjectId("..."), "voter": "Jana", "choice": 1, "pollId": 100 }
    ],
    "choiceC": [ ]
}

my current implementation:

db.poll.aggregate([
    {"$match": {"_id": 100}}, 
    // ...lookup to get comments
    {"$lookup": {
        "from":"castedvotes", 
        "let": {"pollId":"$_id"}, 
        "pipeline":[
            {"$match":
                {"$expr":
                    {"$eq": ["$pollId", "$$pollId"]},
            }},
        ], 
        "as":"votes" // will use this to get number of votes and find out if the authenticated user has voted.
    }},
    {"$unwind":"$choices"},
    {"$lookup": {
        "from":"castedvotes", 
        "let": {"c":"$choices"}, 
        "pipeline":[
            {"$match":
                {"$expr":
                    {"$eq": ["$choice", "$$c.id"]},
            }},
        ], 
        "as":"votesPerChoice"
    }},
])

The issue I have with my current implementation is that it is doing a lookup on the same collection twice I feel like this is unnecessary and it makes the code not dry. With $unwind I know I can un-$unwind as described here.

So my question is how can I get my desired output with one $lookup to the casted vote collection? Since both lookups return the same data.

Or to ask the question differently how can I group an array-1 based on another array-2 in mongodb aggregation when given array-1 and array-2?

This question answers how to group arrays based on another array in mongodb aggregation by structuring the $lookup stage a certain way. It does not answer my question.

Can you show the data you're trying to process and the expected result? — mickl
– mickl, Commented Jul 13, 2020 at 14:47
The inner pipeline is running on the "mydoc" collection so the outer parameters can't really be unwinded like that. you'll have to first $addFields for example c: "$$c" and then you could unwind that however that is not recommeded. if you wanna explain what you're trying to achieve maybe I could help you further. — Tom Slabbaert
– Tom Slabbaert, Commented Jul 13, 2020 at 15:55
@TomSlabbaert I have update the question as requested. Thanks. — YulePale
– YulePale, Commented Jul 14, 2020 at 5:33
Please, edit your question with this structure: Meta, sample data, what you have tried, what do you expect. Your current post is a bit of puzzle, where we need to follow some links and get part of problem, solution, etc.. — Valijon
– Valijon, Commented Jul 14, 2020 at 9:10

Valijon · Accepted Answer · 2020-07-14 12:39:35Z

1

If I've understood the "puzzle" post correctly (Post title and EDIT are different use cases), we can get the desired result with a single $lookup:

db.poll.aggregate([
  {
    "$match": {
      "_id": 100
    }
  },
  {
    "$lookup": {
      "from": "castedvotes",
      "localField": "pollId",
      "foreignField": "choices.id",
      "as": "voters"
    }
  },
  {
    $project: {
      numberOfVotes: {
        $size: "$voters"
      },
      hasThisUserVoted: {
        $in: [
          "$_id",
          "$voters.pollId"
        ]
      },
      /**How to calculate it?*/
      numberOfComments: {
        $multiply: [
          {
            $size: "$voters"
          },
          2
        ]
      },
      castedVotesPerChoice: {
        $arrayToObject: {
          $map: {
            input: "$choices",
            as: "choice",
            in: {
              k: "$$choice.name",
              v: {
                $filter: {
                  input: "$voters",
                  as: "voter",
                  cond: {
                    $eq: [
                      "$$voter.choice",
                      "$$choice.id"
                    ]
                  }
                }
              }
            }
          }
        }
      }
    }
  }
])

MongoPlayground

answered Jul 14, 2020 at 12:39

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

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

2 Comments

YulePale Over a year ago

Yes this is exactly what I was looking for. It works so well. Thank you for taking your time to answer even though my question was not as clear as possible. The numberOfComments part was to show that I needed to avoid using $unwind as there were other $lookups which used the document as it is.

YulePale Over a year ago

Hey @Valijon kindly chek out this other aggregation question

Collectives™ on Stack Overflow

MongoDb Aggregation: How can I group an array-1 based on another array-2 when given array-1 and array-2?

-----EDIT-----

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

-----EDIT-----

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related