CODEWITHSUNDEEP
mongodbaggregation-framework
turivishal
turivishal

Reputation: 36154

Search location using $near and $geometry in $lookup resulted field

The scenario is Employee and they are working in multiple Store Locations, and combine both collections, that is working perfectly,

Problem in: Search in particular location (input lat, long) and this query will give that particular employee working on particular store and that location field is in lookup collection.

Employee

{
  "_id": ObjectId("5f03064b3460ef1f10ec2f25"),
  "employeeName": "Anonymous",
  "stores": [
    {
      "storeId": ObjectId("5f03030a3460ef1f10ec2f23"),
      "workTime": "09:30 to 12:30"
    },
    {
      "storeId": ObjectId("5f03064b3460ef1f10ec2f26"),
      "workTime": "01:30 to 02:30"
    }
  ]
}

Store

{
  "_id": ObjectId("5f03030a3460ef1f10ec2f23"),
  "storeName": "A",
  "location": {
    "coordinates": ["longitude", "latitude"]
  }
}
{
  "_id": ObjectId("5f03064b3460ef1f10ec2f26"),
  "storeName": "B",
  "location": {
    "coordinates": ["longitude", "latitude"]
  }
}

The below is final result that i am getting successfully:

{
  "_id": ObjectId("5f03064b3460ef1f10ec2f25"),
  "employeeName": "Anonymous",
  "stores": [
    {
      "storeId": ObjectId("5f03030a3460ef1f10ec2f23"),
      "workTime": "09:30 to 12:30",
      "storeLocation": {
        "storeName": "A",
        "location": {
          "coordinates": ["longitude", "latitude"]
        }
      }
    },
    {
      "storeId": ObjectId("5f03064b3460ef1f10ec2f26"),
      "workTime": "01:30 to 02:30",
      "storeLocation": {
        "storeName": "B",
        "location": {
          "coordinates": ["longitude", "latitude"]
        }
      }
    }
  ]
}

Aggregation

db.Enmployee.aggregate([
    { "$unwind": "$stores" },
    {
      "$lookup": {
        "from": "Store",
        "localField": "stores.storeId",
        "foreignField": "_id",
        "as": "stores.storeLocation"
      }
    },
    { "$unwind": "$stores.storeLocation" },
    {
        "$group": {
            "_id": "$_id",
            "root": { "$mergeObjects": "$$ROOT" },
            "stores": { "$push": "$stores" }
        }
    },
    {
        "$replaceRoot": {
            "newRoot": {
                "$mergeObjects": ["$root", "$$ROOT"]
            }
        }
    },
    // <== below $match query will come here
    {
      "$project": {
        "_id": 1,
        "employeeName": 1,
        "stores.workTime": 1,
        "stores.storeLocation.storeName": 1,
        "stores.storeLocation.location": 1
      }
    }
  ]
)

My goal is to search employee, they are working on particular location of stores using its latitude and longitude, i have tried below query in above aggregation.

{
  "$match": {
    "stores.storeLocation.location": {
      "$near": {
        "$maxDistance": 1000,
        "$geometry": {
          "type": "Point",
          "coordinates": ["Input Longitude", "Input Latitude"]
        }
      }
    }
  }
}

It gives an error:

Failed to execute script.

Error: command failed: {
    "ok" : 0,
    "errmsg" : "$geoNear, $near, and $nearSphere are not allowed in this context",
    "code" : 2,
    "codeName" : "BadValue"
} : aggregate failed 
Details:
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:534:17
assert.commandWorked@src/mongo/shell/assert.js:618:16
DB.prototype._runAggregate@src/mongo/shell/db.js:260:9
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1062:12
DBCollection.prototype.aggregate@:1:355
@(shell):1:1

Upvotes: 1

Views: 430

Answers (1)

Ana Lava
Ana Lava

Reputation: 787

$geoNear is a pipeline stage itself and you can not use it inside $match. Also, it should be the first stage of the pipeline, so you need to declare it in the $lookup pipeline. Note that you also need to have a geo index for that use:

db.getCollection('Store').createIndex( { "location.coordinates" : "2dsphere" } )

After the changes mentioned above, the query will look like as below:

db.Employee.aggregate([
  { "$unwind": "$stores" },
  {
    "$lookup": {
        from: "Store",
        let: {
          storeId: "$stores.storeId"
        },
        pipeline: [
          { $geoNear: {
                includeLocs: "location",
                distanceField: "distance",
                near: {type: 'Point', coordinates: [57, 35]},
                maxDistance: 1000,
                spherical: true}},
          {
            $match: {
              $expr: { $eq: ["$$storeId", "$_id"] }
            }
          }
    ],
    as: "stores.storeLocation"
  }
},
  { "$unwind": "$stores.storeLocation" },
 
  {
      "$group": {
          "_id": "$_id",
          "root": { "$mergeObjects": "$$ROOT" },
          "stores": { "$push": "$stores" }
      }
  },
  {
      "$replaceRoot": {
          "newRoot": {
              "$mergeObjects": ["$root", "$$ROOT"]
          }
      }
  },
  // <== below $match query will come here
  {
    "$project": {
      "_id": 1,
      "employeeName": 1,
      "stores.workTime": 1,
      "stores.storeLocation.storeName": 1,
      "stores.storeLocation.location": 1
    }
  }
]
)

You can findout more about $geoNear options here.

Upvotes: 1

Related Questions