Merge data from two collections

Question

If I have some data stored in two collections in MongoDB like so:

Collection 1:

opened = [{id: 1, value: 1123}, {id: 2, value: 4231}, {id: 2, value: 3322}...]

Collection 2:

synched = [{id: 1, value: 12343}, {id: 2, value: 12322}, {id: 1, value: 12322}...]

Now I know I can make two calls to the db and get the data from both but I am interested in potentially aggregating the call and joining the data from both collections so that I end up with the following result:

result = {synched: [{id: 1, value: 12343},{id: 1, value: 12322}], opened: [{id: 1, value: 1123}]}

So in my case, currently I am doing a standard query just twice:

db.opened.find({id: 1}, function(err, res){})
db.synched.find({id: 1}, function(err, res){})

Is there a way to combine those?

EDIT:

The original question that I have posted evolved a little. Here's a slight change that I need to deal with that the below answer doesn't support.

What if I have an array of ids that I am matching:

{ $match: { 'id': {$in: [1,2]} }}

So now, the limit call is really messing this up. What I need is all documents from synched and all documents from opened that match any of the ids in the array, but doing a limit returns only first encountered. That's not always the case. There might be multiple documents with the same id property. That property is not _id that is unique.

Any help will be appreciated.

Answer 1

Here I have two collections Users and User Profile,

The below method will match the requested field also combine the two collections and throw the desired output:

const getUserData = async (userId) => {
        const getUserDataByUserId = await userDataModel.aggregate([
            {
                $match: {
                    userId: userId
                }
            }, {
                $lookup: {
                    from: 'user_profiles',
                    localField: 'userId',
                    foreignField: 'userId',
                    as: 'users'
                }
            }, {
                $replaceRoot: {
                    newRoot: {
                        $mergeObjects: [{
                            $arrayElemAt: ["$users", 0]
                        }, "$$ROOT"]
                    }
                }
            }, {
                $project: {
                    fromItems: 0
                }
            }
        ]);

        return getUserDataByUserId;
    }

Answer 2

You can try below aggregation in mongodb 3.4 and above

db.synched.aggregate([
  { "$limit": 1 },
  { "$facet": { 
    "synched": [
      { "$match": { "id": 1  }}
    ],
    "opened": [
      { "$match": { "id": 1  }},
      { "$limit": 1 },
      { "$lookup": {
        "from": "opened",
        "localField": "id",
        "foreignField": "id",
        "as": "opened"
      }},
      { "$unwind": "$opened" },
      { "$replaceRoot": { "newRoot": "$opened" }}
    ]
  }},
  { "$project": { "opened": 1, "synched": 1 }}
])

and with the new $lookup syntax

db.synched.aggregate([
  { "$facet": { 
    "synched": [
      { "$match": { "id"': { "$in": [1,2] }}}
    ],
    "opened": [
      { "$limit": 1 },
      { "$lookup": {
        "from": "opened",
        "pipeline": [
          { "$match": { "id"': { "$in": [1,2] }}}
        ],
        "as": "opened"
      }},
      { "$unwind": "$opened" },
      { "$replaceRoot": { "newRoot": "$opened" }}
    ]
  }},
  { "$project": { "opened": 1, "synched": 1 }}
])