Working on the subarray after lookup Aggregate

Working with Data
1

Hello all,

I have two collections that are products and 2nd is orders.

What i have done by now is that with aggregation i can get the products that have orders the last fixed days(1 , 5, 10 ,15,30 days).

I did that by grouping all the orders and then looking up on products collection with same sku and returning it.

Although now i need the Opposite.

Here is Example of products:

_id:654646
sku : 18986
image: image_18986
barcode : barcode_18986

Example of orders:

_id:Ar200887
date : 2021-07-19T20:40:35.000+00:00
line_items : [
{ sku:18986,quantity:1},
{ sku:24227,quantity:2}
]

        const aggr = await Product.aggregate ([
        {'$match':{published_at:{$type:"string"}, inventory_quantity:{$gt:0}}},
      { $project:{
          _id:0,
          id:"$_id",
          parent_id:"$parent_id",
          barcode:"$barcode",
          image_link:"$image_link",
          inventory_quantity:"$inventory_quantity",
          published_at:"$published_at",
          sku:"$sku",
          title:"$title",
          updated_at:"$updated_at",
          ContID:"$ContID",
          ShelfID:"$ShelfID",
          LimitPrice:"$LimitPrice",
          price:"$price",
          product_link:"$product_link",          
              }
          },
          {$lookup:{
              from:"orders",              
              localField:"sku",
              foreignField:"line_items.sku",
              as:"orders",
          }}])

This though returns the Product and an Orders array with all the orders the product was in.

I want the orders array to be processed and contain only the sums within the days i want and not all the orders the product was in without any restrictions.

2

I hope you’re using server version 5.0 because you’ll need to add additional pipeline stages inside $lookup.

Something like:

onlyAfter=ISODate("2021-09-03T00:00:00Z");
// now your pipeline but with $lookup like this:
{$lookup: {
          from:"orders",              
          localField:"sku",
          foreignField:"line_items.sku",
          as:"orders",
          pipeline: [
             {$match:{date:{$gt: onlyAfter}}},
             {$group:{_id: null, sum:{$sum:{ /*something that sums up appropriate fields from the orders */ }}}}
          ]
}

Can you clarify exactly what you want the result to be? Are you running this for all products or only ones that have sales in the last N days? When you say you want sum, what is the sum you want? Is it sum of quantities sold or something else? In your sample pipeline you also seem to have a $project stages that doesn’t do much, you might want to remove it (if you want to rename _id to be id you can do it at the end of the pipeline with $set stage.

Asya