How can I determine the number of unique values in MongoDB?

Question

How can I determine the number of unique values in MongoDB?

I have a compilation of records that contain various feedback for different products. The structure is as follows:

{
  {
    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"
  },
}

My goal is to determine the frequency of each rating for every item.

Therefore, the desired output should be 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 have attempted so far

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 appears correct, but the counts are consistently 0.

I am curious about the appropriate method to total the occurrences based on the distinct values within the same field?

Thank you!

javascript mongodb aggregation-framework

Answer 1

Answer №1

Although you were very close, keep in mind that $eq simply returns a true/false value. To convert this into a numeric value, you can utilize $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 ]
           }
       }
  }}
])

Viewed as a "ternary" operator, $cond evaluates a logical condition as its first argument (if), returning the second argument for true outcomes (then) and the third argument for false outcomes (else). This conversion allows the true or false outputs to be converted into 1s and 0s for use with $sum.

Additionally, it's important to remember that "case" sensitivity matters when using $eq. If case variations are present, consider incorporating $toLower in the expressions:

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

Shifting gears slightly, the following aggregation method is usually more adaptable to different values and significantly outperforms conditional sums:

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"
            }
        }
    }}
])

This approach would yield results similar to the following output:

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

While conveying the same information, this alternative method eliminates the need to explicitly match values and operates far more efficiently.

Answer 2

Although you were very close, keep in mind that $eq simply returns a true/false value. To convert this into a numeric value, you can utilize $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 ]
           }
       }
  }}
])

Viewed as a "ternary" operator, $cond evaluates a logical condition as its first argument (if), returning the second argument for true outcomes (then) and the third argument for false outcomes (else). This conversion allows the true or false outputs to be converted into 1s and 0s for use with $sum.

Additionally, it's important to remember that "case" sensitivity matters when using $eq. If case variations are present, consider incorporating $toLower in the expressions:

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

Shifting gears slightly, the following aggregation method is usually more adaptable to different values and significantly outperforms conditional sums:

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"
            }
        }
    }}
])

This approach would yield results similar to the following output:

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

While conveying the same information, this alternative method eliminates the need to explicitly match values and operates far more efficiently.

How can I determine the number of unique values in MongoDB?

Answer №1

Similar questions

Stop the webpage from refreshing using JavaScript

What are the steps to position this bootstrap drop-down menu above a fixed page header?

Express.js Server Unable to Receive JSON Data from JQuery Ajax Post

guide for interpreting a complex json structure

JavaScript Special Character Removal

The date functionality is malfunctioning on Internet Explorer 8

Which option is more beneficial for intercepting API data in Angular 6: interfaces or classes?

Displaying a division when a button is pressed

What is the process of relocating JSON and JS code from within an HTML file to external files?

pull information from mongodb and connect it to google charts

The database encountered a surprising issue with an illegal token: `db.run

Tick the checkbox to indicate that I want to insert an image in the adjacent column for the same row

"Utilizing the Mongo Aggregation Pipeline for Formatting Arrays: A Step-by-Step Guide

Sinon Stub generates varying values with each invocation

Vue alert: A duplicate key with the value of '10' has been identified. This could potentially lead to an issue with updates

npm encountered an issue: EPERM error - the operation is not permitted and cannot unlink the file

Angular 4: Transform a string into an array containing multiple objects

I am struggling to grasp the concept of how the g flag in JavaScript's string.match(regexp) method operates

"The website seems to be experiencing some technical difficulties on Firefox, but I have switched to

When running the command `npm install <node_module> --save`, the dependencies are not being updated as