Joining MongoDB collections after fetching them separately

Question

We have a one-to-many relationship stored as two separate collections in MongoDB. The relationship is: 1 Team has many People. We are intentionally saving these as separate collections because we want to be able to query them separately. We decided on this design after doing bunch of research - I hope it makes sense. Here are the simplified Mongoose schemas:

var TeamSchema = new Mongoose.Schema({
    name: String
});

var PersonSchema = new Mongoose.Schema({
    name: String
    teamId: {
        type: Mongoose.Schema.ObjectId,
        ref: 'Team',
    }
});

I would like to join the two collections as follows before sending them to the front-end:

[
     { "name": "John Doe",   "Team": "Orange" },
     { "name": "Jane Smith", "Team": "Orange" },
     { "name": "Doug White", "Team": "Blue"   }
]

I don't mind doing two separate queries to get each collection into memory. The question is what's the most efficient way to join the collections once I have them in memory?

This is what I started doing, but the join code is not there:

Person.find(function(err, people) {
    Team.find(function(err, teams) {
        // Now I have people and teams
        // How to join them?
    });
});

I think you should use SQL (Postgres) for something like this instead of forcing relationships on mongo — Pepp, Jan 03 '14 at 06:32
Good suggestion, but probably a seperate question in its own right. For large enterprise applications there are bound to be complex relationships that should not be forced into one MongoDB document. I am basically going by the decision process described in the book "MongoDB Applied Design Patterns". However I am myself wondering if we are going to do this all over the place then does MongoDB buy us anything? — Naresh, Jan 03 '14 at 06:48
Doing two queries is probably pretty common. These two links might help http://stackoverflow.com/questions/5681851/mongodb-combine-data-from-multiple-collections-in-to-one-how and http://docs.mongodb.org/manual/reference/database-references/ — Pepp, Jan 03 '14 at 06:54

score 7 · Accepted Answer · answered Jan 03 '14 at 07:37

I would opt for using population, which is the common method of 'joining' in Mongoose.

Your schema is already set up for this, so you just have to adjust your query:

Person.find().populate('teamId').exec(function(err, people) {
  ...
});

The people array will look something like this:

[ { name: 'John Doe',
    teamId: { name: 'Orange', _id: ..., __v: 0 },
    _id: ...,
    __v: 0 },
  { name: 'Jane Smith',
    teamId: { name: 'Orange', _id: ..., __v: 0 },
    _id: ...,
    __v: 0 },
  { name: 'Doug White',
    teamId: { name: 'Blue', _id: ..., __v: 0 },
    _id: ...,
    __v: 0 } ]

To get the results you want, it's simple a matter of running a map on it:

var results = people.map(function(person) {
  return { name : person.name, Team : person.teamId.name };
}));

Omg, it turned out to be so simple.. Thanks! – Pavel Staselun Sep 17 '15 at 17:51 — Pavel Staselun, Sep 17 '15 at 17:51

Joining MongoDB collections after fetching them separately

1 Answers1