MongoDB query find, sum and count

Question

My document of a collection is a block in blockchain with fields {height, gasUsed, timestamp}

I want to get the total gas used (sum of gasUsed) and total count, given a particular day.

Below is my query to get all blocks within 4 Jan 2022

    db.getCollection('blocks').find({
        timestamp: {
            $gte: new Date(2022, 0, 4),
            $lt: new Date(2022, 0, 5)
        }
    }

How could I get the total count and sum of gasUsed as well (within 4 Jan 2022)?

Can you add an example of your collection and expected output? — J.F.
– J.F., Commented Jan 11, 2022 at 15:51

Frankie Hung · Accepted Answer · 2022-01-13 19:28:20Z

4

You can do it with Aggregation framework $match and $group:

db.collection.aggregate([
  {
    "$match": {
      "$and": [
        {
          "timestamp": {
            "$gte": new Date("2022-01-01")
          }
        },
        {
          "timestamp": {
            "$lt": new Date("2022-01-03")
          }
        }
      ]
    }
  },
  {
    "$group": {
      "_id": null,
      "total_count": { "$sum": 1 },
      "total_gas":   { "$sum": "$gasUsed" }
    }
  }
])

Working example

edited Jan 13, 2022 at 19:28

Frankie Hung

6212 gold badges8 silver badges20 bronze badges

answered Jan 11, 2022 at 16:11

NeNaD

20.8k11 gold badges64 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Frankie Hung Over a year ago

The answer is almost perfect except there is one excess } in the last 4th line. Unfortunately, I failed to update it. Please correct it if you can.

Frankie Hung Over a year ago

I learned something useful from your answer. We can use Date("YYYY-MM-DD") to specify the date in a more readable way, instead of Date(YYYY,M-1,D).

NeNaD Over a year ago

I am glad it helped you. Consider also upvoting and accepting my answer if it helped you.

Frankie Hung Over a year ago

Your answer will be accepted after fixing the unbalanced { } issue. Thank you.

NeNaD Over a year ago

What do you mean { } issue?

|

Collectives™ on Stack Overflow

MongoDB query find, sum and count

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related