How to $lookup in nested array of objects

Question

I have to two collections one is tours and other is destinations so in tours have i have an array of locations which has destination object with an id and that id is belongs to another destinations collection but the thing is i am not be able to lookup details of destination in the array of locations. every tried many query search here too. but not getting expected result.

Tours :

{
    "_id" : ObjectId("5f3122f4d8d57e3b9650e5b4"),
    "title" : "tour 1",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae9037ea99f20a79071a"
            },
            "services" : {
                "hotel" : true
            }
        }, 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8c"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}
{
    "_id" : ObjectId("5f2d65e68bc6e9155310d147"),
    "title" : "tour 2",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ecf994435c3a6025d5bf126"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}
{
    "_id" : ObjectId("5f2d66398bc6e9155310d161"),
    "title" : "tour 3",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8d"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}

Destinations :

{
    "_id" : ObjectId("5ec5ae9037ea99f20a79071a"),
    "name" : "dest 1",
    "country" : "country name"
}
{
    "_id" : ObjectId("5ec5ae8e37ea99f20a78ef8c"),
    "name" : "dest 2",
    "country" : "country name"
}
{
    "_id" : ObjectId("5ec5ae8e37ea99f20a78ef8d"),
    "name" : "dest 3",
    "country" : "country name"
}
{
    "_id" : ObjectId("5ecf994435c3a6025d5bf126"),
    "name" : "dest 4",
    "country" : "country name"
}

Expected result :

{
    "_id" : ObjectId("5f3122f4d8d57e3b9650e5b4"),
    "title" : "tour 1",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae9037ea99f20a79071a",
                "name" : "dest 1",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }, 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8c",
                "name" : "dest 2",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
},
{
    "_id" : ObjectId("5f2d65e68bc6e9155310d147"),
    "title" : "tour 2",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ecf994435c3a6025d5bf126",
                "name" : "dest 4",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
},
{
    "_id" : ObjectId("5f2d66398bc6e9155310d161"),
    "title" : "tour 3",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8d",
                "name" : "dest 3",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}

Tried query :

db.tours.aggregate([
  { 
    "$addFields": {
      "locations": {
        "$map": {
          "input": "$locations",
          "in": {
            "$mergeObjects": [
              "$$this",
              {
                "dest_oid": {
                  "$toObjectId": "$$this.destination.id"
                }
              }
            ]
          }
        }
      }
    }
  },
  { "$unwind": "$locations" },

  { "$lookup": {
    "from": "destinations",
    "localField": "locations.dest_oid",
    "foreignField": "_id",
    "as": "locations.dest",
  }},
  { "$unwind": "$locations.dest" },
  { "$group": {
    "_id": "$_id",
    "locations": { "$push": "$locations" }
  }}
])

even i have tried this MongoDB $lookup on nested document

have you checked perfectly your expected result with accepted answer? look at the `destination` is merged in a array and `services` are outside of particular `destination` object? please check perfectly. is that you want? — turivishal, Aug 10 '20 at 17:52

turivishal · Accepted Answer · 2020-08-10T20:22:06.737

0

Quick fixes,

$unwind locations array put first because need to convert id to object id

db.tours.aggregate([
  { $unwind: "$locations" },

you skip this part if you have already converted string id t object id
$addFields replace locations.destination.id to object id filtered your logic to short, here no need $map and $mergeObjects options

  {
    $addFields: {
      "locations.destination.id": {
        $toObjectId: "$locations.destination.id"
      }
    }
  },

$lookup that you have already did, but change as locations.destination

  {
    $lookup: {
      from: "destinations",
      as: "locations.destination",
      localField: "locations.destination.id",
      foreignField: "_id"
    }
  },

$unwind locations.destination because its array and we need object

  {
    $unwind: {
      path: "$locations.destination"
    }
  },

$group that you have already did, few changes, push first destination and services in locations and add first title

  {
    $group: {
      _id: "$_id",
      locations: { $push: "$locations" },
      title: { $first: "$title" }
    }
  }
])

Playground: https://mongoplayground.net/p/yaTCij7NRUj

edited Aug 10 '20 at 20:22

answered Aug 10 '20 at 13:33

turivishal

34,368
7
36
59

this is one is giving `Unrecognized expression '$first'` – Vijay Kumar Aug 10 '20 at 19:37
mongodb version 4.2 – Vijay Kumar Aug 10 '20 at 19:43
using shell for testing, and have to use in mongoose – Vijay Kumar Aug 10 '20 at 20:01
and i have converted string to objectId() – Vijay Kumar Aug 10 '20 at 20:09
okay got it because of $first is not supporting inside $push in 4.2 version, so just need to add another pipeline for that, will add, good if you have converted then no need to use $addFields pipeline, i am updating my answer. – turivishal Aug 10 '20 at 20:11
you can check updated answer, it required to add $unwind before $group and updated playground link as well. you can skip object conversion part if you have converted to object id. – turivishal Aug 10 '20 at 20:24
is there any way of updating string to objectId in this case or do i have to use forEach and update every record one by one. – Vijay Kumar Aug 10 '20 at 20:39
if you want to replace all documents of your collection then try aggregation like [this](https://mongoplayground.net/p/jkugm5bKJHn), this will convert string id to object id and rewrite all documents in your collection, or second option you can do loop. – turivishal Aug 10 '20 at 23:10

ambianBeing · Answer 2 · 2020-08-11T09:23:21.937

-1

If you can convert/update the string destination.id in the Tours collection to ObjectId if they are not already. Following query should work.

Query:

db.Tours.aggregate([
  {
    $unwind: "$locations",
  },
  {
    $lookup: {
      from: "Destinations",
      localField: "locations.destination.id",
      foreignField: "_id",
      as: "destination",
    },
  },
  {
    $project: {
      title: "$title",
      locations: {
        destination: {
          $arrayElemAt: ["$destination", 0],
        },
        services: "$locations.services",
      },
    },
  },
  {
    $group: {
      _id: "$_id",
      title: {
        $first: "$title",
      },
      locations: {
        $push: "$locations",
      },
    },
  },
]);

Playground Link

edited Aug 11 '20 at 09:23

answered Aug 10 '20 at 14:04

ambianBeing

3,449
2
14
25

is there any quick way of converting string to ObjectId or do i have to do forEach to update? – Vijay Kumar Aug 10 '20 at 17:11
@VijayKumar Updated the answer in case u are interested in an alternate query. Also since you mentioned to have updated `destination.id` from string to `ObjectId`. I have not put in the stage of conversion. – ambianBeing Aug 11 '20 at 09:22
what happened if locations have no object does it still give result? or destination.id is null – Vijay Kumar Aug 11 '20 at 10:51
it's working on if destination.id = null but if there is not locations then it's not working. [ref](https://mongoplayground.net/p/yP9nLm95cL3) – Vijay Kumar Aug 11 '20 at 10:59
EDIT: That is how `unwind` works. If locations have no object `[]` you would lose that particular document because of `$unwind`. And if it is `null` the final location array would not contain it but will preserve other properties i.e `services`. Also its always a good idea to include these cases that should be considered in the question itself for a better understanding. Do you want to preserve the document if there are no locations. – ambianBeing Aug 11 '20 at 11:03
i am kind of new bee in mongoDb these cases i got when i used it. – Vijay Kumar Aug 11 '20 at 11:09
No worries! they are good cases to ask. You won't be able to preserve the document here (If that's the intent) here since in both the answers `$unwind` is used on `locations`. Would suggest to open up a new question with your problem and context. Both answers here do get you the expected output. And hope it was helpful. – ambianBeing Aug 11 '20 at 11:17

How to $lookup in nested array of objects

2 Answers2