mongodb get count group by

Question

I have problem with get count by group.

"BOOL_LIST": [
        {
            "BOOK_TITLE": "the lord of the ring",
            "INDEX": 1,
            "READ": {
                "NORMAL": {
                    "SN": "12222ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "booked"
                },
                "bNORMAL": {
                    "SN": "4444454b51ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "yet"
                }
            }
        },....

I want to get output like below.

[{BOOL_TITLE:"the lord of the ring", NORMAL.booked_count : 2, bNORMAL.booked_count :1}, 
 {BOOL_TITLE:"Mr.porter", NORMAL.booked_count : 21, bNORMAL.booked_count :1}, ...]

I have a problem with using $group. How can I do this?

here is.. db.verification_job.aggregate([{"$match":{date matching}}, {"$project":{"BOOK_LIST":1}}, {"$group": {"_id":{"book_title":"$BOOK_LIST.BOOK_TITLE", "normal":"$BOOK_LIST.READ.NORMAL.RESULT", "bnormal":"$BOOK_LIST.READ.bNORMAL.RESULT"}} }, {$group:{_id:"$_id.book_title, count : {problem here}}}]) — Acool5
– Acool5, Commented Oct 19, 2017 at 6:26
$unwind would be useful only if we have BOOL_LIST.READ is an array. Please see my answer if READ is used as array — Clement Amarnath
– Clement Amarnath, Commented Oct 19, 2017 at 7:41

Serkan Arslan · Accepted Answer · 2017-10-20 05:30:28Z

You can use this script.

db.verification_job.aggregate( [
{ $unwind: "$BOOL_LIST" },
{ $group : {_id :  {BOOK_TITLE: "$BOOL_LIST.BOOK_TITLE"} 
       , NORMAL_booked_count : { $sum : { $cond:[  { $eq: ["$BOOL_LIST.READ.NORMAL.RESULT", "booked" ] } ,  1, 0 ]  } }
       , bNORMAL_booked_count : { $sum : { $cond:[ { $eq: ["$BOOL_LIST.READ.bNORMAL.RESULT", "booked" ] } , 1 ,  0 ]  } }
   }}
] )

For more accurate testing, I assumed we have this sample data.

db.verification_job.insert({
"BOOL_LIST": [
        {
            "BOOK_TITLE": "the lord of the ring",
            "INDEX": 1,
            "READ": {
                "NORMAL": {
                    "SN": "12222ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "booked"
                },
                "bNORMAL": {
                    "SN": "4444454b51ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "yet"
                }
            }
        },
        {
            "BOOK_TITLE": "the lord of the ring",
            "INDEX": 2,
            "READ": {
                "NORMAL": {
                    "SN": "12222ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "yet"
                },
                "bNORMAL": {
                    "SN": "4444454b51ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "booked"
                }
            }
        },
        {
            "BOOK_TITLE": "Mr.porter",
            "INDEX": 3,
            "READ": {
                "NORMAL": {
                    "SN": "12222ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "booked"
                },
                "bNORMAL": {
                    "SN": "4444454b51ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "yet"
                }
            }
        }
]
})

And I get this output as result.

{ "_id" : { "BOOK_TITLE" : "Mr.porter" }, "NORMAL_booked_count" : 1, "bNORMAL_booked_count" : 0 }
{ "_id" : { "BOOK_TITLE" : "the lord of the ring" }, "NORMAL_booked_count" : 1, "bNORMAL_booked_count" : 1 }

Clement Amarnath · Accepted Answer · 2017-10-19 07:48:30Z

Modified collection Schema, READ is modified into array of sub documents rather than nested sub documents, but if you cannot do this modification in the schema please use $objectToArray to modify the document into array and then you can proceed with $unwind. Please note that $objectToArray is available only from mongodb version 3.4.4

db.collection.aggregate([
{$unwind:"$BOOL_LIST"}, 
{$unwind:"$BOOL_LIST.READ"},
{$group:{_id:{"book_title":"$BOOL_LIST.BOOK_TITLE", 
              "result_bnormal":"$BOOL_LIST.READ.bNORMAL.RESULT", 
              "result_normal":"$BOOL_LIST.READ.NORMAL.RESULT" }, 
               count:{$sum:1}}
}])

sample document after the modification

{"BOOL_LIST": [
        {
            "BOOK_TITLE": "the lord of the ring",
            "INDEX": 1,
            "READ": [{
                "NORMAL": {
                    "SN": "12222ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "booked"
                }},
                {"bNORMAL": {
                    "SN": "4444454b51ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "yet"
                }}
            ]
        },
    {
            "BOOK_TITLE": "The coblet of fire",
            "INDEX": 2,
            "READ": [
                {"NORMAL": {
                    "SN": "23232ea8e679518021f01a19cc4d95b9483e3",
                    "RESULT": "booked"
                }},
                {"bNORMAL": {
                    "SN": "5555554b51ea8e679518021f01a19cc4d95b9483c3",
                    "RESULT": "yet"
                }}
            ]
        }
]}

After executing our query in the modified collection the result is

{
        "_id" : {
                "book_title" : "the lord of the ring",
                "result_normal" : "booked"
        },
        "count" : 1
}
{
        "_id" : {
                "book_title" : "the lord of the ring",
                "result_bnormal" : "yet"
        },
        "count" : 1
}
{
        "_id" : {
                "book_title" : "The coblet of fire",
                "result_bnormal" : "yet"
        },
        "count" : 1
}
{
        "_id" : {
                "book_title" : "The coblet of fire",
                "result_normal" : "booked"
        },
        "count" : 1
}

Collectives™ on Stack Overflow

mongodb get count group by

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related