MongoDB aggregation project the specific fields from lookup

Question

This example is following https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#use-lookup-with-mergeobjects

db.orders.insert([
   { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
   { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 }
])

db.items.insert([
  { "_id" : 1, "item" : "almonds", description: "almond clusters", "instock" : 120 },
  { "_id" : 2, "item" : "bread", description: "raisin and nut bread", "instock" : 80 },
  { "_id" : 3, "item" : "pecans", description: "candied pecans", "instock" : 60 }
])

Aggregation:

db.orders.aggregate([
   {
      $lookup: {
         from: "items",
         localField: "item",    // field in the orders collection
         foreignField: "item",  // field in the items collection
         as: "fromItems"
      }
   },
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromItems", 0 ] }, "$$ROOT" ] } }
   },
   { $project: { fromItems: 0 } }
])

Result:

{ "_id" : 1, "item" : "almonds", "description" : "almond clusters", "instock" : 120, "price" : 12, "quantity" : 2 }
{ "_id" : 2, "item" : "pecans", "description" : "candied pecans", "instock" : 60, "price" : 20, "quantity" : 1 }

Question: How to modify the aggregation to project the specific fields? e.g. project "_id", "item" and "description" only:

{ "_id" : 1, "item" : "almonds", "description" : "almond clusters" }
{ "_id" : 2, "item" : "pecans", "description" : "candied pecans" }

WHat you have done is correct, mongoplayground.net/p/qhpWsmPKwVl .Just need to project the wanted fields only — varman
@varman Thanks, and actually my situation is like this, mongoplayground.net/p/T96ijiGvZb5 , I use orders.itemId to reference items._id, and I cannot project the description. — potato
The problem is your itemId si string in orders collection, meanwhile _id is number in items collection. What @minsky gave you is a correct answer. — varman

Minsky Minsky · Accepted Answer · 2020-11-23T14:07:57

You're getting an empty array, because the $lookup catching anything.

match the types
$addFields to convert

PLAYGROUND

This should be the first stage:

      {
        $addFields: {
          itemId: {
            $convert: {
              input: "$itemId",
              to: "int"
            }
          }
        }
      },

If you prefer, there is no need to add a stage

You could also remove addFields and use $lookup+let.

Modify the lookup this way:

 {
    $lookup: {
      from: "items",
      let: {
        itemId: {
          $convert: {
            input: "$itemId",
            to: "int"
          }
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id",
                "$$itemId"
              ]
            }
          }
        }
      ],
      /** field in the items collection*/
      as: "fromItems"
    }
  }

PLAYGROUND2

MongoDB aggregation project the specific fields from lookup

1 Answers