CODEWITHSUNDEEP
pythonmongodbmongodb-queryaggregation-frameworkpymongo
Kleyson Rios
Kleyson Rios

Reputation: 2867

How to use mongodb aggregate to enrich objects with $lookup?

I'm using Pymongo to run aggregate pipelines against our Mongodb.

I have the following collections:

users

{
  _id: 1,
  name: 'John Doe',
  age: 30
},
{
  _id: 2,
  name: 'Jane Doe',
  age: 20
}

locations

{
  _id: 10,
  name: 'Miami'
},
{
  _id: 20,
  name: 'Orlando'
}

contacts

{
  _id: 100,
  contacts: [
    {
      user_id: 1,
      location_id: 10,
    },
    {
      user_id: 2,
      location_id: 20,
    }
  ]
}

As result of an aggregate pipeline, I need:

{
  _id: 100,
  contacts: [
    {
      user_id: 1,
      user_name: 'John Doe',
      user_age: 30,
      location_id: 10,
      location_name: 'Miami'
    },
    {
      user_id: 2,
      user_name: 'Jane Doe',
      user_age: 20,
      location_id: 20,
      location_name: 'Orlando'
    }
  ]
}

I tried some queries using '$lookup', but I'm just getting a new array instead of putting the values inside the same array/object.

How can I get the desired result ?

Upvotes: 1

Views: 262

Answers (1)

J.F.
J.F.

Reputation: 15187

You can use this aggregation query:

  • First $unwind to deconstructs the array and get values to join
  • Then two $lookup to join values and create arrays users and locations.
  • As the _id is used the value from the array you want is the first one (it should be only one value but if exists multiple whould be repeated values), so you can use $arrayElemAt.
  • Then $project to get the field name you want.
  • And $group to regroup the values.
db.contacts.aggregate([
  {
    "$unwind": "$contacts"
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "contacts.user_id",
      "foreignField": "_id",
      "as": "users"
    }
  },
  {
    "$lookup": {
      "from": "locations",
      "localField": "contacts.location_id",
      "foreignField": "_id",
      "as": "locations"
    }
  },
  {
    "$set": {
      "users": {
        "$arrayElemAt": [
          "$users",
          0
        ]
      },
      "locations": {
        "$arrayElemAt": [
          "$locations",
          0
        ]
      }
    }
  },
  {
    "$project": {
      "contacts": {
        "user_id": 1,
        "location_id": 1,
        "user_name": "$users.name",
        "user_age": "$users.age",
        "location_name": "$locations.name"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "contacts": {
        "$push": "$contacts"
      }
    }
  }
])

Example here

Upvotes: 1

Related Questions