Querying the sum in MongoDB returns empty results

Question

Querying the sum in MongoDB returns empty results

Here is an example of a collection of activity rows:

{
  "_id" : ObjectId("5ec90b5258a37c002509b27d"), 
  "user_hash" : "asdsc4be9fe7xxx", 
  "type" : "Expense", 
  "name" : "Lorem", 
  "amount" : 10000, 
  "date_created" : 1590233938 
}

To find the total sum of the activities with this aggregate code:

db.activities.aggregate(
  [
    {
      $group: 
        {
          _id: "$id", 
          total: { $sum: "$amount" } 
        } 
    }, 
    { 
      $match: { type: "Expense", "user_hash": "asdsc4be9fe7xxx" } 
    } 
  ] 
)

The expected result should be: {_id: null, total: xxxxx }

What could be causing the actual result to differ? Any solutions for this issue? Thank you in advance.

javascript mongodb

Answer 1

Answer №1

There are two issues with your query:

You are performing the sum aggregation on each individual document instead of doing it on the entire collection because you have specified _id: "$id", when you should be using _id: null.
The match stage is being executed in the aggregating process after the group stage, but it needs to be done before. This is important because after grouping, the result will look something like this:

{
  "_id": null,
  "total": 15
}

This object does not contain any fields from the original documents, so no results will match. The sequence of stages is crucial as each stage operates based on the output of the previous one (with some exceptions where MongoDB optimizes the pipeline).

Therefore, the correct query should be:

db.activities.aggregate(
  [
    { 
      $match: { type: "Expense", "user_hash": "asdsc4be9fe7xxx" } 
    },
    {
      $group: 
        {
          _id: null, 
          total: { $sum: "$amount" } 
        } 
    },
  ] 
)

Answer 2

There are two issues with your query:

You are performing the sum aggregation on each individual document instead of doing it on the entire collection because you have specified _id: "$id", when you should be using _id: null.
The match stage is being executed in the aggregating process after the group stage, but it needs to be done before. This is important because after grouping, the result will look something like this:

{
  "_id": null,
  "total": 15
}

This object does not contain any fields from the original documents, so no results will match. The sequence of stages is crucial as each stage operates based on the output of the previous one (with some exceptions where MongoDB optimizes the pipeline).

Therefore, the correct query should be:

db.activities.aggregate(
  [
    { 
      $match: { type: "Expense", "user_hash": "asdsc4be9fe7xxx" } 
    },
    {
      $group: 
        {
          _id: null, 
          total: { $sum: "$amount" } 
        } 
    },
  ] 
)

Querying the sum in MongoDB returns empty results

Answer №1

Similar questions

Simple Method to Retrieve JSON Data from a Data Attribute Using .attr() (or Similar Function)

Perform a mongoDB query that filters out the data logged in the last 24 hours and groups it by ID along with a count

What is the procedure for generating a mouse event for clicking a tab in Selenium WebDriver?

Scrolling automatically to the first empty mandatory field with the help of AngularJS

What are the best practices for establishing a secure SignalR client connection?

Achieving a transparent inner box-shadow effect on hover: a step-by-step guide

The Organization of Event Handling in ThreeJs

The D3.js click event seems to be unresponsive

Tips for creating a div that slides from the right side to the left side

The issue with Mongoose not fully storing the document

Tips for designing a unique Facebook like button

One effective way to transfer state to a child component using function-based React

The significance of namespaces in Vue.js event handling

objects bound to knockout binding effects

Exploring the World of Repeating Elements in Print

Easy ways to manipulate the style of array components in Vue.js

Failure to trigger custom event on FB pixel

Learn how to retrieve and showcase MongoDB data in Node.js by incorporating URL parameters

What is the best method for extracting a property from an embedded document within an array in MongoDB?

Using the value of "1" for Schema.Types.ObjectId, a model was created in Mongoose