0

I have a visits collection where I successfully count the number of visits per location

Visits model:

{
    "_id": {
        "$oid": "5a3969e2f4ea3e33ac5a523d"
    },
    "locationId": "5a395ccf210a1d35d0df4a58"
}

locationId above is of type 'Object' as I learned lookup localField and foreignField must be of same type

nodejs code =>

let sort = { "count": -1, "locationId": 1 };

Visit.aggregate([
    {
        $match:{ 
            $and: [
                { accountId: req.session.passport.user }, 
                { 
                    'details.dateTimeIn': { 
                        $gte: new Date(dateFrom), $lte: new Date(dateTo) 
                    } 
                }
            ] 
        }
    },
    {
        "$group": {
            //_id: name,
            _id: "$locationId",
            count: { $sum: 1 }
        }
    },
    { $sort: sort }
])

Output is half ok:

[
   {
      "_id":"5a395ccf210a1d35d0df4a58",
      "count":20
   }
]

Instead of showing location id id like to show location name. Schema for locations collection is:

{
    "_id": {
        "$oid": "5a395ccf210a1d35d0df4a58"
        "name": "Tower A",
        "__v": 0
    }
}

Research suggests I need to use $lookup to get that JOIN effect

So I tried

{
    "$lookup": {
        from: "locations",
        localField: "_id",
        foreignField: "_id",
        as: "locationdetails"
     }
}

but the match seems broken. The closest I got was a list of all locations in 'locationdetails'

But with code above here is the empty locationdetails

[
   {
      "_id":"5a395ddf1d221918d0041313",
      "count":20,
      "locationdetails":[

      ]
   }
]

What am I missing ?

Dmitrii Sidenko
  • 660
  • 6
  • 19
Tony Aldo
  • 31
  • 3
  • 2
    This will [help](https://stackoverflow.com/questions/41093647/mongodb-join-on-id-field-from-string-to-objectid). – s7vr Dec 18 '17 at 18:57
  • Hmm I flipped all all the foreign fields to type Object from String. Doesn't seem to help. – Tony Aldo Dec 19 '17 at 19:23

0 Answers0