I have three collections in MongoDB 4.0 (pymongo)

users: [
{name: "John", user_type: "client", society: 1},
{name: "Charles", user_type: "client", society: 1},
{name: "Jessy", user_type: "provider", society: 1},
{name: "Tim", user_type: "provider", society: 2}
]

clients: [
{_id: 1, name: "Client1"}
]

providers: [
{_id: 1, name: "Provider1"},
{_id: 2, name: "Provider2"}
]

I need to do a join between users and clients or providers depending on user_type value and set it in the same key value in the result.

For example, the result will be :

user : {name: "John", user_type: "client", society: 1, complete_society: {_id: 1, name: "Client1"}}

or

user : {name: "Tim", user_type: "provider", society: 2, complete_society: {_id: 2, name: "Provider2"}}

The only solution I have now is to do two different $lookup in two different key and then rework the result after the request

db.users.aggregate([{                                   
  "$lookup": {
    "from": "clients",
    "localField": "society",
    "foreignField": "_id",
    "as": "client"
  }
},
{"$unwind": "$clients"},{                                   
  "$lookup": {
    "from": "providers",
    "localField": "society",
    "foreignField": "_id",
    "as": "provider"
  }
},
{"$unwind": "$providers"}]);

And then do a forEach and set a key complete_society and delete previous keys. It's not the perfect way and maybe in mongo, something exists to do that.

CodePudding user response：

You can change your query as per below to achieve your expected result,

• remove $unwind stages
• $project to show required fields
• $arrayElemAt to get the first element from the result of lookup
• $cond to check user_type is "client" then return client array otherwise return provider array

db.users.aggregate([
  {
    $lookup: {
      from: "clients",
      localField: "society",
      foreignField: "_id",
      as: "client"
    }
  },
  {
    $lookup: {
      from: "providers",
      localField: "society",
      foreignField: "_id",
      as: "provider"
    }
  },
  {
    $project: {
      name: 1,
      society: 1,
      user_type: 1,
      complete_society: {
        $arrayElemAt: [
          {
            $cond: [{ $eq: ["$user_type", "client"] }, "$client", "$provider"]
          },
          0
        ]
      }
    }
  }
])

Playground