MongoDB Aggregation in nested collection data

In a collection I'm saving all the Store wise transactions and there is a key named "items" inside all the documents which contains all the items of a particular sale.
Now I've to perform aggregation onto it and send the data somewhere else.
Below I've given the collection data

    [{
"_id": {
"$oid": "63ee26bcb64550cbb20de158"
},
"employee": "1",
"custId": "6357cb852c61064d53040ff2",
"store_code": "BAN-01",
"store_no": "7",
"shift": "Second",
"employee_role": "POS Store Manager",
"store_name": "175",
"customer_name": "Vivek Kumar",
"customer_email": "vivek.kumar98705@gmail.com",
"customer_phone": "9958137221",
"counter_code": "175-Counter-01",
"deposit_amount": "100",
"emp_code": "TCP004",
"items": "[{\"internalid\":2969,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"201450\",\"price\":\"252.50\",\"qty\":\"1\",\"itemamount\":\"265.13\",\"mrp\":\"252.50\",\"discount\":\"0.00\",\"tax\":\"12.63\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: BETADINE GARGLE 100ML\",\"item_upc_code\":\"5285239186\",\"item_hsn_code\":\"30041090\",\"uom\":\"EA\",\"batchDetails\":{\"#PJ0672\":{\"batch_expiry\":\"4/4/2025\",\"qty\":1}}},{\"internalid\":2963,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"225124\",\"price\":\"7.00\",\"qty\":\"15\",\"itemamount\":\"110.25\",\"mrp\":\"7.00\",\"discount\":\"0.00\",\"tax\":\"5.25\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: ZINCOVIT 15TAB\",\"item_upc_code\":\"8035476959\",\"item_hsn_code\":\"21069099\",\"uom\":\"SH(15)\",\"batchDetails\":{\"#ZVT21203\":{\"batch_expiry\":\"4/4/2025\",\"qty\":15}}},{\"internalid\":3067,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"315043\",\"price\":\"1.70\",\"qty\":\"45\",\"itemamount\":\"80.33\",\"mrp\":\"1.70\",\"discount\":\"0.00\",\"tax\":\"3.83\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: FOLVITE 45TAB\",\"item_upc_code\":\"9312066071\",\"item_hsn_code\":\"30045039\",\"uom\":\"SH(45)\",\"batchDetails\":{\"#GH3735\":{\"batch_expiry\":\"4/4/2025\",\"qty\":45}}},{\"internalid\":3069,\"type\":\"InvtPart\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"204009\",\"price\":\"75.44\",\"qty\":\"10\",\"itemamount\":\"792.12\",\"mrp\":\"75.44\",\"discount\":\"0.00\",\"tax\":\"37.72\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: DUPHASTON 10TAB\",\"item_upc_code\":\"3444014334\",\"item_hsn_code\":\"30043919\",\"uom\":\"SH(10)\",\"batchDetails\":{\"#KAVA2050\":{\"batch_expiry\":\"4/4/2025\",\"qty\":10}}}]",
"total_amount": 1247.83,
"total_tax": 59.43,
"total_discount": 0,
"subTotal": 1188.4,
"payment_details": [
[
{
"paymentMethod": "cash",
"amount_paid": 1247.83,
"transaction_date": "2/16/2023 4:58:19 pm",
"payment_status": "Success",
"transaction_id": "test123"
}
]
],
"auth_id": "master_ad14cca679b44ef1d78a3e_master",
"created_by": "master_ad14cca679b44ef1d78a3e_master",
"updated_by": "master_ad14cca679b44ef1d78a3e_master",
"invoice_no": "WFINV#1",
"updated_at": {
"$date": {
"$numberLong": "1676551868017"
}
},
"created_at": {
"$date": {
"$numberLong": "1676551868017"
}
}
},
{
"_id": {
"$oid": "63ee2778b64550cbb20de17c"
},
"employee": "1",
"custId": "6357cb852c61064d53040ff2",
"store_code": "BAN-01",
"store_no": "7",
"shift": "Second",
"employee_role": "POS Store Manager",
"store_name": "175",
"customer_name": "Vivek Kumar",
"customer_email": "vivek.kumar98705@gmail.com",
"customer_phone": "9958137221",
"counter_code": "175-Counter-01",
"deposit_amount": "100",
"emp_code": "TCP004",
"items": "[{\"internalid\":\"undefined\",\"type\":\"undefined\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"\",\"price\":\"2.30\",\"qty\":\"20\",\"itemamount\":\"48.30\",\"mrp\":\"2.30\",\"discount\":\"0.00\",\"tax\":\"2.3\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: COMBIFLAM 20TAB\",\"item_upc_code\":\"UPC000004\",\"item_hsn_code\":\"30049063\",\"uom\":\"SH(20)\",\"batchDetails\":{\"#1022563\":{\"batch_expiry\":\"4/4/2025\",\"qty\":20}}},{\"internalid\":\"undefined\",\"type\":\"undefined\",\"islotitem\":true,\"isserialitem\":false,\"isspecialorderitem\":false,\"itemName\":\"\",\"price\":\"93.00\",\"qty\":\"1\",\"itemamount\":\"97.65\",\"mrp\":\"93.00\",\"discount\":\"0.00\",\"tax\":\"4.65\",\"promotion\":\"NA\",\"item_display_name\":\"UOM: FEBREX-PLUS DS SYP 60ML\",\"item_upc_code\":\"1036121752\",\"item_hsn_code\":\"30049093\",\"uom\":\"EA\",\"batchDetails\":{\"#22430041\":{\"batch_expiry\":\"4/4/2025\",\"qty\":1}}}]",
"total_amount": 145.95,
"total_tax": 6.95,
"total_discount": 0,
"subTotal": 139,
"payment_details": [
[
{
"paymentMethod": "cash",
"amount_paid": 145.95,
"transaction_date": "2/16/2023 4:54:25 pm",
"payment_status": "Success",
"transaction_id": "test123"
}
]
],
"auth_id": "master_ad14cca679b44ef1d78a3e_master",
"created_by": "master_ad14cca679b44ef1d78a3e_master",
"updated_by": "master_ad14cca679b44ef1d78a3e_master",
"invoice_no": "WFINV#4",
"updated_at": {
"$date": {
"$numberLong": "1676552056047"
}
},
"created_at": {
"$date": {
"$numberLong": "1676552056047"
}
}
}
]

I Tried the below aggregation but it didn't work.

$data = \DB::collection("online_orders")->raw(function($collection) use ($requestData) {
return $collection->aggregate([
[
'$match' => [
'created_at' => [
'$gte' => $requestData['from'], 
'$lte' => $requestData['to']
]
],
'$group' => [
'_id' => [
'store_code' => '$store_code',
'sku' => '$items.itemName'
],
'items' => '$items',
'sold_qty' => [ '$sum' => '$items.qty' ]
]
]
]);
});

After Pipeline

> Expected Result :

Result Keys relation to the collection keys just for reference**

sku -> itemName
sold_qty -> how many time that particular item has been sold store code wise 
return_qty -> curerntly leave it as 0
total_amount -> total amount of particular item sold 
uom -> uom
batchNo -> batchDetails key is batchNo
Quantity -> batchDetails -- qty
{
"order_type": "CS",
"data": [
{
"store_code": "175",
"cash_sale_details": [
{
"sku": "321405",
"sold_qty": "5",
"return_qty": "0",
"total_amount": "69.83",
"uom": "EA",
"batchDetails": [
{
"batchNo": "LOT121",
"Quantity": 5
}
]
},
{
"sku": "223327",
"sold_qty": "5",
"return_qty": "0",
"total_amount": "488.25",
"uom": "EA",
"batchDetails": [
{
"batchNo": "22430041",
"Quantity": 5
}
]
},                            
{
"sku": "222045",
"sold_qty": "5",
"return_qty": "0",
"total_amount": "842.89",
"uom": "EA",
"batchDetails": [
{
"batchNo": "B2329D164",
"Quantity": 5
}
]
},
{
"sku": "341106",
"sold_qty": "5",
"return_qty": "0",
"total_amount": "623.44",
"uom": "EA",
"batchDetails": [
{
"batchNo": "AS2211",
"Quantity": 5
}
]
}
]
}
]
}

Thanks

I changed the collection as given below

[
{
"_id": {
"$oid": "63f5d5f66229de80d201113b"
},
"employee": "9",
"custId": "6357cb852c61064d53040ff2",
"store_code": "BAN-01",
"store_no": "7",
"shift": "General",
"employee_role": "Sales Person",
"store_name": "175",
"customer_name": "RahulTest raja",
"customer_email": "hdsudashudasuduha@gamail.com",
"customer_phone": "8 (787) 878-7878",
"counter_code": "175-Counter-03",
"deposit_amount": "0",
"emp_code": "TCP004",
"order_type": "INV",
"custterms": "NET 15",
"status": "Pending Billing",
"items": [
{
"internalid": "undefined",
"type": "undefined",
"islotitem": false,
"isserialitem": false,
"isspecialorderitem": false,
"itemName": "321405",
"price": "14.00",
"qty": "11",
"itemamount": "161.70",
"mrp": "14.00",
"discount": "0.00",
"tax": "7.70",
"promotion": "NA",
"item_display_name": "UOM: JANUMET 50MG/500MG 15TAB",
"item_upc_code": "9346943598",
"item_hsn_code": "30021500",
"uom": "EA",
"batchDetails": {
"#LOT121": {
"batch_expiry": "null",
"qty": 5
},
"#LOT122": {
"batch_expiry": "null",
"qty": 6
}
}
}
],
"total_amount": 161.7,
"total_tax": 7.7,
"total_discount": 0,
"subTotal": 154,
"payment_details": [
[
{
"type": "terms",
"amount": 0,
"termsId": "NET 15"
}
]
],
"auth_id": "master_ad14cca679b44ef1d78a3e_master",
"created_by": "master_ad14cca679b44ef1d78a3e_master",
"updated_by": "master_ad14cca679b44ef1d78a3e_master",
"invoice_no": "WFINV#1",
"updated_at": {
"$date": {
"$numberLong": "1677055478717"
}
},
"created_at": {
"$date": {
"$numberLong": "1677055478717"
}
}
}
]

then Written the below aggregation which did the job

$data = Order::raw(function ($collection) use ($requestData) {
return $collection->aggregate([
[
'$match' => [
'created_at' => [
'$gte' => $requestData['from'],
'$lte' => $requestData['to']
],
'order_type' => $requestData['order_type']
],
],
[
'$unwind' => '$items'
],
[
'$group' => [
'_id' => [
'store_code' => '$store_code',
'sku' => '$items.itemName',
'order_type' => '$order_type',                                
],
'sold_qty' => [ '$first' => '$items.qty'],
// 'return_qty' => [0],
'total_amount' => ['$first' => '$items.itemamount' ],
'uom' => ['$first' => '$items.uom' ],
'batchDetails' => [ '$first' => [ '$objectToArray' => '$items.batchDetails'] ],
// 'items' => [ '$first' => '$items' ],
]
],
]);
});