CODEWITHSUNDEEP
mongodb
Dev
Dev

Reputation: 423

mongodb find group by multiple fields

I have a collection example below

ROW Order Type   isdeliver
1     T      W       TRUE
2     T      L       TRUE
3     T      W       TRUE
4     T      L       FALSE
5     C      L       TRUE
6     C      L       TRUE
7     C      W       TRUE
6     C      W       FALSE

Now I want to write a mongodb query this query will return where isdeliver TRUE more than one and have same Order and Type Need exact below output

ROW Order Type   isdeliver
1     T      W       TRUE
3     T      W       TRUE
5     C      L       TRUE
6     C      L       TRUE

Upvotes: 2

Views: 8683

Answers (1)

Saravana
Saravana

Reputation: 12817

Try this aggregation to group by multiple fields, here we are grouping by Order, Type and isdeliver, filtering all having count less than two

db.col.aggregate(
    [
        {$group : {
            _id : {$concat : ["$Order", "$Type", "$isdeliver"]}, 
            count : {$sum : 1},
            data : {$push : "$$ROOT"}
            }
        },
        {$match : {"count" :{ $gt : 1}}},
        {$unwind : "$data"},
        { $replaceRoot: { newRoot: "$data" } }

    ]
)

or

db.col.aggregate(
    [
        {$group : {
            _id : {
                Order : "$Order", Type : "$Type", isdeliver : "$isdeliver"
            }, 
            count : {$sum : 1},
            data : {$push : "$$ROOT"}
            }
        },
        {$match : {"count" :{ $gt : 1}}},
        {$unwind : "$data"},
        { $replaceRoot: { newRoot: "$data" } }

    ]
)

collection

> db.col.find()
{ "_id" : ObjectId("5a73500cd6c0ccee5268a15f"), "ROW" : 1, "Order" : "T", "Type" : "W", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a160"), "ROW" : 2, "Order" : "T", "Type" : "L", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a161"), "ROW" : 3, "Order" : "T", "Type" : "W", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a162"), "ROW" : 4, "Order" : "T", "Type" : "L", "isdeliver" : "FALSE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a163"), "ROW" : 5, "Order" : "C", "Type" : "L", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a164"), "ROW" : 6, "Order" : "C", "Type" : "L", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a165"), "ROW" : 7, "Order" : "C", "Type" : "W", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a166"), "ROW" : 6, "Order" : "C", "Type" : "W", "isdeliver" : "FALSE" }

result

> db.col.aggregate( [ {$group : { _id : {$concat : ["$Order", "$Type", "$isdeliver"]},  count : {$sum : 1}, data : {$push : "$$ROOT"} } }, {$match : {"count" :{ $gt : 1}}}, {$unwind : "$data"}, { $replaceRoot: { newRoot: "$data" } }  ] )
{ "_id" : ObjectId("5a73500cd6c0ccee5268a15f"), "ROW" : 1, "Order" : "T", "Type" : "W", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a161"), "ROW" : 3, "Order" : "T", "Type" : "W", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a163"), "ROW" : 5, "Order" : "C", "Type" : "L", "isdeliver" : "TRUE" }
{ "_id" : ObjectId("5a73500cd6c0ccee5268a164"), "ROW" : 6, "Order" : "C", "Type" : "L", "isdeliver" : "TRUE" }
>

EDIT

to filter by order type after $group

db.col.aggregate(
    [
        {$group : {
            _id : {
                Order : "$Order", Type : "$Type", isdeliver : "$isdeliver"
            }, 
            count : {$sum : 1},
            data : {$push : "$$ROOT"}
            }
        },
        {$match : {$and : [{"count" :{ $gt : 1}}, {"_id.Order" : {$eq : "T"}}]}},
        {$unwind : "$data"},
        { $replaceRoot: { newRoot: "$data" } }
    ]
)

Upvotes: 3

Related Questions