CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-framework
Knörpeltäng
Knörpeltäng

Reputation: 463

How to sum distinct values of a field in a MongoDB collection (utilizing mongoose)

Imagine I had a collection called journals containing documents like the following:

{
  "article": "id1",
  "d": 2
},
{
  "article": "id1",
  "d": 2
},
{
  "article": "id1",
  "d": 3
},
{
  "article": "id2",
  "d": 2
},
...

Where d is kind of a switch and article is a reference. Now I want to have a result like the following:

[
  {
    "_id": "id1",
    "total": 3,
    "d2": 2,
    "d3": 1
  },
  {
    "_id": "id2",
    "total": 1,
    "d2": 1,
    "d3": 0
  }
]

I'm using mongoose and have a model called Journal. What I've got so far is…

Journal.aggregate(
  { $project: {
    articleId: 1,
    depth2 : { $gte:['$d', 2] },
    depth3 : { $eq:['$d', 3] }
  }},
  { $group: {
      _id: '$article',
      total: { $sum: 1 },
      d2: { $sum: '$depth2'},
      d3: { $sum: '$depth3'}
    }
  },
  function (err, journal) {
    console.log(journal);
  }
);

which results in:

[
  {
    "_id": "id1",
    "total": 3,
    "d2": 0,
    "d3": 0
  },
  {
    "_id": "id2",
    "total": 1,
    "d2": 0,
    "d3": 0
  }
]

Obviously the error here is that $eq:['$d', 3] is not summed up because that results in a boolean.

So is there a better expression that projects the new depth2 and depth3 fields to 1or 0 instead of true or false?
Or is there a complete and better approach? :)

I'd like to avoid making 3 queries and prepending a matching phase like { $match: { d: 2 } }.

Upvotes: 1

Views: 1295

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312149

You can use $cond to convert a boolean to a numerical value, but you've also got a $gte where it seems an $eq should be and your docs use article while your code uses articleId:

Journal.aggregate([
  { $project: {
    article: 1,
    depth2 : { $cond: [{$eq: ['$d', 2]}, 1, 0] },
    depth3 : { $cond: [{$eq: ['$d', 3]}, 1, 0] }
  }},
  { $group: {
      _id: '$article',
      total: { $sum: 1 },
      d2: { $sum: '$depth2'},
      d3: { $sum: '$depth3'}
    }
  }
]);

Output:

{
    "result" : [ 
        {
            "_id" : "id2",
            "total" : 1,
            "d2" : 1,
            "d3" : 0
        }, 
        {
            "_id" : "id1",
            "total" : 3,
            "d2" : 2,
            "d3" : 1
        }
    ],
    "ok" : 1
}

Upvotes: 2

Related Questions