Mongodb – Efficient way to get data for each date range intervals using single query | MongoDB

aggregategroup bymongodbnosql

I have collections about products and purchases. I need to get statistics for each time interval.
I need following:

Name quantity (date 1 range) quantity (date 2 range)
Name One 500 400
Name Two 251 450
Name Three 444 600

I have following collections:

//Products
{
 name: {
    type: String,
    required: [true, "The prodcut name is required"],
    unique: true,
}
price: {
    type: mongoose.Types.Decimal128,
    default: "0.0",
  },
}

//Orders model scheme
 { 
   product: {
    type: Schema.Types.ObjectId, ref: "Products",
},
price: {
    type: mongoose.Types.Decimal128,
    default: "0.0",
},
quantity: {
    type: Number,
    default: 0,
},
orderDate: {
        type: Date,
        default: Date.now, //new Date(),
    },
  }

And I have the following aggregate function:

    OrdersModel.aggregate(
        [
            { $match: { orderDate: { $gte: new Date( sdt ), $lte: new Date( edt  ) } } },
            {
                $group:
                {
                    _id: "$product_name", //{ name: "$name"},
                    totalQuantity: { $sum: "$quantity" },
                }
            },
            {
                $project: {
                    _id: 1,
                    qty: "$totalQuantity",
                    //...
                }
            }
        ]
    )

This query is working, but only for one time interval. But I need to get results for multiple time intervals.

I'm new to NoSQL, I've tried retrieving data from one or more-time intervals, but I couldn't find out how to achieve this. How can I solve this problem?

I need to get the sales total Quantity of a product at different time intervals.

2010-01-01 to 2012-01-01
2015-01-01 to 2017-01-01
...

Time intervals can be dynamic. There can be a minimum of 1 and a maximum of 4 time intervals.

Best Answer

If you have only 4 intervals then this could be an approach:

OrdersModel.aggregate([
   {
      $facet: {
         interval1: [
            { $match: { orderDate: { $gte: new Date(sdt_1), $lte: new Date(edt_1) } } },
            {
               $group:
                  {
                     _id: "$product_name", //{ name: "$name"},
                     totalQuantity: { $sum: "$quantity" },
                  }
            },
            { $project: { _id: 1, qty: "$totalQuantity", } }
         ],
         interval2: [
            { $match: { orderDate: { $gte: new Date(sdt_2), $lte: new Date(edt_2) } } },
            {
               $group:
                  {
                     _id: "$product_name", //{ name: "$name"},
                     totalQuantity: { $sum: "$quantity" },
                  }
            },
            { $project: { _id: 1, qty: "$totalQuantity", } }
         ],
      }
   }
])

A more dynamic way would be this one:

var facets = {};

facets["interval1"] = [
   { $match: { orderDate: { $gte: new Date(sdt_1), $lte: new Date(edt_1) } } },
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
];

facets["interval2"] = [
   { $match: { orderDate: { $gte: new Date(sdt_2), $lte: new Date(edt_2) } } },
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
];


facets["interval3"] = [];
facets["interval3"].push({ $match: { orderDate: { $gte: new Date(sdt_3), $lte: new Date(edt_3) } } });
facets["interval3"].push(
   {
      $group:
         {
            _id: "$product_name", //{ name: "$name"},
            totalQuantity: { $sum: "$quantity" },
         }
   },
   { $project: { _id: 1, qty: "$totalQuantity", } }
);

OrdersModel.aggregate([
   {
      $facet: facets
   }
])
Related Question