I'm trying to figure out how to essentially do a join in MongoDB. I've read about doing aggregates: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/, but that doesn't seem to be what I'm looking for.

I'm also very new to NoSQL, so I'm not exactly sure what I should be using here.

I have two collections in MongoDB, structured as follows:

db collection - employees:

{
  _id: 1,
  name: 'John Doe',
  filesAccess: {
     web: true
  },
  fileIds: [
     'fileId1',
     'fileId2'
  ]
},
{
  _id: 2,
  name: 'Bob Jones',
  filesAccess: {
     web: false
  },
  fileIds: [
     'fileId3',
     'fileId4'
  ]
}

db collection - files:

{
   _id: fileId1,
   fileMetaData: {
      location: 'NE'
   }
},
{
   _id: fileId2,
   fileMetaData: {
      location: 'NE'
   }
},
{
   _id: fileId3,
   fileMetaData: {
      location: 'SW'
   }
},
{
   _id: fileId4,
   fileMetaData: {
      location: 'SW'
   }
}

I want to be able to query for all employees who have fileAccess.web = true and get their employee ID, name, and fileMetaData.location. The location for all of an employee's files will be the same. So the query only needs to use the first fileId from an employee to get the location from the files collection

So I'd like my result should look like:

{
   _id: 1,
   name: 'John Doe',
   location: 'NE'
}

How would you structure a query to accomplish this in MongoDB? I'm using Studio3T as my interface to the db. Any help is greatly appreciated

CodePudding user response：

You can use this aggregation query:

• First $match to get only documents where filesAccess.web is true.
• The join based on values on fileIds and _id. This give an array calling result.
• Then get the first position
• And $project to output the fields you want.

db.employess.aggregate([
  {
    "$match": {
      "filesAccess.web": true
    }
  },
  {
    "$lookup": {
      "from": "files",
      "localField": "fileIds",
      "foreignField": "_id",
      "as": "result"
    }
  },
  {
    "$set": {
      "result": {
        "$arrayElemAt": [
          "$result",
          0
        ]
      }
    }
  },
  {
    "$project": {
      "_id": 1,
      "name": 1,
      "location": "$result.fileMetaData.location"
    }
  }
])

Example here