How to aggregate data Month-on-Month or Year-on-Year using aggregate query in MongoDB?

Anuj Panchal 0
Viewed 14

I have objects in my bookings collection. Structure of the object is as follows:

{
    "_id": "asdkbasdkjcbhsajcbh",
    "created_at": 1662712231,
    "total_teus": 4,
    ...
}

I want to write a query which gives me Month-on-Month or Year-on-Year sum of total_teus sold. I tried my hands but failed.

I wrote the following query:

    db.bookings.aggregate([{
        "$match": {
            "created_at": {
                "$gte": 1648771200
            },
            "company_id": {
                "$nin": ["ln_exports1582702220", "marico_limited1588656327", "star_fisheries_pvt_ltd1576862831", "td_infotech1569314187", "Xportify1561638979", "msuyash1572972166", "demo_account1587548110", "production_testing1586152669", "abc_tech_pvt._ltd.1571657756"]
            }
        }
    },
    {
        "$addFields": {
            "created_date_long": {
                "$toLong": "$created_at"
            }
        }
    },
    {
        "$addFields": {
            "created_date": {
                "$toDate": "$created_date_long"
            }
        }
    },
    {
        "$group": {
          "_id": {
            "$dateToString": {
              "date": "$created_date",
              "format": "%Y-%m"
            }
          },
          "teus_exported": {
            "$sum": "$total_teus"
          }  
        }
    }
])

When I ran this query, I got the following result:

{
    "_id": "1970-01",
    "teus_exported": 234
}

I want result to look like this:

[{
    "_id": "1970-01",
    "teus_exported": 234
},
{
    "_id": "1970-02",
    "teus_exported": 654
},
{
    "_id": "1970-03",
    "teus_exported": 623
}]

Can someone throw some light as to where my query is going wrong?

aggregation-framework pymongo mongodb-query mongodb
1 Answers

You need to multiply your created_at by 1000, as it is in seconds, and $dateToString expect milliseconds:

db.collection.aggregate([
  {
    "$match": {
      "created_at": {
        "$gte": 1648771200
      },
      "company_id": {
        "$nin": [
          "ln_exports1582702220",
          "marico_limited1588656327",
          "star_fisheries_pvt_ltd1576862831",
          "td_infotech1569314187",
          "Xportify1561638979",
          "msuyash1572972166",
          "demo_account1587548110",
          "production_testing1586152669",
          "abc_tech_pvt._ltd.1571657756"
        ]
      }
    }
  },
  {$addFields: {
      created_date: {
        $dateToString: {
          date: {$toDate: {$multiply: ["$created_at", 1000]}},
          format: "%Y-%m"
        }
      }
  }},
  {$group: {_id: "$created_date", teus_exported: {$sum: "$total_teus"}}}
])

See how it works on the playground example

nimrod serok0
Related