Do a conditional $lookup depending of a value in MongoDB?

user_12164583 0
Viewed 413

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.

aggregation-framework pymongo mongodb
1 Answers

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

user_89871280
Related