CODEWITHSUNDEEP
mongodbaggregate
Panikos Stavrou
Panikos Stavrou

Reputation: 113

MongoDB Aggregate Count Strange Behavior

this mongo command (1) returns 100

db.collection.aggregate([{$match: {"events._id":{$exists:true}}},{$unwind: "$events"},{$match: {"events._id":{$exists:true}}},{$group: {_id: null, count: {$sum: 1}}}])

this mongo command (2) returns 80

db.collection.aggregate([{$match: {"events.field":{$exists:true}}},{$unwind: "$events"},{$match: {"events.field":{$exists:true}}},{$group: {_id: null, count: {$sum: 1}}}])

this mongo command (2) returns 15

db.collection.aggregate([{$match: {"events.field":{$exists:false}}},{$unwind: "$events"},{$match: {"events.field":{$exists:false}}},{$group: {_id: null, count: {$sum: 1}}}])

in other words, the first command returns the total count of events in my collection, 100. The second command returns those events where 'field' exists, 80. The third command returns those events where 'field' doesn't exist, 15. Shouldn't (1) + (2) = 100? How do I get the 5 missing events?

Upvotes: 1

Views: 379

Answers (1)

Saravana
Saravana

Reputation: 12817

In $unwind pipeline, you need to preserve the empty or null arrays using field preserveNullAndEmptyArrays else that the missing or null arrays be filtered and will not be passed to next pipeline.

In your collection some of the documents don't have events or events.field field

db.collection.aggregate(
    [
        { $unwind:  { path : "$events", preserveNullAndEmptyArrays : true } },
        { $match: {"events.field":{ $exists:false } } },
        { $group: { _id: null, count: { $sum: 1 } } }
    ]
)

sample collection

> db.collection.find()
{ "_id" : ObjectId("5a5b0a3dc2a7348cbc592b9e"), "name" : "1", "events" : { "field" : "1" } }
{ "_id" : ObjectId("5a5b0a3dc2a7348cbc592b9f"), "name" : "2" }
{ "_id" : ObjectId("5a5b0a3dc2a7348cbc592ba0"), "name" : "3", "events" : { "field" : "2" } }
{ "_id" : ObjectId("5a5b0a3dc2a7348cbc592ba1"), "name" : "1", "events" : { "field" : "1" } }
>

total count

> db.collection.count()
4
> 
>

count of events.field exists

> db.collection.aggregate(
    [ 
        { $unwind:  { path : "$events", preserveNullAndEmptyArrays : true } }, 
        { $match: { "events.field": { $exists : true } } }, 
        { $group: { _id: null, count: { $sum: 1 } } } 
    ] 
)
{ "_id" : null, "count" : 3 }
>

count of events.field does not exist

> db.collection.aggregate(
    [ 
        { $unwind:  { path : "$events", preserveNullAndEmptyArrays : true } }, 
        { $match: { "events.field": { $exists : false } } }, 
        { $group: { _id: null, count: { $sum: 1 } } } 
    ] 
)
{ "_id" : null, "count" : 1 }
> 
>

Upvotes: 1

Related Questions