$lookup when localfield may not exist MongoDb-CodePudding

I have three collections in MongoDB 4.0 (pymongo)

users: [
{name: "John", house_id: 1, car_id: 1},
{name: "Charles", house_id: 1},
{name: "Jessy", house_id: 2, car_id: 2},
{name: "Tim", house_id: 3}
]

houses: [
{_id: 1, name: "house1"},
{_id: 1, name: "house2"},
{_id: 1, name: "house3"}
]

cars: [
{_id: 1, name: "car1"},
{_id: 2, name: "car2"}
]

In users table, house_id is required but car_id no. I need to do a join between users, houses and cars to have a list of users with houses information and cars information if they have car_id.

This is my script

db.users.aggregate([{                                   
  "$lookup": {
    "from": "houses",
    "localField": "house_id",
    "foreignField": "_id",
    "as": "house"
  }
},
{"$unwind": "$house"},
{                                   
  "$lookup": {
    "from": "cars",
    "localField": "car_id",
    "foreignField": "_id",
    "as": "car"
  }
},
{"$unwind": "$car"}]);

But this script return only users with car_id. If I add a $match with un user._id which don't have car_id, I have no result.

I understand that car_id need to be here to have result but in my case, I need to have all results.

Expected output for a user who don't have car_id :

[
  {
    "_id": ObjectId("xxxxxxxxxx"),
    "name": "Charles"
    "house_id": 1,
    "houses": {
      "_id": 1,
      "name": "house1"
    },
  }
]

CodePudding user response：

I am unsure of what you want your output to look like but you can accomplish this by adding the match before your $lookups.

You can check out a live demo of this query here

Database

Consider the following database.

db={
  users: [
    {
      name: "John",
      house_id: 1,
      car_id: 1
    },
    {
      name: "Charles",
      house_id: 1
    },
    {
      name: "Jessy",
      house_id: 2,
      car_id: 2
    },
    {
      name: "Tim",
      house_id: 3
    }
  ],
  houses: [
    {
      _id: 1,
      name: "house1"
    },
    {
      _id: 2,
      name: "house2"
    },
    {
      _id: 3,
      name: "house3"
    }
  ],
  cars: [
    {
      _id: 1,
      name: "car1"
    },
    {
      _id: 2,
      name: "car2"
    }
  ]
}

Query

We can use this query for nested lookups:

db.users.aggregate([
  {
    $match: {
      name: "Charles"
    }
  },
  {
    "$lookup": {
      "from": "houses",
      "as": "houses",
      "localField": "house_id",
      "foreignField": "_id"
    }
  },
  {
    "$lookup": {
      "from": "cars",
      "as": "cars",
      "localField": "car_id",
      "foreignField": "_id"
    }
  },
  {
    $unwind: {
      path: "$cars",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $unwind: {
      path: "$houses",
      preserveNullAndEmptyArrays: true
    }
  }
])

Result

Which gives us:

[
  {
    "_id": ObjectId("5a934e000102030405000006"),
    "house_id": 1,
    "houses": {
      "_id": 1,
      "name": "house1"
    },
    "name": "Charles"
  }
]