Efficient way to get data for each date range intervals using single query | MongoDB 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, &quot;The prodcut name is required&quot;],
    unique: true,
}
price: {
    type: mongoose.Types.Decimal128,
    default: &quot;0.0&quot;,
  },
}

//Orders model scheme
 { 
   product: {
    type: Schema.Types.ObjectId, ref: &quot;Products&quot;,
},
price: {
    type: mongoose.Types.Decimal128,
    default: &quot;0.0&quot;,
},
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: &quot;$product_name&quot;, //{ name: &quot;$name&quot;},
                    totalQuantity: { $sum: &quot;$quantity&quot; },
                }
            },
            {
                $project: {
                    _id: 1,
                    qty: &quot;$totalQuantity&quot;,
                    //...
                }
            }
        ]
    )

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.
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: &quot;$product_name&quot;, //{ name: &quot;$name&quot;},
                     totalQuantity: { $sum: &quot;$quantity&quot; },
                  }
            },
            { $project: { _id: 1, qty: &quot;$totalQuantity&quot;, } }
         ],
         interval2: [
            { $match: { orderDate: { $gte: new Date(sdt_2), $lte: new Date(edt_2) } } },
            {
               $group:
                  {
                     _id: &quot;$product_name&quot;, //{ name: &quot;$name&quot;},
                     totalQuantity: { $sum: &quot;$quantity&quot; },
                  }
            },
            { $project: { _id: 1, qty: &quot;$totalQuantity&quot;, } }
         ],
      }
   }
])

A more dynamic way would be this one:
var facets = {};

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

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


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

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