Group By year-month

[petridis_panagiotis]

Hello guys. I have implemented a simple booking system. What i want is to group Bookings for a user by year and by month(based on date_started) , calculate the total cost for every month, and also carry some additional properties like resource id in order to display them in my browser. Have anyone some ideas how to implement that? Below is my collection schema iam using .Thank you in advance

var BookingSchema= new Schema(
    {
        userID: {type: Schema.Types.ObjectId , ref:'Users'},
        date_finished:{type: Date},
        date_started: {type:Date},
        Project_title: {type:String},
        Project_desc: {type: String},
        total_cost: {type :Number},
        resourceID: {type: Schema.Types.ObjectId,ref:'Resources'},
        timestamp: {type:Date}

    }
);

[Prasad_Saya]

Hello @petridis_panagiotis, You can use an aggregation query (db.collection.aggregate()) to get the desired result. The aggregation stage $group allows you group by user+year+month, and calculate (or sum) the total cost (for the month).

See $group aggregation example. You can use the aggregation operator $first to capture other fields like resource id from within the group stage.

[petridis_panagiotis]

Hello i read what $first is but what i understood this carries other fields only for the first doc. To be more precise i will give my favourite result.
Year 2021
January
[{Booking id:124,resource id total_cost},{Booking id:453,rrsource id total_cost} , month cost:40]
Feb the same
March same
…
2022 the same

[Prasad_Saya]

@petridis_panagiotis, please include a sample input document from your collection.

[petridis_panagiotis]

Ok, let’s go. Below i give a sample dataset

[
  {
    _id: 607188c53f598d0015d362a7,
    resourceID: { _id: 606f58733d3c8b2f50f78a79, name: 'Sillicon Waffer Tool' },
    date_started: 2021-06-21T11:00:00.000Z,
    date_finished: 2021-06-23T11:30:00.000Z,
    total_cost: 4.800000000000001
  },
  {
    _id: 607188913f598d0015d362a5,
    resourceID: {
      _id: 606f5b2e3d3c8b2f50f78a7b,
      name: 'Photolithography Laboratory'
    },
    date_started: 2021-06-10T11:00:00.000Z,
    date_finished: 2021-06-11T11:30:00.000Z,
    total_cost: 12
  },
  {
    _id: 607188313f598d0015d362a3,
    resourceID: { _id: 606f5a423d3c8b2f50f78a7a, name: 'Nanophotonics Classroom' },
    date_started: 2021-05-10T11:00:00.000Z,
    date_finished: 2021-05-11T11:00:00.000Z,
    total_cost: 9.600000000000001
  },
  {
    _id: 606f7d03c297a70015be79da,
    resourceID: { _id: 606f58733d3c8b2f50f78a79, name: 'Sillicon Waffer Tool' },
    date_started: 2021-04-19T01:00:00.000Z,
    date_finished: 2021-04-21T01:00:00.000Z,
    total_cost: 4.800000000000001
  }
]

Now, i will show you what at the end would like to have in my browser. So this page implemented with some silly array if-statements,hence the way to structurize like that my data was very struggling. As you can see from the image, in the link bar we take the user id from the parameter and query for this user. Then for one year( my dataset has at this time bookings only in 2021) we display bookings for every month and calculate the monthly cost (like June in my image). So, finally, is there any query that will free me up from complex and multiple arrays and array loop iterations? .If my desired result is imposibble using one mongodb command and maybe a few javacript iterations, i will again do that using nested arrays

[Prasad_Saya]

You can use this aggregate to start with and refine it as per your formatting:

db.collection.aggregate([
{ 
    $match: { _id: INPUT_USER_ID } 
},
{ 
    $group: {
        _id: { year: { $year: "$date_started" }, month: { $month: "$date_started" } },
        total_cost_month: { $sum: "$total_cost" }
    }
}
])

A sample output for a year+month would be:

{ 
  "_id" : { "year" : 2021, "month" : 6 }, 
  "total_cost_month" : 16.8 
}

[petridis_panagiotis]

Thank you very much. In addition i would like to display some properties for every booking of each month, like in my image. You can see under the Month name there are the details of their respective bookings, is that possible?

[Prasad_Saya]

@petridis_panagiotis

You can see under the Month name there are the details of their respective bookings, is that possible?

Yes, that is possible - try this one:

db.test.aggregate([
{ 
    $match: { _id: INPUT_USER_ID } 
},
{ 
    $group: {
        _id: { 
            year: { $year: "$date_started" }, 
            month: { $month: "$date_started" } 
        },
        total_cost_month: { $sum: "$total_cost" },
        bookings_month: { 
            $push: { 
                date_started: "$date_started",
                date_finished: "$date_finished",
                total_cost: "$total_cost" 
            } 
        }
    }
}
])

[petridis_panagiotis]

Thank you very much, iam looking forward to try and i will respond to you asap
Thank you!!!

[petridis_panagiotis]

So we have results… It works perfect!. Thank you very much Mister Prasad Saya iam very thankfull to you and iam to your disposal whenever you want .