Guest User

Untitled

a guest
Dec 14th, 2017
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.48 KB | None | 0 0
  1. {
  2. "Category" : 1,
  3. "Messages" : ["Msg1", "Msg2"],
  4. "Value" : 1
  5. },
  6. {
  7. "Category" : 1,
  8. "Messages" : [],
  9. "Value" : 10
  10. },
  11. {
  12. "Category" : 1,
  13. "Messages" : ["Msg1", "Msg3"],
  14. "Value" : 100
  15. },
  16. {
  17. "Category" : 2,
  18. "Messages" : ["Msg4"],
  19. "Value" : 1000
  20. },
  21. {
  22. "Category" : 2,
  23. "Messages" : ["Msg5"],
  24. "Value" : 10000
  25. },
  26. {
  27. "Category" : 3,
  28. "Messages" : [],
  29. "Value" : 100000
  30. }
  31.  
  32. {group : {
  33. _id : "$Category",
  34. Value : { $sum : "$Value"},
  35. Messages : {$push : "$Messages"}
  36. }
  37. },
  38. {$unwind : "$Messages"},
  39. {$unwind : "$Messages"},
  40. {$group : {
  41. _id : "$_id",
  42. Value : {$first : "$Value"},
  43. Messages : {$addToSet : "$Messages"}
  44. }
  45. }
  46.  
  47. "result" : [{
  48. "_id" : 1,
  49. "Value" : 111,
  50. "Messages" : ["Msg3", "Msg2", "Msg1"]
  51. },
  52. {
  53. "_id" : 2,
  54. "Value" : 11000,
  55. "Messages" : ["Msg5", "Msg4"]
  56. }
  57. ]
  58.  
  59. {
  60. "_id" : 3,
  61. "Value" : 100000,
  62. "Messages" : []
  63. }
  64.  
  65. > db.messages.find()
  66. { "Category" : 1, "Messages" : [ "Msg1", "Msg2" ], "Value" : 1 }
  67. { "Category" : 1, "Messages" : [ ], "Value" : 10 }
  68. { "Category" : 1, "Messages" : [ "Msg1", "Msg3" ], "Value" : 100 }
  69. { "Category" : 2, "Messages" : [ "Msg4" ], "Value" : 1000 }
  70. { "Category" : 2, "Messages" : [ "Msg5" ], "Value" : 10000 }
  71. { "Category" : 3, "Messages" : [ ], "Value" : 100000 }
  72.  
  73. > var group1 = {
  74. "$group": {
  75. "_id": "$Category",
  76. "Value": {
  77. "$sum": "$Value"
  78. },
  79. "Messages": {
  80. "$push": "$Messages"
  81. }
  82. }
  83. };
  84.  
  85. > var project1 = {
  86. "$project": {
  87. "Value": 1,
  88. "Messages": {
  89. "$cond": [
  90. {
  91. "$eq": [
  92. "$Messages",
  93. [ [ ] ]
  94. ]
  95. },
  96. [ [ null ] ],
  97. "$Messages"
  98. ]
  99. }
  100. }
  101. };
  102.  
  103. > db.messages.aggregate( group1, project1 )
  104. { "_id" : 3, "Value" : 100000, "Messages" : [ [ null ] ] }
  105. { "_id" : 2, "Value" : 11000, "Messages" : [ [ "Msg4" ], [ "Msg5" ] ] }
  106. { "_id" : 1, "Value" : 111, "Messages" : [ [ "Msg1", "Msg2" ], [ ], [ "Msg1", "Msg3" ] ] }
  107.  
  108. > var unwind = {"$unwind":"$Messages"};
  109.  
  110. > var group2 = {
  111. $group: {
  112. "_id": "$_id",
  113. "Value": {
  114. "$first": "$Value"
  115. },
  116. "Messages": {
  117. "$addToSet": "$Messages"
  118. }
  119. }
  120. };
  121.  
  122. > var project2 = {
  123. "$project": {
  124. "Category": "$_id",
  125. "_id": 0,
  126. "Value": 1,
  127. "Messages": {
  128. "$cond": [
  129. {
  130. "$eq": [
  131. "$Messages",
  132. [ null ]
  133. ]
  134. },
  135. [ ],
  136. "$Messages"
  137. ]
  138. }
  139. }
  140. };
  141.  
  142. > db.messages.aggregate(group1, project1, unwind, unwind, group2 ,project2 )
  143. { "Value" : 111, "Messages" : [ "Msg3", "Msg2", "Msg1" ], "Category" : 1 }
  144. { "Value" : 11000, "Messages" : [ "Msg5", "Msg4" ], "Category" : 2 }
  145. { "Value" : 100000, "Messages" : [ ], "Category" : 3 }
  146.  
  147. db.messages.aggregate([
  148. {"$group":{"_id":"$Category","Value":{"$sum":"$Value"},"Messages":{"$push":"$Messages"}}},
  149. {"$unwind":{"path":"$Messages","preserveNullAndEmptyArrays":true}},
  150. {"$unwind":{"path":"$Messages","preserveNullAndEmptyArrays":true}},
  151. {"$group":{"_id":"$_id","Value":{"$first":"$Value"},"Messages":{"$addToSet":"$Messages"}}}
  152. ])
Add Comment
Please, Sign In to add comment