Merge multiple subdocuments as an array in aggregation projection MongoDB
<p>What I am trying to do here is querying and merging multiple sub-documents in return as an <strong>array</strong>. I think the aggregation framework is the way to go, but I can't seem to get it exactly right.</p>

<p>Here is my collection called <em>visitors</em>:</p>

<pre><code>{
  "_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
  "username": "Bob",
  "fullname": "Bobby",
  "activities": 
      { "visits" : [
         {
            "_id": ObjectId("57dc674e4208b12fd4a52a3d"),
            "date": ISODate("2016-09-18T08:00:00.000Z"),
            "location" : "Jakarta",
         },
         {
            "_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
            "date": ISODate("2016-09-17T08:00:00.000Z"),
            "location" : "Denpasar",
         }
       ],
        "purchases" : [
        {
          "_id": ObjectId("57dc4769c0f09317282b3f92"),
          "date": ISODate("2016-09-17T07:30:00.000Z"),
          "product_name" : "Shirt",
          "price": 125000
        },{
          "_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
          "date": ISODate("2016-09-18T08:30:00.000Z"),
          "product_name" : "Shoes",
          "price": 150000
        }
      ]},
  }
}
</code></pre>

<p>And here is the <strong>document</strong> that <strong>I want</strong> and try to achieve with aggregation is this, with condition that i can <code>sort</code> them by date, use <code>limit</code> and <code>skip</code>.</p>

<pre><code>{
  "_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
  "activities": [
     {
       "activity_type": "purchases",
       "_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
        "date": ISODate("2016-09-18T08:30:00.000Z"),
       "product_name" : "Shoes",
       "price": 150000
     },{
       "activity_type": "visits",
       "visits_id": ObjectId("57dc674e4208b12fd4a52a3d"),
       "date": ISODate("2016-09-18T08:00:00.000Z"),
       "location" : "Jakarta",
     },{
       "activity_type": "visits",
       "visits_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
       "date": ISODate("2016-09-17T08:00:00.000Z"),
       "location" : "Denpasar",
     },{
       "activity_type": "purchases",
       "date": ISODate("2016-09-17T07:30:00.000Z"),
       "product_name" : "Shirt",
       "price": 125000
     }]
}
</code></pre>

<p>I've been trying to do this with this aggregation</p>

<pre><code>db.visitors.aggregate([{
  $match: { _id: ObjectId("57dc5c2b7463d336ec3fff8c") } },
     {
        $group: {
           _id: "$_id",
          visits: {
             "$addToSet": "$activities.visits"
          },
          purchases: {
             "$addToSet": "$activities.purchases"
          }
        }
     }])
</code></pre>

<p>But I don't get exactly what i want, it was instead just group them by their type, and I can't even use <code>skip</code> and <code>limit</code> (seems to just skip and limit visitors number).</p>

<pre><code>{
   "_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
   "visits": [
      [
         {
            "_id": ObjectId("57dc674e4208b12fd4a52a3d"),
            "date": ISODate("2016-09-18T08:00:00.000Z"),
            "location" : "Jakarta",
         },
         {
            "_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
            "date": ISODate("2016-09-17T08:00:00.000Z"),
            "location" : "Denpasar",
         }
      ]
   ],
   "news": [
      [
         {
           "_id": ObjectId("57dc4769c0f09317282b3f92"),
           "date": ISODate("2016-09-17T07:30:00.000Z"),
           "product_name" : "Shirt",
           "price": 125000
         },{
           "_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
           "date": ISODate("2016-09-18T08:30:00.000Z"),
           "product_name" : "Shoes",
           "price": 150000
         } 
      ]
   ]  
}
</code></pre>

<p>and I've tried this another projection with <code>unwind</code>,</p>

<pre><code>db.visitors.aggregate([
   { $match: { _id: ObjectId("57dc5c2b7463d336ec3fff8c") } },
   { $unwind: '$activities.visits' },
   { $unwind: '$activities.purchases' },
   { $project: {
        _id: 0,
        //visits
        "visits_id": "$activities.visits._id",
        "visits_date": "$activities.visits.date",
        "visits_location" : "$activities.visits.location"
        //purchases
        "purchases_id": "$activities.purchases._id",
        "purchases_date": "$activities.purchases.date",
        "purchases_product_name": "$activities.purchases.product_name",
        "purchases_price": "$activities.purchases.price",
     }
   }
])
  .skip(0)
  .limit(2)
</code></pre>

<p>but it seems to merge the documents with different type at each index</p>

<pre><code>{
  "_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
  "activities": [
     {
       "purchases_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
       "purchases_date": ISODate("2016-09-18T08:30:00.000Z"),
       "purchases_product_name" : "Shoes",
       "purchases_price": 150000
       "visits_id": ObjectId("57dc674e4208b12fd4a52a3d"),
       "visits_date": ISODate("2016-09-18T08:00:00.000Z"),
       "visits_location" : "Jakarta",
     },{
       "purchases_id": ObjectId("57dc4769c0f09317282b3f92"),
       "purchases_date": ISODate("2016-09-17T07:30:00.000Z"),
       "purchases_product_name" : "Shirt",
       "purchases_price": 125000
       "visits_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
       "visits_date": ISODate("2016-09-17T08:00:00.000Z"),
       "visits_location" : "Denpasar",
     }]
}
</code></pre>

<p>Is it possible to do this with my current document? Or should I change my document structure?</p>

<p><strong>Update Solved</strong></p>

<p>I ended up add <code>activity_type</code> at each of my subdocument in array, and solve this using <code>$setUnion</code> to merge multiple array as an array, and use <code>$slice</code> to limit and skip the array. I don't know how, but it seems <code>$setUnion</code> already sorting it automatically</p>

<pre><code>db.visitors.aggregate([
{
    $project: {
        activities: {
            $setUnion: ['$activities.visits', '$activities.purchases'], 
        }
    }
}, 
{
    $project:{
        activites: {
            $slice: ["$activities", 0, 2]
        }
    }
}
])
</code></pre>