Push elements from arrays into a single array in mongodb

Question

My Document Structure:

{
    "_id" : ObjectId("59edc58af33e9b5988b875fa"),
    "Agent" : {
        "Name" : "NomanAgent",
        "Location" : "Lahore",
        "AgentId" : 66,
        "Reward" : "Thumb Up",
        "Suggestion" : [ 
            "Knowledge", 
            "Professionalisn"
        ]
    }
}

What I want to achieve in this query: I want to find the count of each suggestion given by a customer to every agent, it should look something like,

{
    "AgentName": "Xyz",
    "SuggestionCounts": {
         "Knowledge": 2,
         "Professionalism": 3,
         "Friendliness": 1
     }
}

What I have done so far,

db.getCollection('_survey.response').aggregate([
    {
        $group:{
            _id: "$Agent.Name",
            Suggestions: {$push:"$Agent.Suggestion"}
        }
    }
]);

Output:

/* 1 */
{
    "_id" : "GhazanferAgent",
    "Suggestions" : [ 
        [ 
            "Clarity", 
            "Effort"
        ], 
        [ 
            "Friendliness"
        ]
    ]
}

/* 2 */
{
    "_id" : "NomanAgent",
    "Suggestions" : [ 
        [ 
            "Knowledge", 
            "Professionalisn"
        ]
    ]
}

How I want it to be(As Suggestion in the document is an array and when when i group documents by Agent.Name so it become array of arrays as shown in my output, it want to merge all arrays into single with duplication and then i will find the count of each element in array):

/* 1 */
    {
        "_id" : "GhazanferAgent",
        "SuggestionsCombined" : [ 
            [ 
                "Clarity", 
                "Effort",
                "Friendliness"
            ]
        ]
    }

    /* 2 */
    {
        "_id" : "NomanAgent",
        "SuggestionsCombined" : [ 
            [ 
                "Knowledge", 
                "Professionalisn"
            ]
        ]
    }

Thanks in advance!!

dnickless · Accepted Answer · 2017-10-30 16:03:12Z

1

One way would be like this - the output structure is not identical to what you suggested but probably close enough:

db.getCollection('_survey.response').aggregate([
    {
        $unwind: "$Agent.Suggestion" // flatten "Suggestion" array
    }, {
        $group:{ // group by agent and suggestion
            _id: { "AgentName": "$Agent.Name", "Suggestion": "$Agent.Suggestion" },
            "Count": { $sum: 1} // calculate count of occurrencs
        }
    }, {
        $group:{
            _id: "$_id.AgentName", // group by agent only
            "Suggestions": { $push: { "Suggestion": "$_id.Suggestion", "Count": "$Count" } } // create array of "Suggestion"/"Count" pairs per agent
        }
    }
]);

answered Oct 30, 2017 at 16:03

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Push elements from arrays into a single array in mongodb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related