1

I have 2 collection coll1 and coll2. I want to apply $lookup on fields "_id" and "comm_field" so I used the query:

db.coll1.aggregate([
    {
      $lookup:
        {
          from: "coll2",
          localField: "_id",
          foreignField: "comm_field",
          as: "inventory_docs"
        }
   },
   {
       $project:{"_id" : 0, "inventory_docs" : 1}
   },
   { $unwind:"$inventory_docs"}
])

And get the output as:

/* 1 */
{
    "inventory_docs" : {
        "_id" : ObjectId("ssdfsfsdfsdfsfsdfsdfsdfsfsdf"),
        "comm_field" : NumberLong(1111),
        "status" : "active"
    }
}

/* 2 */
{
    "inventory_docs" : {
        "_id" : ObjectId("erteterterterterterterterter"),
        "comm_field" : NumberLong(1111),
        "status" : "active"
    }
}

/* 3 */
{
    "inventory_docs" : {
        "_id" : ObjectId("vbvbfvbdbbcvbvcbcdrgvbcbcbcv"),
        "comm_field" : NumberLong(2222),
        "status" : "active"
    }
}

Is there any way by which i can see the output like:

        {
            "_id" : ObjectId("ssdfsfsdfsdfsfsdfsdfsdfsfsdf"),
            "comm_field" : NumberLong(1111),
            "status" : "active"
        }
        {
            "_id" : ObjectId("erteterterterterterterterter"),
            "comm_field" : NumberLong(1111),
            "status" : "active"
        }
        {
            "_id" : ObjectId("vbvbfvbdbbcvbvcbcdrgvbcbcbcv"),
            "comm_field" : NumberLong(2222),
            "status" : "active"
        }

So basically I want my output in format {---}, not in {"inventory_docs":{---}}

Shashank
  • 712
  • 15
  • 33
  • 1
    Use `$replaceRoot` or simply `$project` the fields to return the sub-properties "after" doing the `$unwind`. You are not really gaining much from placing the `$project` "before", or at least not as much gain as you seem to think you are. Placing `$unwind` directly following the `$lookup` is actually a [pipeline optimization](https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#lookup-unwind-coalescence), that "elevates" the `$unwind` into the `$lookup` itself. – Neil Lunn Aug 24 '17 at 07:45
  • It worked. Thanks @NeilLunn – Shashank Aug 24 '17 at 10:42
  • @NeilLunn $replaceRoot worked but $project didn't. As per you suggestion i changed the order of $unwind and $project with embedded document values. but still can't get rid from format {"inventory_docs":{---}}. I am using this query now: db.coll1.aggregate([ { $lookup: { from: "coll2", localField: "_id", foreignField: "comm_field", as: "inventory_docs" } }, { $unwind:"$inventory_docs"}, { $project:{"_id" : 0, "inventory_docs._id" : 1, "inventory_docs.comm_field" : 1, "inventory_docs.status" : 1}} ]) – Shashank Aug 25 '17 at 03:11

1 Answers1

0

Neil's answer worked. Addition of { $replaceRoot: { newRoot: "$inventory_docs" } } did the job. Thanks Neil.

db.coll1.aggregate([
    {
      $lookup:
        {
          from: "coll2",
          localField: "_id",
          foreignField: "comm_field",
          as: "inventory_docs"
        }
   },
   {
       $project:{"_id" : 0, "inventory_docs" : 1}
   },
   { $unwind:"$inventory_docs"},
   { $replaceRoot: { newRoot: "$inventory_docs" } }
])
Shashank
  • 712
  • 15
  • 33