MongoDB中的嵌套集合数据聚合

huangapple go评论75阅读模式
英文:

MongoDB Aggregation in nested collection data

问题

以下是翻译的内容:

在一个集合中,我正在保存所有门店的交易记录,并且每个文档中都有一个名为"items"的键,其中包含了特定销售的所有商品。
现在我需要对其执行聚合操作并将数据发送到其他地方。
以下是提供的集合数据:

[{
"_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

英文:

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

  1. [{
  2. "_id": {
  3. "$oid": "63ee26bcb64550cbb20de158"
  4. },
  5. "employee": "1",
  6. "custId": "6357cb852c61064d53040ff2",
  7. "store_code": "BAN-01",
  8. "store_no": "7",
  9. "shift": "Second",
  10. "employee_role": "POS Store Manager",
  11. "store_name": "175",
  12. "customer_name": "Vivek Kumar",
  13. "customer_email": "vivek.kumar98705@gmail.com",
  14. "customer_phone": "9958137221",
  15. "counter_code": "175-Counter-01",
  16. "deposit_amount": "100",
  17. "emp_code": "TCP004",
  18. "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}}}]",
  19. "total_amount": 1247.83,
  20. "total_tax": 59.43,
  21. "total_discount": 0,
  22. "subTotal": 1188.4,
  23. "payment_details": [
  24. [
  25. {
  26. "paymentMethod": "cash",
  27. "amount_paid": 1247.83,
  28. "transaction_date": "2/16/2023 4:58:19 pm",
  29. "payment_status": "Success",
  30. "transaction_id": "test123"
  31. }
  32. ]
  33. ],
  34. "auth_id": "master_ad14cca679b44ef1d78a3e_master",
  35. "created_by": "master_ad14cca679b44ef1d78a3e_master",
  36. "updated_by": "master_ad14cca679b44ef1d78a3e_master",
  37. "invoice_no": "WFINV#1",
  38. "updated_at": {
  39. "$date": {
  40. "$numberLong": "1676551868017"
  41. }
  42. },
  43. "created_at": {
  44. "$date": {
  45. "$numberLong": "1676551868017"
  46. }
  47. }
  48. },
  49. {
  50. "_id": {
  51. "$oid": "63ee2778b64550cbb20de17c"
  52. },
  53. "employee": "1",
  54. "custId": "6357cb852c61064d53040ff2",
  55. "store_code": "BAN-01",
  56. "store_no": "7",
  57. "shift": "Second",
  58. "employee_role": "POS Store Manager",
  59. "store_name": "175",
  60. "customer_name": "Vivek Kumar",
  61. "customer_email": "vivek.kumar98705@gmail.com",
  62. "customer_phone": "9958137221",
  63. "counter_code": "175-Counter-01",
  64. "deposit_amount": "100",
  65. "emp_code": "TCP004",
  66. "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}}}]",
  67. "total_amount": 145.95,
  68. "total_tax": 6.95,
  69. "total_discount": 0,
  70. "subTotal": 139,
  71. "payment_details": [
  72. [
  73. {
  74. "paymentMethod": "cash",
  75. "amount_paid": 145.95,
  76. "transaction_date": "2/16/2023 4:54:25 pm",
  77. "payment_status": "Success",
  78. "transaction_id": "test123"
  79. }
  80. ]
  81. ],
  82. "auth_id": "master_ad14cca679b44ef1d78a3e_master",
  83. "created_by": "master_ad14cca679b44ef1d78a3e_master",
  84. "updated_by": "master_ad14cca679b44ef1d78a3e_master",
  85. "invoice_no": "WFINV#4",
  86. "updated_at": {
  87. "$date": {
  88. "$numberLong": "1676552056047"
  89. }
  90. },
  91. "created_at": {
  92. "$date": {
  93. "$numberLong": "1676552056047"
  94. }
  95. }
  96. }
  97. ]

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

  1. $data = \DB::collection("online_orders")->raw(function($collection) use ($requestData) {
  2. return $collection->aggregate([
  3. [
  4. '$match' => [
  5. 'created_at' => [
  6. '$gte' => $requestData['from'],
  7. '$lte' => $requestData['to']
  8. ]
  9. ],
  10. '$group' => [
  11. '_id' => [
  12. 'store_code' => '$store_code',
  13. 'sku' => '$items.itemName'
  14. ],
  15. 'items' => '$items',
  16. 'sold_qty' => [ '$sum' => '$items.qty' ]
  17. ]
  18. ]
  19. ]);
  20. });

After Pipeline

> Expected Result :

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

  1. sku -> itemName
  2. sold_qty -> how many time that particular item has been sold store code wise
  3. return_qty -> curerntly leave it as 0
  4. total_amount -> total amount of particular item sold
  5. uom -> uom
  6. batchNo -> batchDetails key is batchNo
  7. Quantity -> batchDetails -- qty
  8. {
  9. "order_type": "CS",
  10. "data": [
  11. {
  12. "store_code": "175",
  13. "cash_sale_details": [
  14. {
  15. "sku": "321405",
  16. "sold_qty": "5",
  17. "return_qty": "0",
  18. "total_amount": "69.83",
  19. "uom": "EA",
  20. "batchDetails": [
  21. {
  22. "batchNo": "LOT121",
  23. "Quantity": 5
  24. }
  25. ]
  26. },
  27. {
  28. "sku": "223327",
  29. "sold_qty": "5",
  30. "return_qty": "0",
  31. "total_amount": "488.25",
  32. "uom": "EA",
  33. "batchDetails": [
  34. {
  35. "batchNo": "22430041",
  36. "Quantity": 5
  37. }
  38. ]
  39. },
  40. {
  41. "sku": "222045",
  42. "sold_qty": "5",
  43. "return_qty": "0",
  44. "total_amount": "842.89",
  45. "uom": "EA",
  46. "batchDetails": [
  47. {
  48. "batchNo": "B2329D164",
  49. "Quantity": 5
  50. }
  51. ]
  52. },
  53. {
  54. "sku": "341106",
  55. "sold_qty": "5",
  56. "return_qty": "0",
  57. "total_amount": "623.44",
  58. "uom": "EA",
  59. "batchDetails": [
  60. {
  61. "batchNo": "AS2211",
  62. "Quantity": 5
  63. }
  64. ]
  65. }
  66. ]
  67. }
  68. ]
  69. }

Thanks

答案1

得分: 0

我已更改集合如下所示:

  1. [
  2. {
  3. "_id": {
  4. "$oid": "63f5d5f66229de80d201113b"
  5. },
  6. "employee": "9",
  7. "custId": "6357cb852c61064d53040ff2",
  8. "store_code": "BAN-01",
  9. "store_no": "7",
  10. "shift": "General",
  11. "employee_role": "Sales Person",
  12. "store_name": "175",
  13. "customer_name": "RahulTest raja",
  14. "customer_email": "hdsudashudasuduha@gamail.com",
  15. "customer_phone": "8 (787) 878-7878",
  16. "counter_code": "175-Counter-03",
  17. "deposit_amount": "0",
  18. "emp_code": "TCP004",
  19. "order_type": "INV",
  20. "custterms": "NET 15",
  21. "status": "Pending Billing",
  22. "items": [
  23. {
  24. "internalid": "undefined",
  25. "type": "undefined",
  26. "islotitem": false,
  27. "isserialitem": false,
  28. "isspecialorderitem": false,
  29. "itemName": "321405",
  30. "price": "14.00",
  31. "qty": "11",
  32. "itemamount": "161.70",
  33. "mrp": "14.00",
  34. "discount": "0.00",
  35. "tax": "7.70",
  36. "promotion": "NA",
  37. "item_display_name": "UOM: JANUMET 50MG/500MG 15TAB",
  38. "item_upc_code": "9346943598",
  39. "item_hsn_code": "30021500",
  40. "uom": "EA",
  41. "batchDetails": {
  42. "#LOT121": {
  43. "batch_expiry": "null",
  44. "qty": 5
  45. },
  46. "#LOT122": {
  47. "batch_expiry": "null",
  48. "qty": 6
  49. }
  50. }
  51. }
  52. ],
  53. "total_amount": 161.7,
  54. "total_tax": 7.7,
  55. "total_discount": 0,
  56. "subTotal": 154,
  57. "payment_details": [
  58. [
  59. {
  60. "type": "terms",
  61. "amount": 0,
  62. "termsId": "NET 15"
  63. }
  64. ]
  65. ],
  66. "auth_id": "master_ad14cca679b44ef1d78a3e_master",
  67. "created_by": "master_ad14cca679b44ef1d78a3e_master",
  68. "updated_by": "master_ad14cca679b44ef1d78a3e_master",
  69. "invoice_no": "WFINV#1",
  70. "updated_at": {
  71. "$date": {
  72. "$numberLong": "1677055478717"
  73. }
  74. },
  75. "created_at": {
  76. "$date": {
  77. "$numberLong": "1677055478717"
  78. }
  79. }
  80. }
  81. ]

然后,我编写了以下聚合操作来执行相应任务:

  1. $data = Order::raw(function ($collection) use ($requestData) {
  2. return $collection->aggregate([
  3. [
  4. '$match' => [
  5. 'created_at' => [
  6. '$gte' => $requestData['from'],
  7. '$lte' => $requestData['to']
  8. ],
  9. 'order_type' => $requestData['order_type']
  10. ],
  11. ],
  12. [
  13. '$unwind' => '$items'
  14. ],
  15. [
  16. '$group' => [
  17. '_id' => [
  18. 'store_code' => '$store_code',
  19. 'sku' => '$items.itemName',
  20. 'order_type' => '$order_type',
  21. ],
  22. 'sold_qty' => ['$first' => '$items.qty'],
  23. 'total_amount' => ['$first' => '$items.itemamount'],
  24. 'uom' => ['$first' => '$items.uom'],
  25. 'batchDetails' => ['$first' => ['$objectToArray' => '$items.batchDetails']],
  26. ]
  27. ],
  28. ]);
  29. });

请注意,这是一段PHP代码,用于MongoDB数据库的聚合操作。

英文:

I changed the collection as given below

  1. [
  2. {
  3. "_id": {
  4. "$oid": "63f5d5f66229de80d201113b"
  5. },
  6. "employee": "9",
  7. "custId": "6357cb852c61064d53040ff2",
  8. "store_code": "BAN-01",
  9. "store_no": "7",
  10. "shift": "General",
  11. "employee_role": "Sales Person",
  12. "store_name": "175",
  13. "customer_name": "RahulTest raja",
  14. "customer_email": "hdsudashudasuduha@gamail.com",
  15. "customer_phone": "8 (787) 878-7878",
  16. "counter_code": "175-Counter-03",
  17. "deposit_amount": "0",
  18. "emp_code": "TCP004",
  19. "order_type": "INV",
  20. "custterms": "NET 15",
  21. "status": "Pending Billing",
  22. "items": [
  23. {
  24. "internalid": "undefined",
  25. "type": "undefined",
  26. "islotitem": false,
  27. "isserialitem": false,
  28. "isspecialorderitem": false,
  29. "itemName": "321405",
  30. "price": "14.00",
  31. "qty": "11",
  32. "itemamount": "161.70",
  33. "mrp": "14.00",
  34. "discount": "0.00",
  35. "tax": "7.70",
  36. "promotion": "NA",
  37. "item_display_name": "UOM: JANUMET 50MG/500MG 15TAB",
  38. "item_upc_code": "9346943598",
  39. "item_hsn_code": "30021500",
  40. "uom": "EA",
  41. "batchDetails": {
  42. "#LOT121": {
  43. "batch_expiry": "null",
  44. "qty": 5
  45. },
  46. "#LOT122": {
  47. "batch_expiry": "null",
  48. "qty": 6
  49. }
  50. }
  51. }
  52. ],
  53. "total_amount": 161.7,
  54. "total_tax": 7.7,
  55. "total_discount": 0,
  56. "subTotal": 154,
  57. "payment_details": [
  58. [
  59. {
  60. "type": "terms",
  61. "amount": 0,
  62. "termsId": "NET 15"
  63. }
  64. ]
  65. ],
  66. "auth_id": "master_ad14cca679b44ef1d78a3e_master",
  67. "created_by": "master_ad14cca679b44ef1d78a3e_master",
  68. "updated_by": "master_ad14cca679b44ef1d78a3e_master",
  69. "invoice_no": "WFINV#1",
  70. "updated_at": {
  71. "$date": {
  72. "$numberLong": "1677055478717"
  73. }
  74. },
  75. "created_at": {
  76. "$date": {
  77. "$numberLong": "1677055478717"
  78. }
  79. }
  80. }
  81. ]

then Written the below aggregation which did the job

  1. $data = Order::raw(function ($collection) use ($requestData) {
  2. return $collection->aggregate([
  3. [
  4. '$match' => [
  5. 'created_at' => [
  6. '$gte' => $requestData['from'],
  7. '$lte' => $requestData['to']
  8. ],
  9. 'order_type' => $requestData['order_type']
  10. ],
  11. ],
  12. [
  13. '$unwind' => '$items'
  14. ],
  15. [
  16. '$group' => [
  17. '_id' => [
  18. 'store_code' => '$store_code',
  19. 'sku' => '$items.itemName',
  20. 'order_type' => '$order_type',
  21. ],
  22. 'sold_qty' => [ '$first' => '$items.qty'],
  23. // 'return_qty' => [0],
  24. 'total_amount' => ['$first' => '$items.itemamount' ],
  25. 'uom' => ['$first' => '$items.uom' ],
  26. 'batchDetails' => [ '$first' => [ '$objectToArray' => '$items.batchDetails'] ],
  27. // 'items' => [ '$first' => '$items' ],
  28. ]
  29. ],
  30. ]);
  31. });

huangapple
  • 本文由 发表于 2023年2月19日 23:53:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/75501375.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定