0

I have seen kind of a similar question, but it has no answer so it doesn't answer my question.

How to group arrays based on another array in mongodb aggregation?

Assume you have these schemas:

PollSchema

const PollSchema = new mongoose.Schema({
    numberOfChoices: {
        type: Number,
        required: true,
        min: [1, "Number of choices must be at least 1"],
        max: [10, "Number of choices cannot be more than 10"],
    },
    arrayOfNamesOfChoices:[{
        name: {
            type: String,
            required: true,
        },
    }],
});

CastedVotedSchema

const CastedVoteSchema = new mongoose.Schema({
    voter: {
        type: String,
        required: true,
    },
    choice: {
        type: Number,
        required: true,
        min:0,
        max:9
    },
});

How can I, in mongodb aggregation, sort the castedVotes in arrays based on their choice. For example consider the data sample below:

Poll = {
    numberOfChoices: 3
    arrayOfNamesOfChoices: [
        {name:'choiceA'},{name:'choiceB'},{name:'choiceC'}
    ]
}

CastedVotes = [
    {voter: 'Juzi', choice: 0},{voter: 'Juma', choice: 0},{voter: 'Jane', choice: 0},
    {voter: 'Jamo', choice: 1},{voter: 'Juju', choice: 1},{voter: 'Jana', choice: 1},
]

NOTE: There are no votes casted in choiceC

I am trying to create an aggregation query that returns an array based on each choice. For example (see below).

//below is the result I desire to have 
{ 
    totalVotes:[
        {voter: 'Juzi', choice: 0},{voter: 'Juma', choice: 0},{voter: 'Jane', choice: 0},
        {voter: 'Jamo', choice: 1},{voter: 'Juju', choice: 1},{voter: 'Jana', choice: 1},
    ], 
    choiceA:[{voter: 'Juzi', choice: 0},{voter: 'Juma', choice: 0},{voter: 'Jane', choice: 0}],
    choiceB:[{voter: 'Jamo', choice: 1},{voter: 'Juju', choice: 1},{voter: 'Jana', choice: 1}],
    choiceC:[],// the choiceC array should be present and empty
}

How to group the CastedVote array based Poll.arrayOfNamesOfChoices array in mongodb aggregation?

Improve this question
4
  • Have you tried using $group, $lookup, $facet, and/or $arrayToObject Commented Mar 18, 2020 at 17:10
  • 1
    You should modify the schema, try adding an ID for array of choices, i.e. {name:"choiceA", id:0} so that you perform lookup easier. Commented Mar 18, 2020 at 23:42
  • @Joe I have tried, but I do not know how to group the CastedVote array based Poll.arrayOfNamesOfChoices array in mongodb aggregation. If you do kindly help by answering the question. Commented Mar 19, 2020 at 4:51
  • . @Wan Bachtiar I have. The problem is I do not know how to structure the lookup aggregation, can you please show me (in an answer)? Commented Mar 19, 2020 at 4:52

1 Answer 1

Reset to default
1

How to group the CastedVote array based Poll.arrayOfNamesOfChoices array in mongodb aggregation?

If you have a collection called poll, containing a document as below 

{ "_id": 100,
  "choices": [
    { "name": "choiceA", "id": 0 },
    { "name": "choiceB", "id": 1 },
    { "name": "choiceC", "id": 2 } ]
}

And another collection called castedvote, containing documents as below: 

{"voter": "Juzi", "choice": 0, "pollId": ObjectId("...")}
{"voter": "Juma", "choice": 0, "pollId": ObjectId("...")}
{"voter": "Jane", "choice": 0, "pollId": ObjectId("...")}
{"voter": "Jamo", "choice": 1, "pollId": ObjectId("...")}
{"voter": "Juju", "choice": 1, "pollId": ObjectId("...")}
{"voter": "Jana", "choice": 1, "pollId": ObjectId("...")}

You can perform aggregation below: 

db.poll.aggregate([
    {"$match": {"_id": 100}}, 
    {"$unwind":"$choices"},
    {"$lookup": {
        "from":"castedvotes", 
        "let": {"c":"$choices"}, 
        "pipeline":[
            {"$match":
                {"$expr":
                    {"$eq": ["$choice", "$$c.id"]},
            }},
        ], 
        "as":"voters"
    }},
    {"$addFields":{
        "x": {
            "$arrayToObject": [ [{"k": "$choices.name", "v": "$voters"}] ]
        }
    }}, 
    {"$replaceRoot": {"newRoot": "$x"}}, 
]);

Using $arrayToObject to create a dynamic field name from the field values. The output would be: 

{  "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": [ ] }
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks a lot. I had not thought of using the poll to do the aggregation. I was using castedVote instead.
Hi @Wan Bachtiar kindly check out this other related question. Thank you.
Hi @Wan Bachtiar kindly check this other related question. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.