How to Group By and Count values inside multi layered array in mongoDB?-CodePudding

I have very complicated Document of MongoDB

For example: Order Document >>>

  {
     "_id": "62cdbae0421b250009acc329",
     "cartitems": "62cdbaaf74c9c80009f5a4b2",
  },
  {
        "_id": "62d27e192b254600099ae680",
       "cartitems": "62d27d9d91568c0009866d23",
  }

and cart Document >>>


 {
   "_id": "62cdbaaf74c9c80009f5a4b2",
    "cartItems": [
          {
                      "productCode": [
                            "NCK-1"
                        ],
                        "Price": "56",
                    },
                    {
                        "productCode": [
                            "NCK-2"
                        ],
                        "Price": "56",
                    }
                ],

 },
 {
   "_id": "62d27d9d91568c0009866d23",
    "cartItems": [
                    {
                      "productCode": [
                            "NCK-3"
                        ],
                        "Price": "56",
                    },
                    {
                        "productCode": [
                            "NCK-1"
                        ],
                        "Price": "56",
                    }
      ],

 },

I want to join Order Document Order.cartitems with Cart._id and groupby ProductCode and Count Product Code and Sum Price i.e In total the result must look like

NCK-1 112
NCK-2 56 NCK-3 56

I tried the following code >>>

Order.aggregate([
      {
        $lookup: {
          from: Cart.collection.name,
          localField: 'cartitems',
          foreignField: '_id',
          as: 'cartitems',
        },
      },
      { $unwind: '$cartitems' },
      {
        $group: {
          _id: '$cartitems.cartItems.productCode',
          count: { $sum: '$cartitems.cartItems.Price' },
        },
      },
    ]);

I could not find the solution please guide me in solving this.

CodePudding user response：

$lookup
$unwind
$unwind - Deconstruct the cartitems.cartItems array into multiple documents.
$group - Since there is only one item in cartitems.cartItems.productCode, can consider using $first to take the first value of the array. And you need to cast cartitems.cartItems.Price to number type before sum.

db.order.aggregate([
  {
    $lookup: {
      from: "cart",
      localField: "cartitems",
      foreignField: "_id",
      as: "cartitems"      
    }
  },
  {
    $unwind: "$cartitems"
  },
  {
    $unwind: "$cartitems.cartItems"
  },
  {
    $group: {
      _id: {
        $first: "$cartitems.cartItems.productCode"
      },
      count: {
        $sum: {
          $toInt: "$cartitems.cartItems.Price"
        }
      }   
    } 
  }  
])

Sample Mongo Playground