One to Many MongoDB lookup with project req fields

Question

I have a one to many relationship of two Collections say A to B. How can I i show the desired output in one document for each id. For example, I have

/*Collection A*/
{
    "a_Id": "abc",
        "name": "xyz",
        "age": 5
}

...//Other docs

/*Collection B*/
{
    "b_id": "abc",
    "FeeAmount": 800000,
    "invoiceNumber": "A10",
    "Date": "2021-10-29T00:00:00.000+04:00",
    "PaidAmount": 200000
},
{
    "b_id": "abc",
    "FeeAmount": 90,
    "invoiceNumber": "A20",
    "Date": "2021-10-29T00:00:00.000+04:00",
    "PaidAmount": 20
}

//...other docs multiple for different ids eg abc1,abc2

How can I achieve the following output after lookup on base of id? This is one document per id.

    /*Desired OutPut*/
//Document 1
    {
       "name": "xyz",
        "age": 5
       "availableLimitAmount": 800000,
      "FeeAmount": 800000,
        "invoiceNumber": "A10",
        "Date": "2021-10-29T00:00:00.000+04:00",
        "PaidAmount": 200000
    },
    {
        "name": "xyz",
         "age": 5
        "FeeAmount": 90,
        "invoiceNumber": "A20",
        "Date": "2021-10-29T00:00:00.000+04:00",
        "PaidAmount": 20
    }
//Document 2
 {
       "name": "qwe",
        "age": 50
       "availableLimitAmount": 20000,
      "FeeAmount": 40000,
        "invoiceNumber": "B10",
        "Date": "2021-1-1T00:00:00.000+04:00",
        "PaidAmount": 1000
    },
    {
        "name": "qwe",
         "age": 50
        "FeeAmount": 40,
        "invoiceNumber": "B20",
        "Date": "2021-2-2T00:00:00.000+04:00",
        "PaidAmount": 500
    }

Umer Abbas · Accepted Answer · 2021-03-30T15:28:09.997

1

Here is a working solution for how you can achieve that.

db.coll1.aggregate([
  {
    $lookup: {
      localField: "a_Id",
      from: "coll2",
      foreignField: "b_id",
      as: "data",
      
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          "$data"
        ]
      }
    }
  },
  {
    $project: {
      "data": 0
    }
  }
])

Updated

db.coll1.aggregate([
  {
    $lookup: {
      localField: "a_Id",
      from: "coll2",
      foreignField: "b_id",
      as: "data",
      
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          "$data"
        ]
      }
    }
  },
  {
    $project: {
      "data": 0
    }
  },
  {
    $group: {
      _id: "$a_Id",
      data: {
        $push: "$$ROOT"
      }
    }
  }
])

edited Mar 30 '21 at 15:28

answered Mar 26 '21 at 15:25

Umer Abbas

1,866
3
13
19

It again gives result with multiple documents. i want the result in same document but different object. Means one document with multiple resulting objects for one id. A new document when the id is changed then all those id results again joined in one document with an object each. – Wajih Mar 29 '21 at 12:13
Please update your question with the desired output like the way you tried to explain in the above comment it's difficult to understand. – Umer Abbas Mar 29 '21 at 13:15
@Wajih Check if the updated query works for you. – Umer Abbas Mar 30 '21 at 15:29
yes It worked. thanks for your time. i appreciate that. – Wajih Mar 31 '21 at 07:39

Dheemanth Bhat · Answer 2 · 2021-03-26T15:30:11.997

Join two collections A and B using $lookup operator.
perform $unwind operation to "spread" the result.
$project it the way you want.

So try this:

db.A.aggregate([
    {
        $lookup: {
            from: "B",
            localField: "a_Id",
            foreignField: "b_id",
            as: "B"
        }
    },
    { $unwind: "$B" },
    {
        $project: {
            "_id": 0,
            "name": "$name",
            "age": "$age",
            "FeeAmount": "$B.FeeAmount",
            "invoiceNumber": "$B.invoiceNumber",
            "Date": "$B.Date",
            "PaidAmount": "$B.PaidAmount",
        }
    }
]);

One to Many MongoDB lookup with project req fields

2 Answers2