MongoDB:计算每个不同的值有多少? [英] MongoDB: Counting how many of each distinct values there are?

问题描述

I have a collection of documents holding a list of feedbacks for different items. It looks something like this:

{
  {
    item: "item_1"
    rating: "neutral"
    comment: "some comment"
  },
  {
    item: "item_2"
    rating: "good"
    comment: "some comment"
  },
  {
    item: "item_1"
    rating: "good"
    comment: "some comment"
  },
  {
    item: "item_1"
    rating: "bad"
    comment: "some comment"
  },
  {
    item: "item_3"
    rating: "good"
    comment: "some comment"
  },
}

I want a way to find out how many different ratings each item got.

so the output should look something like this:

{
  {
    item: "item_1"
    good: 12
    neutral: 10
    bad: 67
  },
  {
    item: "item_2"
    good: 2
    neutral: 45
    bad: 8
  },
  {
    item: "item_3"
    good: 1
    neutral: 31
    bad: 10
  }

}

This is what I've done

db.collection(collectionName).aggregate(
          [
             {
               $group:
                 {
                   _id: "$item",
                   good_count: {$sum: {$eq: ["$rating",  "Good"]}},
                   neutral_count:{$sum: {$eq: ["$rating",  "Neutral"]}},
                   bad_count:{$sum: {$eq: ["$rating",  "Bad"]}},
                 }
             }
           ]
)

The format of the output looks right, but the counts are always 0.

I'm wondering what's the properway of summing things up by looking at the distinct values of the same field?

Thanks!

解决方案

You were very close, but of course $eq just returns a true/false value, so to make that numeric you need $cond:

db.collection(collectionName).aggregate([
  { "$group" : {
       "_id": "$item",
       "good_count": { 
           "$sum": { 
               "$cond": [ { "$eq": [ "$rating",  "good" ] }, 1, 0] 
           }
       },
       "neutral_count":{
           "$sum": { 
               "$cond": [ { "$eq": [ "$rating", "neutral" ] }, 1, 0 ]
            }
       },
       "bad_count": { 
           "$sum": { 
               "$cond": [ { "$eq": [ "$rating",  "bad" ] }, 1, 0 ]
           }
       }
  }}
])

As a "ternary" operator $cond takes a logical condition as it's first argument (if) and then returns the second argument where the evaluation is true (then) or the third argument where false (else). This makes true/false returns into 1 and 0 to feed to $sum respectively.

Also note that "case" is sensitive for $eq. If you have varing case then you likely want $toLower in the expressions:

               "$cond": [ { "$eq": [ { "$toLower": "$rating" },  "bad" ] }, 1, 0 ]

---

On a slightly different note, the following aggregation is usually more flexible to different possible values and runs rings around the conditional sums in terms of performance:

db.collection(collectionName).aggregate([
    { "$group": {
        "_id": { 
            "item": "$item",
            "rating": { "$toLower": "$rating" }
        },
        "count": { "$sum": 1 }
    }},
    { "$group": {
        "_id": "$_id.item",
        "results": {
            "$push": {
                "rating": "$_id.rating",
                "count": "$count"
            }
        }
    }}
])

That would instead give output like this:

{
    "_id": "item_1"
    "results":[
        { "rating": "good", "count": 12 },
        { "rating": "neutral", "count": 10 }
        { "rating": "bad", "count": 67 }
    ]
}

It's all the same information, but you did not have to explicitly match the values and it does execute much faster this way.

这篇关于MongoDB:计算每个不同的值有多少?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！