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

user_2135819 0
Viewed 52

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 ?

aggregation-framework pymongo mongodb-query mongodb python
1 Answers

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

user_134642790
Related