Constructing a framework through an aggregation query to categorize by two identifiers

Question

Constructing a framework through an aggregation query to categorize by two identifiers

I possess a variety of documents similar to the example displayed below as 3 distinct objects.

{
comment:{ 
      text_sentiment: "positive",
      topic: "A"
   }
}, // DOC-1

{
comment:{ 
      text_sentiment: "negative",
      topic: "A"
}}, // DOC-2

{
comment:{ 
      text_sentiment: "positive",
      topic: "B"
}},..//DOC-3 ..

My goal is to construct an aggregation that produces outcomes in the format outlined below:

{
   topic: "A",
   topicOccurance: 2,
   sentiment: {
      positive: 3,
      negative: 2,
      neutral: 0
   }

},...

I have devised an aggregation that successfully groups data by topic and text_sentiment, but I am uncertain about how to generate a structure resembling the one specified above. The aggregation I have created is presented below.

   db.MyCollection.aggregate({
       $match: {
           _id: "xyz",
           "comment.topic": {$exists: 1},
       }
   },{
       $group: {
           _id: {
               topic: "$comment.topic",
               text_sentiment: "$comment.text_sentiment"
               
           },
           total: {$sum: 1},
           
       }
   },{
       $project: {
           topic: {
               name: "$_id.topic",
               occurence: "$total"
           },
           sentiment: "$_id.text_sentiment"
       }
   },{
       $sort: {"topic.occurence": -1}
   })

Although it successfully divides data by topic and sentiment, the resulting structure does not match the desired format. How can I achieve a structure similar to the required one?

javascript database mongodb aggregation-framework aggregate-functions

Answer 1

Answer №1

Response 1

To achieve the desired outcome, it is necessary to have 2 $group stages.

$match
$group - Group by comment.topic and comment.text_sentiment, then use $sum.
$group - Group by _id.topic with $sum; include text_sentiment and total from the previous stage in text_sentiments using $push.
$project - Customize output documents by setting sentiment through converting the array of text_sentiments into key-value pairs with $arrayToObject.
$sort

db.collection.aggregate([
  {
    $match: {
      _id: "xyz",
      "comment.topic": {
        $exists: 1
      },
      
    }
  },
  {
    $group: {
      _id: {
        topic: "$comment.topic",
        text_sentiment: "$comment.text_sentiment"
      },
      total: {
        $sum: 1
      },
      
    }
  },
  {
    $group: {
      _id: "$_id.topic",
      total: {
        $sum: 1
      },
      text_sentiments: {
        $push: {
          k: "$_id.text_sentiment",
          v: "$total"
        }
      }
    }
  },
  {
    $project: {
      topic: "$_id",
      topicOccurance: "$total",
      sentiment: {
        "$arrayToObject": "$text_sentiments"
      }
    }
  },
  {
    $sort: {
      "topicOccurance": -1
    }
  }
])

Example Mongo Playground (Response 1)

Response 2

If the values for text_sentiment are constant, the following query can be utilized:

db.collection.aggregate([
  {
    $match: {
      _id: "xyz",
      "comment.topic": {
        $exists: 1
      },
      
    }
  },
  {
    $group: {
      _id: "$comment.topic",
      total: {
        $sum: 1
      },
      text_sentiments: {
        $push: "$comment.text_sentiment"
      }
    }
  },
  {
    $project: {
      topic: "$_id",
      topicOccurance: "$total",
      sentiment: {
        "positive": {
          $reduce: {
            input: "$text_sentiments",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                {
                  "$cond": {
                    "if": {
                      $eq: [
                        "$$this",
                        "positive"
                      ]
                    },
                    "then": 1,
                    "else": 0
                  }
                }
              ]
            }
          }
        },
        "negative": {
          $reduce: {
            input: "$text_sentiments",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                {
                  "$cond": {
                    "if": {
                      $eq: [
                        "$$this",
                        "negative"
                      ]
                    },
                    "then": 1,
                    "else": 0
                  }
                }
              ]
            }
          }
        },
        "neutral": {
          $reduce: {
            input: "$text_sentiments",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                {
                  "$cond": {
                    "if": {
                      $eq: [
                        "$$this",
                        "neutral"
                      ]
                    },
                    "then": 1,
                    "else": 0
                  }
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $sort: {
      "topicOccurance": -1
    }
  }
])

Drawback: Modification of the query is required when adding/removing a value for text_sentiment.

Example Mongo Playground (Response 2)

Response 3

Another approach similar to Response 2 involves utilizing $size and $filter instead of $reduce.

db.collection.aggregate([
  {
    $match: {
      //_id: "xyz",
      "comment.topic": {
        $exists: 1
      }, 
    }
  },
  {
    $group: {
      _id: "$comment.topic",
      total: {
        $sum: 1
      },
      text_sentiments: {
        $push: "$comment.text_sentiment"
      }
    }
  },
  {
    $project: {
      topic: "$_id",
      topicOccurance: "$total",
      sentiment: {
        "positive": {
          $size: {
            $filter: {
              input: "$text_sentiments",
              cond: {
                $eq: [
                  "$$this",
                  "positive"
                ]
              }
            }
          }
        },
        "negative": {
          $size: {
            $filter: {
              input: "$text_sentiments",
              cond: {
                $eq: [
                  "$$this",
                  "negative"
                ]
              }
            }
          }
        },
        "neutral": {
          $size: {
            $filter: {
              input: "$text_sentiments",
              cond: {
                $eq: [
                  "$$this",
                  "neutral"
                ]
              }
            }
          }
        }  
      }
    }
  },
  {
    $sort: {
      "topicOccurance": -1
    }
  }
])

Example Mongo Playground (Response 3)

Answer 2

Response 1

To achieve the desired outcome, it is necessary to have 2 $group stages.

$match
$group - Group by comment.topic and comment.text_sentiment, then use $sum.
$group - Group by _id.topic with $sum; include text_sentiment and total from the previous stage in text_sentiments using $push.
$project - Customize output documents by setting sentiment through converting the array of text_sentiments into key-value pairs with $arrayToObject.
$sort

db.collection.aggregate([
  {
    $match: {
      _id: "xyz",
      "comment.topic": {
        $exists: 1
      },
      
    }
  },
  {
    $group: {
      _id: {
        topic: "$comment.topic",
        text_sentiment: "$comment.text_sentiment"
      },
      total: {
        $sum: 1
      },
      
    }
  },
  {
    $group: {
      _id: "$_id.topic",
      total: {
        $sum: 1
      },
      text_sentiments: {
        $push: {
          k: "$_id.text_sentiment",
          v: "$total"
        }
      }
    }
  },
  {
    $project: {
      topic: "$_id",
      topicOccurance: "$total",
      sentiment: {
        "$arrayToObject": "$text_sentiments"
      }
    }
  },
  {
    $sort: {
      "topicOccurance": -1
    }
  }
])

Example Mongo Playground (Response 1)

Response 2

If the values for text_sentiment are constant, the following query can be utilized:

db.collection.aggregate([
  {
    $match: {
      _id: "xyz",
      "comment.topic": {
        $exists: 1
      },
      
    }
  },
  {
    $group: {
      _id: "$comment.topic",
      total: {
        $sum: 1
      },
      text_sentiments: {
        $push: "$comment.text_sentiment"
      }
    }
  },
  {
    $project: {
      topic: "$_id",
      topicOccurance: "$total",
      sentiment: {
        "positive": {
          $reduce: {
            input: "$text_sentiments",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                {
                  "$cond": {
                    "if": {
                      $eq: [
                        "$$this",
                        "positive"
                      ]
                    },
                    "then": 1,
                    "else": 0
                  }
                }
              ]
            }
          }
        },
        "negative": {
          $reduce: {
            input: "$text_sentiments",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                {
                  "$cond": {
                    "if": {
                      $eq: [
                        "$$this",
                        "negative"
                      ]
                    },
                    "then": 1,
                    "else": 0
                  }
                }
              ]
            }
          }
        },
        "neutral": {
          $reduce: {
            input: "$text_sentiments",
            initialValue: 0,
            in: {
              $sum: [
                "$$value",
                {
                  "$cond": {
                    "if": {
                      $eq: [
                        "$$this",
                        "neutral"
                      ]
                    },
                    "then": 1,
                    "else": 0
                  }
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $sort: {
      "topicOccurance": -1
    }
  }
])

Drawback: Modification of the query is required when adding/removing a value for text_sentiment.

Example Mongo Playground (Response 2)

Response 3

Another approach similar to Response 2 involves utilizing $size and $filter instead of $reduce.

db.collection.aggregate([
  {
    $match: {
      //_id: "xyz",
      "comment.topic": {
        $exists: 1
      }, 
    }
  },
  {
    $group: {
      _id: "$comment.topic",
      total: {
        $sum: 1
      },
      text_sentiments: {
        $push: "$comment.text_sentiment"
      }
    }
  },
  {
    $project: {
      topic: "$_id",
      topicOccurance: "$total",
      sentiment: {
        "positive": {
          $size: {
            $filter: {
              input: "$text_sentiments",
              cond: {
                $eq: [
                  "$$this",
                  "positive"
                ]
              }
            }
          }
        },
        "negative": {
          $size: {
            $filter: {
              input: "$text_sentiments",
              cond: {
                $eq: [
                  "$$this",
                  "negative"
                ]
              }
            }
          }
        },
        "neutral": {
          $size: {
            $filter: {
              input: "$text_sentiments",
              cond: {
                $eq: [
                  "$$this",
                  "neutral"
                ]
              }
            }
          }
        }  
      }
    }
  },
  {
    $sort: {
      "topicOccurance": -1
    }
  }
])

Example Mongo Playground (Response 3)

Constructing a framework through an aggregation query to categorize by two identifiers

Answer №1

Similar questions

Exploring the process of defining methods within a child Vue component

Subscribe on Footer triggers automatic scrolling to the bottom of the page

What is the method to set precise values on a range slider?

Looking for non-case-sensitive letters in a text input field

Storing JSON data in LocalStorage or within the App on Ionic 2

EasyWaySaveArchive in ninja training - mastering the art of retrieving components

What is the most effective way to access content from a webpage that is rendered

The database did not respond, causing the API to resolve without sending a response for the specified endpoint (/api/dates). This could potentially lead to requests becoming stalled in Next

Mongoose is unable to update arrays, so it will simply create a new array

Is there a specific jest matcher available for comparing Array<Objects>?

Ways to fix the loading error in AngularJS version 1.3.5?

how to bind data to an array in Angular

Using Slick Slider and Bootstrap CSS to display text on top of images

Receiving an undefined error response when attempting to submit a form using JQuery ajax for file retrieval

AngularJS Controller managing the entry field

What is the optimal strategy for Database Design?

Elegant Bootstrap 4 Carousel featuring a glimpse of the upcoming slide alongside the primary carousel item

What is the best way to update a value in Angular's HTML binding?

Rendering JSON Data in JavaScript using Table Pagination

Direct your attention to the final item in a visible array within a ReactJS component