CODEWITHSUNDEEP
mongodb
vimmi
vimmi

Reputation: 407

MongoDb documents join with inner objects

I have 2 documents named calls and customers i want to join locationId of calls to customers.location._id

calls

      "_id": "5d456560221b9147e4af4b97",
      "customerId": "5d0a3bbfac9470aea56ad619",
      "locationId": "5d11d2c4cba8151f67f735b1",
      "startDateTime": "2019-06-21T05:30:00.000Z",
      "endDateTime": "2019-06-21T06:00:00.000Z"
    }

customers

      "_id": "5d0a3bbfac9470aea56ad619",
      "locations": [
        {
          "_id": "5d11d2c4cba8151f67f735b1",
          "phone": "9889599999",
          "ext": "91",
          "address": "sad",
          "neighborhood": "sda",
          "city": "punetest",
          "state": "MH",
          "zip": "589365",
          "country": "india",
          "isBillingAddress": false,
          "taxPercentage": 0,
          "type": "Residential",
          "status": "Active",
          "firstName": "A",
          "lastName": "B"
        },
        {
          "_id": "5d11e457cba8151f67f735b3",
          "phone": "969696999",
          "ext": "91",
          "address": "",
          "neighborhood": "",
          "city": "udaipur",
          "state": "raj",
          "zip": "312563",
          "country": "india",
          "isBillingAddress": false,
          "taxPercentage": 0,
          "type": "Residential",
          "status": "Deleted",
          "firstName": "AB",
          "lastName": "CD"
        }
      ],
      "createdAt": "2019-06-19T13:42:23.479Z",
      "updatedAt": "2019-06-25T13:39:07.597Z"
    }

    [
     {
        $lookup: {
          from: 'customers.locations',
          localField: 'locationId',
          foreignField: '_id',
          as: 'customers.locations',
        },
      }
     ]);

it is not working I have 2 documents named calls and customers i want to join locationId of calls to customers.location._id

I want output``` { "_id": "5d456560221b9147e4af4b97", "customerId": "5d0a3bbfac9470aea56ad619", "locationId": "5d11d2c4cba8151f67f735b1", "location":{"_id": "5d11d2c4cba8151f67f735b1", "phone": "9889599999", "ext": "91", "address": "sad", "neighborhood": "sda", "city": "punetest", "state": "MH", "zip": "589365", "country": "india", "isBillingAddress": false, "taxPercentage": 0, "type": "Residential", "status": "Active", "firstName": "A", "lastName": "B"} }

Upvotes: 1

Views: 52

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

Ok, this is what you're looking at :

If your locationId in calls has only one matching customer.locations._id in customer :

 db.getCollection('calls').aggregate([
  {
    $lookup: {
      from: 'customers',
      localField: 'locationId',
      foreignField: 'locations._id',
      as: 'customersCalls',
    }
  }, { $unwind: '$customersCalls' },
  {
    $project: {
      customerId: 1, locationId: 1, locations: {
        $filter: {
          input: "$customersCalls.locations",
          as: "item",
          cond: { $eq: ["$$item._id", '$locationId'] }
        }
      }
    }
  },
  { $group: { _id: '$_id', result: { $first: '$$ROOT' } } },
  { $replaceRoot: { newRoot: "$result" } }
])

else if your locationId in calls has multiple matching customer.locations._id in customer :

db.getCollection('calls').aggregate([
  {
    $lookup: {
      from: 'customers',
      localField: 'locationId',
      foreignField: 'locations._id',
      as: 'customersCalls',
    }
  }, { $unwind: '$customersCalls' },
  {
    $project: {
      customerId: 1, locationId: 1, locations: {
        $filter: {
          input: "$customersCalls.locations",
          as: "item",
          cond: { $eq: ["$$item._id", '$locationId'] }
        }
      }
    }
  },
  { $group: { _id: '$_id', locations: { $push: { $arrayElemAt: ["$locations", 0] } }, result: { $first: '$$ROOT' } } },
  { $addFields: { 'result.locations': '$locations' } },
  { $replaceRoot: { newRoot: "$result" } }
])

Upvotes: 1

Related Questions