Mongodb Group by count Based on Array fields

Question

I am having following data. I want to group by fields in array.

{
    "_id" : ObjectId("54e73137758646882f445383"),
    "items" : [ 
    {
        "name" : "one",
        "quality" : "high"
    }, 
    {
        "name" : "one",
        "quality" : "low"
    }, 
    {
        "name" : "two",
        "quality" : "high"
    }
    ]
}

/* 1 */
{
    "_id" : ObjectId("54e73166758646882f445384"),
    "items" : [ 
    {
        "name" : "two",
        "quality" : "high"
    }, 
    {
        "name" : "three",
        "quality" : "low"
    }, 
    {
        "name" : "four",
        "quality" : "medium"
    }
    ]
}

I want to group by name and quality and I want to get count.

This is what I have tried

db.testColl.aggregate( {$group : { 
            _id : {itemsName : "$items.name" ,itemsQuality : "$items.quality" },  
          count : {$sum : 1} 
    }})

and I am getting result like

{
    "result" : [ 
    {
        "_id" : {
            "itemsName" : [ 
                "two", 
                "three", 
                "four"
            ],
            "itemsQuality" : [ 
                "high", 
                "low", 
                "medium"
            ]
        },
        "count" : 1
    }, 
    {
        "_id" : {
            "itemsName" : [ 
                "one", 
                "one", 
                "two"
            ],
            "itemsQuality" : [ 
                "high", 
                "low", 
                "medium"
            ]
        },
        "count" : 1
    }
    ],
    "ok" : 1
}

Expected Result:

{
"result" : [ 
    {
        "_id" : {
            "itemsName" : one,
            "itemsQuality" : high
        },
        "count" : 1
    },
       {
        "_id" : {
            "itemsName" : one,
            "itemsQuality" : low
        },
        "count" : 1
    },
    {
        "_id" : {
            "itemsName" : two,
            "itemsQuality" : high
        },
        "count" : 2
    },

    ....
    ],
    "ok" : 1
}

Is it possible? If so help me in doing that? If it is not possible , please correct my structure to achieve the result.

Neo-coder · Accepted Answer · 2015-02-20 13:28:51Z

2

Try this will solve your problem

db.collectionName.aggregate({
    "$unwind": "$items"
}, {
    "$group": {
    "_id": {
        "itemsName": "$items.name",
        "itemsQuality": "$items.quality"
    },
    "count": {
        "$sum": 1
    }
    }
})

answered Feb 20, 2015 at 13:28

Neo-coder

7,8585 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Mongodb Group by count Based on Array fields

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related