How to make a select with grouping in MongoDB

I am new to mongo and I try to obtain a group given by the identity document, the equivalent in sql would have the following form:

SELECT fecIni, idc , cic 
from db.cllAuditoria 
WHERE fecIni BETWEEN '2017-07-01T05:00:00.000Z' AND '2017-07-31T05:00:00.000Z' 
group by idc , cic

please friends, this would help me a lot I have researched from different sources without coming up with the solution, I thank you in advance.

1 answer

  • answered 2018-01-11 19:38 tarasg

    Assuming U have collection like:

    /* 1 */
    {
        "_id" : ObjectId("5a58dafe1aadbcd8ae74572e"),
        "fecIni" : ISODate("2017-07-30T05:00:00.000Z"),
        "idc" : "idc 1",
        "cic" : "cic 1"
    }
    
    /* 2 */
    {
        "_id" : ObjectId("5a58dc3a1aadbcd8ae745758"),
        "fecIni" : ISODate("2017-07-29T05:00:00.000Z"),
        "idc" : "idc 1",
        "cic" : "cic 1"
    }
    
    /* 3 */
    {
        "_id" : ObjectId("5a58dc671aadbcd8ae74575e"),
        "fecIni" : ISODate("2017-07-28T05:00:00.000Z"),
        "idc" : "idc 1",
        "cic" : "cic 2"
    }

    Grouping by idc, cic fields could be performed by query:

    db.cllAuditoria.aggregate([ 
        { 
            $match : { 
                    fecIni: {
                        $gte: ISODate("2017-07-01T05:00:00.000Z"),
                        $lte: ISODate("2017-07-31T05:00:00.000Z")
                    }
            }
        },
        { $group : { _id : { "idc": "$idc", "cic" : "$cic"} } }
    ]);

    Outcome will looks:

    /* 1 */
    {
        "_id" : {
            "idc" : "idc 1",
            "cic" : "cic 2"
        }
    }
    
    /* 2 */
    {
        "_id" : {
            "idc" : "idc 1",
            "cic" : "cic 1"
        }
    }

    If U want to count items in group:

    db.cllAuditoria.aggregate([ 
        { 
            $match : { 
                    fecIni: {
                        $gte: ISODate("2017-07-01T05:00:00.000Z"),
                        $lte: ISODate("2017-07-31T05:00:00.000Z")
                    }
            }
        },
        { $group : { 
            _id : { "idc": "$idc", "cic" : "$cic"},
            count: { $sum: 1 } 
        } }
    ]);    

    Result:

    /* 1 */
    {
        "_id" : {
            "idc" : "idc 1",
            "cic" : "cic 2"
        },
        "count" : 1.0
    }
    
    /* 2 */
    {
        "_id" : {
            "idc" : "idc 1",
            "cic" : "cic 1"
        },
        "count" : 2.0
    }

    Hope, it will helps U. If your data differs, please, provide example of collection and what U want to get as a result.