Merge multiple subdocuments as an array in aggregation projection MongoDB [duplicate]

Question

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

Here is my collection called visitors:

{
  "_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
        }
      ]},
  }
}

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

{
  "_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
     }]
}

I've been trying to do this with this aggregation

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

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

{
   "_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
         } 
      ]
   ]  
}

and I've tried this another projection with unwind,

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)

but it seems to merge the documents with different type at each index

{
  "_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",
     }]
}

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

Update Solved

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

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

$slice can work as skip and limit that i wanted, but it doesn't solve my problem, whereas $setUnion is exactly what i was looking for — fadeltd
– fadeltd, Commented Sep 18, 2016 at 15:17

TomG · Accepted Answer · 2016-09-18 07:31:04Z

1

If you add activity_type field to each of the array elements, it will be easy to use $setUnion with one aggregation stage:

db.visitors.aggregate([
    {
        $project: {
            activities: {
                $setUnion: ['$activities.visits', '$activities.purchases']
            }
        }
    }
])

In this case you will get the arrays "concatenated" together into one activities array with their type.

For the other part of your question, sort, skip, limit, are aggregation stages that will work on each document and not sub-documents, so you will need to $match your needed document, $unwind the result activities array from before, and then you can can use all of the sort, skip, limit operators.

answered Sep 18, 2016 at 7:31

TomG

2,5695 gold badges27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sede Over a year ago

Note that this will filter out duplicates from the result.

TomG Over a year ago

@Styvane visits and purchases both have an _id field, there won't be duplicates. and I also requested him to add activity_type field for both of them

Sede Over a year ago

You are right. I didn't notice that.

fadeltd Over a year ago

@Tom Thanks, I added the activity_type in each of my array, and it works like a charm. $setunion exactly what i've been looking for.

Collectives™ on Stack Overflow

Merge multiple subdocuments as an array in aggregation projection MongoDB [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related