MongoDB:聚合查询中的Near +查找 [英] MongoDB : near + lookup in aggregation query

问题描述

我想知道这是否有可能.

I would like to know if this is even possible.

实际上，我的查询是:用户表上的近查询+用户运动表上的查找查询的forEach循环，其中user._id =用户运动的userId(就像在foreach中完成的联接一样):

Actually my query is : a near query on Users table + a forEach loop of find queries on UserSports table where user._id = userId of UserSports (it's like a join done in a foreach) :

    //geonear query
    db.users.find({
        geopoint_mongo:
           {$near:
                  {$geometry: {type: "Point", coordinates: [userGeopoint.lng, userGeopoint.lat]},
                        $maxDistance: maxDistance * 1000
                  }
           }
    },
    {_id: 1, geopoint: 1, url_avatar: 1, bool_coach: 1, bool_ambassador: 1, gender: 1, bool_handicap: 1}
    ).toArray(function (err, usersFound) {
        if (usersFound && usersFound.length > 0) {
            var count = 0;
            var total = usersFound.length;
            var users = [];
            // start the loop
            usersFound.forEach(function (user) {
                // find with condition query
                db.userSports.find({
                    userId: user._id,
                    sportId: {$in: sportIds}
                },
                {_id: 1, user_level: 1, sportId: 1}
                ).toArray(function (err, userSports) {
                    count++;
                    if (userSports && userSports.length > 0) {
                        user.userSports = userSports;
                        users.push(user);
                    }
                    if (count === total) {
                        console.log('Done');
                    }
                });
            });
        }
    });

我听说您可以使用$ lookup和Aggregation进行连接，因为实际上我的查询花费了太多时间-但我找不到任何这样的示例. 至少，有解决方案可以更快地运行此代码吗?

I heard you can use $lookup and aggregate to do the join, because actually my queries take too much time - but I wasn't able to find any example of this. At least, is there a solution to run this code faster?

推荐答案

您可以在3.4版本的$lookup阶段使用以下聚合查询.

You can use below aggregation query with $lookup stage in 3.4 version.

db.users.aggregate([
  {"$match":{ // Match to find users in location
    "geopoint_mongo":{
      "$near":{
        "$geometry":{"type":"Point","coordinates":[userGeopoint.lng, userGeopoint.lat]},
        "$maxDistance":maxDistance*1000
      }
    }
  }},
  {"$project":{ // Keep fields from users collection
    "geopoint":1,"url_avatar":1,"bool_coach":1,"bool_ambassador":1,"gender":1,"bool_handicap":1
  }},
  {"$lookup":{ // Join user with user sports collection
    "from":"userSports",
    "localField":"_id",
    "foreignField":"userId",
    "as":"sports"
  }},
  {"$unwind":"$sports"}, // $unwind + $match to get matching sports
  {"$match":{"sports.sportId":{"$in":sportIds}}},
  {"$addFields":{"sports._id":1,"sports.user_level":1,"sports.sportId":1}}, // Keep fields from user sports collection
  {"$group":{
    "_id":"$_id",
    "geopoint":{"$first":"$geopoint"},// Keep fields from users collection
    "bool_coach":{"$first":"$bool_coach"},
    "bool_ambassador":{"$first":"$bool_ambassador"},
    "gender":{"$first":"$gender"},
    "bool_handicap":{"$first":"$bool_handicap"},
    "sports":{"$push":"$sports"} // Collect matching sports
  }}
])

这篇关于MongoDB:聚合查询中的Near +查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！