CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Anu
Anu

Reputation: 3450

How to merge the collections to get the same document schema?

I have two collections with following document structure : comment collection:

{ 
    "_id" : ObjectId("59bab6c6d41dce6422af08cd"), 
    "userId" : 12345.0, 
    "comment" : "Hey, what's up?", 
    "created" : ISODate("2017-09-14T17:05:10.820+0000")
}
{ 
    "_id" : ObjectId("59bab6c6d41dce6422af08ce"), 
    "userId" : 123456.0, 
    "comment" : "Not much", 
    "created" : ISODate("2017-09-14T17:05:10.855+0000")
}
{ 
    "_id" : ObjectId("59bab6c6d41dce6422af08cf"), 
    "userId" : 12345678.0, 
    "comment" : "Cool", 
    "created" : ISODate("2017-09-14T17:05:10.889+0000")
}
{ 
    "_id" : ObjectId("59bab6c6d41dce6422af08d0"), 
    "userId" : 12.0, 
    "comment" : "Nothing", 
    "created" : ISODate("2017-09-14T17:05:10.931+0000")
}

users collection:

{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d1"), 
    "unique_Id" : 12345.0, 
    "firstName" : "Rich", 
    "lastName" : "S", 
    "gender" : "M", 
    "country" : "CA", 
    "age" : "18"
}
{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d2"), 
    "unique_Id" : 123456.0, 
    "firstName" : "Rob", 
    "lastName" : "M", 
    "gender" : "M", 
    "country" : "US", 
    "age" : "25"
}
{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d3"), 
    "unique_Id" : 12345.0, 
    "firstName" : "Sarah", 
    "lastName" : "T", 
    "gender" : "F", 
    "country" : "US", 
    "age" : "13"
}

I tried to join them and need them to follow the same document schema after join. I did 

db.getCollection('users').aggregate([
    {
        $lookup: {
            from: "comments",
            localField: "unique_Id",
            foreignField: "userId",
            as: "goldStandard"
        }

    },
    { $out : "test2"}
])

output:

{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d1"), 
    "unique_Id" : 12345.0, 
    "firstName" : "Rich", 
    "lastName" : "S", 
    "gender" : "M", 
    "country" : "CA", 
    "age" : "18", 
    "goldStandard" : [
        {
            "_id" : ObjectId("59bab6c6d41dce6422af08cd"), 
            "userId" : 12345.0, 
            "comment" : "Hey, what's up?", 
            "created" : ISODate("2017-09-14T17:05:10.820+0000")
        }
    ]
}
{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d2"), 
    "unique_Id" : 123456.0, 
    "firstName" : "Rob", 
    "lastName" : "M", 
    "gender" : "M", 
    "country" : "US", 
    "age" : "25", 
    "goldStandard" : [
        {
            "_id" : ObjectId("59bab6c6d41dce6422af08ce"), 
            "userId" : 123456.0, 
            "comment" : "Not much", 
            "created" : ISODate("2017-09-14T17:05:10.855+0000")
        }
    ]
}
{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d3"), 
    "unique_Id" : 12345.0, 
    "firstName" : "Sarah", 
    "lastName" : "T", 
    "gender" : "F", 
    "country" : "US", 
    "age" : "13", 
    "goldStandard" : [
        {
            "_id" : ObjectId("59bab6c6d41dce6422af08cd"), 
            "userId" : 12345.0, 
            "comment" : "Hey, what's up?", 
            "created" : ISODate("2017-09-14T17:05:10.820+0000")
        }
    ]
}

Now, the "from" collections documents are added as objects under the "as" field name of type array. If I use $unwind to unwind the array, then it is given as an object. I do not want to be as an object, instead, I want the final documents to have the following structure after join: The field with the matching conditions in $lookup & common columns should be combined together to avoid duplicate fields. The new fields will be added to the new documents. For Example:

{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d1"), 
    "unique_Id" : 12345.0, 
    "firstName" : "Rich", 
    "lastName" : "S", 
    "gender" : "M", 
    "country" : "CA", 
    "age" : "18",  
    "comment" : "Hey, what's up?", 
    "created" : ISODate("2017-09-14T17:05:10.820+0000")
}
{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d2"), 
    "unique_Id" : 123456.0, 
    "firstName" : "Rob", 
    "lastName" : "M", 
    "gender" : "M", 
    "country" : "US", 
    "age" : "25", 
    "comment" : "Not much", 
    "created" : ISODate("2017-09-14T17:05:10.855+0000")
}
{ 
    "_id" : ObjectId("59bab74cd41dce6422af08d3"), 
    "unique_Id" : 12345.0, 
    "firstName" : "Sarah", 
    "lastName" : "T", 
    "gender" : "F", 
    "country" : "US", 
    "age" : "13", 
    "comment" : "Hey, what's up?", 
    "created" : ISODate("2017-09-14T17:05:10.820+0000")
}

Please suggest.

Upvotes: 1

Views: 1812

Answers (1)

s7vr
s7vr

Reputation: 75984

You can use $mergeObject operator which will be in upcoming 3.6 release.

$mergeObject to merge the fields with the joined collection fields followed by $replaceRoot to promote the combined doc to top level.

$project with exclusion to drop the goldStandard field and $out to write to new collection.

Something like

db.getCollection('users').aggregate([
  {
    "$lookup": {
      "from": "comments",
      "localField": "unique_Id",
      "foreignField": "userId",
      "as": "goldStandard"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          {
            "$arrayElemAt": [
              "$goldStandard",
              0
            ]
          }
        ]
      }
    }
  },
  {
    "$project": {
      "goldStandard": 0
    }
  },
  {
    "$out": "test2"
  }
])

Upvotes: 3

Related Questions