使用Pipeline进行MongoDB聚合查找不起作用

Question

我有两个集合。如果集合2中的1号和2号在集合1中指定的一定范围内，我正在尝试将集合2的文档添加到集合1中。集合1中的FYI ObjectId和集合2中的ObjectId指的是两个不同的项目/产品，因此我无法在此id上加入两个集合。

集合1中的示例文档：

{'_id': ObjectId('4321'),
 'number1_lb': 61.205672407820025,
 'number1_ub': 61.24170844385606,
 'number2_lb': -149.75074963516136,
 'number2_ub': -149.71471359912533}

集合2中的示例文档：

{'_id': ObjectId('1234'),
  'number1': 1.282298,
  'number2': 103.8475}

我想要输出：

{'_id': ObjectId('4321'),
 'number1_lb': 61.205672407820025,
 'number1_ub': 61.24170844385606,
 'number2_lb': -149.75074963516136,
 'number2_ub': -149.71471359912533,
 'recs': [ObjectId('3456'), ObjectId('4567'),...]

我认为使用管道的查找阶段可以工作。我的代码当前如下：

 {"$lookup":{
        "from": "Collection 2",
        "let":{
            "number1_lb":"$number1_lb",
            "number1_ub":"$number1_ub",
            "number2_lb":"$number2_lb",
            "number2_ub":"$number2_ub"
        },
        "pipeline": [
            {"$match":
             {"$expr":
              {"$and":[
                  {"$gte":["$number1","$$number1_lb"]},
                  {"$gte":["$number2","$$number2_lb"]},
                  {"$lte":["$number1","$$number1_ub"]},
                  {"$lte":["$number2","$$number2_ub"]}
              ]}}}
        ],
        "as": "recs"
    }}

但是运行上面的没有给我输出。我做错了什么吗？？

Answer 1

您希望使用查找$和项目：

   {
        $lookup: {
            from: "Collection2",
            localField: [Foreign Field of the Collection1],
            foreignField: [Principal field of the foreign collection here Collection2],
            as: "nameJoint"
            }
    },
    {$project: {

        "newFieldName": 
    }},

但要在两个文件之间建立一个连接，这两个文件之间就有一个公共字段。我不确定在这种情况下是否有这样一个问题，或者我误解了它。

（一个$lookup基本上是noSQL中的一个SQL关节）

Answer 2

我运行它，它似乎工作得很好；但我不得不在coll1中调整您的输入数据，因为它不符合标准。

from pymongo import MongoClient
from bson.json_util import dumps

db = MongoClient()["testdatabase"]
# Data Setup
db.coll1.replace_one({"_id": "4321"}, {"_id": "4321", "number1_lb": -61.205672407820025, "number1_ub": 61.24170844385606, "number2_lb": -149.75074963516136, "number2_ub": 149.71471359912533}, upsert=True)
db.coll2.replace_one({"_id": "1234"}, {"_id": "1234", "number1": 1.282298, "number2": 103.8475}, upsert=True)
# Run the aggregation
results = db.coll1.aggregate([
    {"$lookup": {
        "from": "coll2",
        "let": {
            "number1_lb": "$number1_lb",
            "number1_ub": "$number1_ub",
            "number2_lb": "$number2_lb",
            "number2_ub": "$number2_ub"
        },
        "pipeline": [
            {"$match":
                {"$expr":
                    {"$and": [
                        {"$gte": ["$number1", "$$number1_lb"]},
                        {"$gte": ["$number2", "$$number2_lb"]},
                        {"$lte": ["$number1", "$$number1_ub"]},
                        {"$lte": ["$number2", "$$number2_ub"]}
                    ]}}}
        ],
        "as": "recs"
    }}
])
# pretty up the results
print(dumps(results, indent=4))

给出：

[
    {
        "_id": "4321",
        "number1_lb": -61.205672407820025,
        "number1_ub": 61.24170844385606,
        "number2_lb": -149.75074963516136,
        "number2_ub": 149.71471359912533,
        "recs": [
            {
                "_id": "1234",
                "number1": 1.282298,
                "number2": 103.8475
            }
        ]
    }
]