'Mongoose $lookup in same collection doesn't work

I have the following dataset, where I have two types of objects discriminate by the field lType that can be either base or extra. extra has a ref to a base location.

    [
            {
                "_id": "622b6f1c7a0aca9aa252756c",
                "country": "US",
                "county": "Florida",
                "city": "Miami",
                "lType": "base",
                "displayString": "Florida,Miami",
                "__v": 0
            },
            {
                "_id": "622b6f1d7a0aca9aa252756e",
                "landmark": "Gas station",
                "baseLocation": "622b6f1c7a0aca9aa252756c",
                "lType": "extra",
                "displayString": "Florida,Miami,Gas station",
                "__v": 0
            },
            {
                "_id": "622b6f4c5d0fe602a18826a7",
                "country": "US",
                "county": "Florida",
                "city": "Tampa",
                "lType": "base",
                "displayString": "Florida,Tampa",
                "__v": 0
            },
            {
                "_id": "622b6f4c5d0fe602a18826a9",
                "landmark": "Downtown",
                "baseLocation": "622b6f4c5d0fe602a18826a7",
                "lType": "extra",
                "displayString": "Florida,Tampa,Downtown",
                "__v": 0
            }
    ]

When I try to do an aggregation lookup like this

    const location = await Location.collection.aggregate([{
      $lookup: {
        from: 'location',
        localField: 'baseLocation',
        foreignField: '_id',
        as: 'locationData'
      }
    }]).toArray();

I get an empty array in locationData for my extra locations even though my baseLocation field is an ObjectId pointing to a base location object.

node.jsmongodbmongoosemongodb-queryaggregation-framework


Solution 1:[1]

I think you have a typo. Instead of from: 'location', it should be from: 'locations'.

Try to change your code like this:

const location = await Location.collection.aggregate([{
  $lookup: {
    from: 'locations',
    localField: 'baseLocation',
    foreignField: '_id',
    as: 'locationData'
  }
}]);

Working example

Solution 2:[2]

In case you need more "depth" linking locations, you could use "$graphLookup".

[Finds the Candy Machine at the Gas Station.]

db.locations.aggregate([
  {
    "$graphLookup": {
      "from": "locations",
      "startWith": "$baseLocation",
      "connectFromField": "baseLocation",
      "connectToField": "_id",
      "as": "locationData"
    }
  }
])

Try it on mongoplayground.net.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 NeNaD
Solution 2 rickhg12hs

Related Questions