1

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.

0

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
   }
])

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.