MongoDB nested lookup with sum-CodePudding

I need to retrieve the entire single object hierarchy from the database as a JSON. And at the same time sum from 3 level nested collection.

Venue

{"_id": "2", "name": "name2"}
{"_id": "3", "name": "name3"}
{"_id": "4", "name": "name4"}
{"_id": "5", "name": "name5"}

user

{"_id": "u2", "name": "u2", venue_id: "2"}
{"_id": "u3", "name": "u3", venue_id: "2"}
{"_id": "u4", "name": "u4", venue_id: "3"}
{"_id": "u5", "name": "u5", venue_id: "4"}

show

{"_id": "s2", "name": "s2", host_id: "u2", "duration": 20}
{"_id": "s3", "name": "s3", host_id: "u2", "duration": 50}
{"_id": "s4", "name": "s4", host_id: "u2", "duration": 60}
{"_id": "s5", "name": "s5", host_id: "u4", "duration": 30}

I need to retrieve all Venues with sum of show duration where the user is associated with that venue.

output

{"_id": "2", "name": "name2", "duration": 130}
{"_id": "3", "name": "name3", "duration": 30}
{"_id": "4", "name": "name4", "duration": 0}
{"_id": "5", "name": "name5", "duration": 0}

CodePudding user response：

You could be thinking over-complicatedly if you are going to do with a "nested" lookup. 2 simple $lookup should suffice what you need. Do a final $group to get your expected result.

db.venue.aggregate([
  {
    "$lookup": {
      "from": "user",
      "localField": "_id",
      "foreignField": "venue_id",
      "as": "userLookup"
    }
  },
  {
    "$unwind": {
      path: "$userLookup",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    "$lookup": {
      "from": "show",
      "localField": "userLookup._id",
      "foreignField": "host_id",
      "as": "showLookup"
    }
  },
  {
    "$unwind": {
      path: "$showLookup",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      duration: {
        $sum: "$showLookup.duration"
      }
    }
  }
])

Here is the Mongo playground for your reference.