Mongodb aggregate: get referenced document field value

Question

I would like to get a field value from a referenced document. Mongodb-version is 3.4.

Lets say I have 2 collections foo and bar. Foo has a reference to bar:

{
  "_id" : ObjectId("5b6c713ea502dea387860eba"),
  "stack" : "overflow",
  "bar" : {
    "$ref" : "bar",
    "$id" : ObjectId("5b6c70e1a502dea387860ea6"),
    "$db" : "stackoverflow"
  }
}

And the corresponding bar document looks like this:

{
  "_id" : ObjectId("5b6c70e1a502dea387860ea6"),
  "name" : "bar-2"
}

I tried the following aggregate function to archieve this result:

{
  "_id" : ObjectId("5b6c713ea502dea387860eba"),
  "stack" : "overflow",
  "barName" : "bar-2"
}

Aggregrate-function:

db.getCollection('foo').aggregate([
  { $match: { _id: ObjectId("5b6c713ea502dea387860eba") }},
  {
    $lookup: {
        from: 'bar',
        localField: 'bar',
        foreignField: '_id',
        as: "bar"
      }
  },
  {
    $project: {
        _id: 1,
        stack: "$stack",
        barName: "$bar.name"
    }
  }

  ]).pretty()

But my result is this:

{
  "_id" : ObjectId("5b6c713ea502dea387860eba"),
  "stack" : "overflow",
  "barName" : []
}

I tried to google it, I looked into the mongodb docs but was not able to find a solution for this.

Is there something I'm missing or is this not possible?

Thanks!

Ashh Ashh · Accepted Answer · 2018-08-09T17:13:43

You need to $unwind the bar and also your localField is bar.id not only bar

db.getCollection('foo').aggregate([
  { "$match": { "_id": ObjectId("5b6c713ea502dea387860eba") }},
  { "$lookup": {
    "from": "bar",
    "localField": "bar.id",
    "foreignField": "_id",
    "as": "bar"
  }},
  { "$unwind": "$bar" },
  { "$project": {
    "_id": 1,
    "stack": "$stack",
    "barName": "$bar.name"
  }}
])

Mongodb aggregate: get referenced document field value

2 Answers