Count where value does not match array

Question

I have following offers collection

Here readBy contains the _id of the users... Now I want to count the number of unRead offers for the userId = "5add82620d7f5b38240c63d4"

{
    "_id" : ObjectId("5aeaab5ed6a9c97d0209260a"),
    "expiresIn" : ISODate("2018-05-30T18:30:00.000Z"),
    "name" : "Trip ",
    "readBy" : [ 
        ObjectId("5add82620d7f5b38240c63d4"),
        ObjectId("5add82620d7f5b38240c63c6")
    ],
    "__v" : 0
}
{
    "_id" : ObjectId("5aeaab7dd6a9c97d0209260b"),
    "expiresIn" : ISODate("2018-05-29T18:30:00.000Z"),
    "name" : "Trip",
    "readBy" : [ObjectId("5add82620d7f5b38240c63d4")],
    "__v" : 0
}
{
    "_id" : ObjectId("5aeae233d6a9c97d02092622"),
    "expiresIn" : ISODate("2018-05-25T18:30:00.000Z"),
    "name" : "two way off",
    "readBy" : [],
}
{
    "_id" : ObjectId("5aeae49643f10d284726069c"),
    "expiresIn" : ISODate("2018-05-25T18:30:00.000Z"),
    "name" : "two way off",
    "readBy" : [],
}
{
    "_id" : ObjectId("5aeae49743f10d284726069d"),
    "expiresIn" : ISODate("2018-05-25T18:30:00.000Z"),
    "name" : "two way off",
    "readBy" : []
}
{
    "_id" : ObjectId("5aeae49743f10d284726069e"),
    "expiresIn" : ISODate("2018-05-25T18:30:00.000Z"),
    "name" : "two way off",
    "readBy" : []
}

so for the above collection my output should be

[{
  numberOfUnreadOffers: 4
}]

because four of the collection does not have 5add82620d7f5b38240c63d4 in readBy array

@Héctor with the exception of course that "aggregation" is actually the wrong approach for the most performant solution to this. — Neil Lunn
– Neil Lunn, Commented May 7, 2018 at 9:05

Neil Lunn · Accepted Answer · 2018-05-07 09:00:33Z

2

You basically use $setIsSubset and $cond here:

var userId= "5add82620d7f5b38240c63d4";

Model.aggregate([
  { "$group": {
    "_id": null,
    "numberOfUnreadOffers": {
      "$sum": {
        "$cond": {
          "if": { 
            "$setIsSubset": [[mongoose.Types.ObjectId(userId)], "$readBy"]
          },
          "then": 0,
          "else": 1
        }
      }
    }
  }}
])

Of course you also need to "cast" using mongoose.Types.ObjectId from the "string" to a valid ObjectId value.

But really you get better performance from a simple count() instead:

Model.count({ "readBy": { "$ne": userId } })

So you really should not use .aggregate() for this at all.

answered May 7, 2018 at 9:00

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

2 Comments

Ashh Over a year ago

perfect Neil... where you do test with the queries? I mean there is site like jsFiddle to test mongodb query?

Neil Lunn Over a year ago

@AshishChoudhary Where do "I" test? Usually "nowhere" and this stuff just comes out of my head, which explains why there are usually syntax errors :) If a question correctly shows data and I actually need to think about it for a few moments, then I'll simply copy and paste to insert to a new collection and then run the queries that matter. Always have a MongoDB instance you can trash with rubbish collections and test things I guess. Better than any "fiddle".

Collectives™ on Stack Overflow

Count where value does not match array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related