How to do lookup with an array in mongoDB

Question

today I started working with MongoDB. I have created two collections: Restaurant and OpeningHours. I have inserted data to the database with the following code:

   db.OpeningHours.insert({
        day: "Sunday",
        from: "10.00am",
        to: "16.00pm"
});

    db.Restaurant.insert({
         name: "Restaurant01",
         openingHoursId: 
           [
             {id: db.OpeningHours.find({day: "Sunday", from: "10.00am", to: "16.00pm"})[0]._id},
           ]
});

The restaurant contains an array of OpeningHours ids. I want to write a query with lookup so I get all the data from the Restaurant and the data for corresponding opening hours. Here is my code so far and if I run it I get an error: Command failed...

db.Restaurant.aggregate([
{
    $unwind: "$openingHoursId",
    $lookup:
        {
            from: "OpeningHours",
            localField: "id",
            foreignField: "_id",
            as: "RestaurantHours"
        }
}
])

The expected result I want is something like this:

{
   "_id": ObjectId("5c43b6c8d0fa3ff24621f749"),
   "name": "Restaurant01",
   "openingHoursId": 
          [
              {
                 "id": ObjectId("5c43b6c8d0fa3ff2462fg93e")
              }
          ],
   "RestaurantHours" :
          [ 
              {
                  "_id": ObjectId("5c43b6c8d0fa3ff2462fg93e"),
                  "day": "Sunday",
                  "from": "10.00am",
                  "to": "16.00pm"  
              }
          ]
}

This one: https://stackoverflow.com/questions/34967482/lookup-on-objectids-in-an-array — Jonas, Jan 20 '19 at 05:05

Ashh · Accepted Answer · 2019-01-20T08:19:45.727

1

Your localField should be openingHoursId.id not only id

db.Restaurant.aggregate([
  { "$unwind": "$openingHoursId" },
  { "$lookup": {
    "from": "OpeningHours",
    "localField": "openingHoursId.id",
    "foreignField": "_id",
    "as": "openingHoursId.RestaurantHours"
  }},
  { "$unwind": "$openingHoursId.RestaurantHours" },
  { "$group": { 
    "_id": "$_id",
    "name": { "$first": "name" },
    "openingHoursId": { "$push": "openingHoursId" }
  }}
])

edited Jan 20 '19 at 08:19

answered Jan 20 '19 at 05:03

Ashh

44,693
14
105
132

Thanks, but if I and more ids to the openingHoursId array the results won't be pushed into the same array. For example if I add two more ids into the array (total = 3) then the result will print out the same restaurant three times but every iteration it will change the array element – morty Jan 20 '19 at 07:31

How to do lookup with an array in mongoDB

1 Answers1