MongoDB - combining query across mulitple collections

Question

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

score 1 · Accepted Answer · answered Nov 04 '21 at 23:07

1

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

answered Nov 04 '21 at 23:07

J.F.

13,927
9
27
65

This is great, thank you! Looking at the example you linked, this seems to be what I'm looking for. I tried to put this query into Studio3T and got the error 'Unrecognized pipeline stage name: '$set''. Do you know if there's something I need to change to get this to execute in Studio3T? – Cineno28 Nov 05 '21 at 00:38
Which version of mongo are you using? [$set](https://docs.mongodb.com/manual/reference/operator/aggregation/set/) is new in 4.2, maybe you can use [$addFields](https://docs.mongodb.com/manual/reference/operator/aggregation/addFields/) – J.F. Nov 05 '21 at 00:55
Ah yep, that's it! I just found it by reading here: https://stackoverflow.com/questions/62624944/cannot-test-mongo-aggregation-mongoerror-unrecognized-pipeline-stage-name-se. I'm on 4.0.15 so that was it. Thanks so much, I learned a lot from this! – Cineno28 Nov 05 '21 at 01:00

MongoDB - combining query across mulitple collections

1 Answers1