One to Many relationship join and project

Working with Data
, , ,
1

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
}

/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
}

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

/Desired OutPut/
{
“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
}

2

Hi @MWD_Wajih_N_A,

The following aggregation lookup can do the requested… Its important to note that using a lookup can potentially have performance overhead and we suggest to design the schema to avoid lookups as much as possible.

db.collectionB.aggregate([{$lookup: {
  from: 'collectionA',
  localField: 'a_id',
  foreignField: 'b_id',
  as: 'lookup'
}}, {$project: {
  "name" : { $first : "$lookup.name" },
  "age"  : { $first : "$lookup.age" },
  FeeAmount : 1,
  Date : 1,
  PaidAmount : 1,
  availableLimitAmount : 1
}}])

RESULT:

{ _id: ObjectId("6061d50144567725448f109b"),
  FeeAmount: 800000,
  Date: '2021-10-29T00:00:00.000+04:00',
  PaidAmount: 200000,
  name: 'xyz',
  age: 5 }
{ _id: ObjectId("6061d50144567725448f109c"),
  FeeAmount: 90,
  Date: '2021-10-29T00:00:00.000+04:00',
  PaidAmount: 20,
  name: 'xyz',
  age: 5 }

Best regards,
Pavel

1 Like