mongoDB, mongoose aggregation query with $sum and $count

world! I'm new in mongodb, and I no have idea have I can do this task with aggregation and sum etc..( So, I have this collections: This is stores collection

{
"storeName": "store one",
"_id" : ObjectId("store id 1"), // this is store ID
"shopId" : ObjectId("shopId 1"),
"shopItems" : [
    {
        "_id" : ObjectId("6048a1fa31d779032b16301e"),
        "itemId" : ObjectId("111"), // this is product id from PRODUCTS collection
        "itemCount" : 2
    },
    {
        "_id" : ObjectId("6048a46e31d779032b163043"),
        "itemId" : ObjectId("222"),// this is product id from PRODUCTS collection
        "itemCount" : 0
    }
  ],
},
{
   "storeName": "store two"
   "_id" : ObjectId("storeId 2"), // this is store ID
   "shopId" : ObjectId("shopId 2"),
   "shopItems" : [
    {
        "_id" : ObjectId("6048a1fa31d779032b16301e"),
        "itemId" : ObjectId("222"), // this is product id from PRODUCTS collection
        "itemCount" : 2
    },
    {
        "_id" : ObjectId("6048a46e31d779032b163043"),
        "itemId" : ObjectId("333"),// this is product id from PRODUCTS collection
        "itemCount" : -5
    }
            {
        "_id" : ObjectId("6048a46e31d779032b163043"),
        "itemId" : ObjectId("111"),// this is product id from PRODUCTS collection
        "itemCount" : -7
    }
  ],
}

This is products collection and here is one array with different price, in fact there are many more ~50/50. I have boolean in collection that`s say if the prices for shops is difference (maybe it will be help for task)

// products collection
{
    "_id" : ObjectId("111"), // itemId in STORE collection
    "differentPricesForShops" : false,
    "productName" : "some name here",
    "buyPrice" : 10,
    "sellingPrice" : 100,
    "differentPricesForShops" : false,
    "difPriceArray" : []
},
{
    "_id" : ObjectId("222"), // itemId in STORE collection
    "differentPricesForShops" : false,
    "productName" : "some name here",
    "differentPricesForShops" : true, <- TRUE
    "difPriceArray" : [
        {
            "shopId": ObjectId("shopId1") <- shop
            "buyPrice" : 5,   <- differentPrices
            "sellingPrice" : 50,
        },
        {
            "shopId": ObjectId("shopId 2")
            "buyPrice" : 15,
            "sellingPrice" : 55,
        },
    ]
},
{
    "_id" : ObjectId("333"), // itemId in STORE collection
    "differentPricesForShops" : false,
    "productName" : "some name here",
    "buyPrice" : 5,
    "sellingPrice" : 15,
    "differentPricesForShops" : false,
    "difPriceArray" : []
},

I need to have this output data:

"shopId" : ObjectId("6048a15031d779032b16300f"),

product count : 2,

product in minus: 0,

selfprice: (2-> count in store * 10(buyPrice) ) = 20

products for sale: (2 -> count in store * 100 (selling price) ) = 200

"shopId" : ObjectId("6048a15031d779032b16300f"),

product count : 2,

product in minus: -5(item count in store) + -7(item count in store) = -12

selfprice: (2-> count in store * 15(different price by shop) ) = 30

products for sale: (2 -> count in store * 55 (different price by shop) ) = 110

this example is by 1 product, but I need to have SUM of selfPrices and products for sale. please, check the image I need to display data like this:

data in table img

I hope someone can help me. many many thanks!

Solution

this aggregation query will give you desired output:

pipeline stages:

unwind : this will create separate document for each product at given store
lookup : this will be used to make join and fetch products details
unwind : as lookup result is array will just make it as document/object
addfields: check if diff in price then set appropriate values
addfields: convert matched store $buyPrice & $sellingPrice from above step from object to field
addfields: set selfprice & products_for_sale based on $buyPrice & $sellingPrice from above step

group: extract fields the way we want!

  [{$unwind: {
       path: '$shopItems' 
     }}, {$lookup: {
       from: 'products',
       localField: 'shopItems.itemId',
       foreignField: '_id',
       as: 'shop_products'
     }}, {$unwind: {
       path: '$shop_products'

     }}, {$addFields: {
       buyPrice:{  $cond: { if:{ $anyElementTrue:["$shop_products.difPriceArray"]}, 
       then:{
                  $arrayElemAt:[{$filter: {
                    input: "$shop_products.difPriceArray",
                    as: "item",
                    cond: { $eq: [ "$$item.shopId", '$shopId' ] }
                 }
              },0]}
           , 
       else: '$shop_products.buyPrice'}},
       sellingPrice:{  $cond: { if:{ $anyElementTrue:["$shop_products.difPriceArray"]}, 
       then:{
                  $arrayElemAt:[{$filter: {
                    input: "$shop_products.difPriceArray",
                    as: "item",
                    cond: { $eq: [ "$$item.shopId", '$shopId' ] }
                 }
              },0]}, 


           else: '$shop_products.sellingPrice'}},
     }}, {$addFields: {
       buyPrice: {  $cond: { if:{ $eq: [{$type: '$buyPrice'},"object"] }, then: '$buyPrice.buyPrice' ,else:'$buyPrice'}},
       sellingPrice: {  $cond: { if:{ $eq: [{$type: '$sellingPrice'},"object"] }, then: '$sellingPrice.sellingPrice' ,else:'$sellingPrice'}}
     }}, {$addFields: {
     selfprice: { $multiply: [ {$ifNull:['$shopItems.itemCount',0]}, {$ifNull:[ '$buyPrice',0]}] },
     products_for_sale  :{ $multiply: [ {$ifNull:['$shopItems.itemCount',0]}, {$ifNull:[ '$sellingPrice',0]}] },

     }}, {$group: {
       _id: '$_id',
       selfprice:{$sum: {
                      $cond: { if: { $lt: [ "$selfprice", 0 ] }, then: 0, else: '$selfprice' } 
                    }},
       product_count:{$sum:1},
       shopId:{$first:'$shopId'},
       product_in_minus:{$sum: {
                      $cond: { if: { $lt: [ "$shopItems.itemCount", 0 ] }, then: "$shopItems.itemCount", else: 0 }
                    }} ,
       products_for_sale:{$sum: {
                      $cond: { if: { $lt: [ "$products_for_sale", 0 ] }, then: 0, else: '$products_for_sale' } 
                    }} 
     }}]

Mongo Playground: https://mongoplayground.net/p/P7xYYEQBJ-J