如何使用lookup mongodb hapijs从集合中的每个文档中获取其他集合中的文档数组-电脑我帮您

如何使用lookup mongodb hapijs从集合中的每个文档中获取其他集合中的文档数组

我正在使用MongoDB和HapiJs我有3个mongo db集合，如下所示：

companies: [
    {_id: "autogeneratedId", name: "companyname1"},
    {_id: "autogeneratedId", name: "companyname2"}
]
employees: [
    {_id: "autogeneratedId", salary: 10000, employeename: "employeename1", "company_id": "_id of company"}, 
    {_id: "autogeneratedId", salary: 50000, employeename: "employeename2", "company_id": "_id of company"}, 
    {_id: "autogeneratedId", salary: 25000, employeename: "employeename3", "company_id": "_id of company"}
]
products: [
    {_id: "autogeneratedId", price: 900, productname: "productname1", "company_id": "_id of company"}, 
    {_id: "autogeneratedId", price: 400, productname: "productname2", "company_id": "_id of company"}, 
    {_id: "autogeneratedId", price: 500, productname: "productname3", "company_id": "_id of company"}
]

我想从companies系列中获取公司列表。现在连同companies列表，我还想根据products集合的_id从employees和companies的集合中获取每个相应公司的产品和员工列表。这是我正在尝试的代码

db.getCollection('companies').aggregate([
                {
                  $lookup:
                    {
                      from: "employees",
                      localField: "_id",
                      foreignField: "company_id",
                      as: "employees_array"
                    }
               },
                {
                  $lookup:
                    {
                      from: "products",
                      localField: "_id",
                      foreignField: "company_id",
                      as: "products_array"
                    }
               },
            ])

但由于某种原因，上面的代码在products_array和employees_array中返回一个空数组。所以，有谁能告诉我，我在做什么错了？

在employees_array和products_array中，我想仅根据薪水> = 10000的员工和价格> = 500的产品等条件获取数据。那么我该如何实现呢？

回答如下：

这是我添加的文件：

公司

{
    "_id" : ObjectId("5a3455b69beb3178555e98da"),
    "name" : "companyname1"
}
{
    "_id" : ObjectId("5a3455b69beb3178555e98dc"),
    "name" : "companyname2"
}

雇员

{
    "_id" : ObjectId("5a34564c9beb3178555e990d"),
    "salary" : 10000,
    "employeename" : "employeename1",
    "company_id" : ObjectId("5a3455b69beb3178555e98da")
}
{
    "_id" : ObjectId("5a34564c9beb3178555e990f"),
    "salary" : 50000,
    "employeename" : "employeename2",
    "company_id" : ObjectId("5a3455b69beb3178555e98da")
}
{
    "_id" : ObjectId("5a34564c9beb3178555e9911"),
    "salary" : 25000,
    "employeename" : "employeename3",
    "company_id" : ObjectId("5a3455b69beb3178555e98dc")
}

制品

{
    "_id" : ObjectId("5a3456929beb3178555e991e"),
    "price" : 900,
    "productname" : "productname1",
    "company_id" : ObjectId("5a3455b69beb3178555e98da")
}
{
    "_id" : ObjectId("5a3456929beb3178555e9920"),
    "price" : 400,
    "productname" : "productname2",
    "company_id" : ObjectId("5a3455b69beb3178555e98dc")
}
{
    "_id" : ObjectId("5a3456929beb3178555e9922"),
    "price" : 500,
    "productname" : "productname3",
    "company_id" : ObjectId("5a3455b69beb3178555e98da")
}

仅应用您的查询时：

db.getCollection('companies').aggregate([{
        $lookup: {
            from: "employees",
            localField: "_id",
            foreignField: "company_id",
            as: "employees_array"
        }
    },
    {
        $lookup: {
            from: "products",
            localField: "_id",
            foreignField: "company_id",
            as: "products_array"
        }
    }
])
}

如果您的字段是按照您在问题中发布的名称命名的，那么您应该得到结果。寻找某种类型或东西。我得到以下结果（从你描述的内容看似正确）：

{
    "_id" : ObjectId("5a3455b69beb3178555e98da"),
    "name" : "companyname1",
    "employees_array" : [ 
        {
            "_id" : ObjectId("5a34564c9beb3178555e990d"),
            "salary" : 10000,
            "employeename" : "employeename1",
            "company_id" : ObjectId("5a3455b69beb3178555e98da")
        }, 
        {
            "_id" : ObjectId("5a34564c9beb3178555e990f"),
            "salary" : 50000,
            "employeename" : "employeename2",
            "company_id" : ObjectId("5a3455b69beb3178555e98da")
        }
    ],
    "products_array" : [ 
        {
            "_id" : ObjectId("5a3456929beb3178555e991e"),
            "price" : 900,
            "productname" : "productname1",
            "company_id" : ObjectId("5a3455b69beb3178555e98da")
        }, 
        {
            "_id" : ObjectId("5a3456929beb3178555e9922"),
            "price" : 500,
            "productname" : "productname3",
            "company_id" : ObjectId("5a3455b69beb3178555e98da")
        }
    ]
}
{
    "_id" : ObjectId("5a3455b69beb3178555e98dc"),
    "name" : "companyname2",
    "employees_array" : [ 
        {
            "_id" : ObjectId("5a34564c9beb3178555e9911"),
            "salary" : 25000,
            "employeename" : "employeename3",
            "company_id" : ObjectId("5a3455b69beb3178555e98dc")
        }
    ],
    "products_array" : [ 
        {
            "_id" : ObjectId("5a3456929beb3178555e9920"),
            "price" : 400,
            "productname" : "productname2",
            "company_id" : ObjectId("5a3455b69beb3178555e98dc")
        }
    ]
}

如果你想要这些salary >= 10000 and products having price >= 500条件，你必须像这样制定聚合管道：

db.getCollection('companies').aggregate([{
        $lookup: {
            from: "employees",
            localField: "_id",
            foreignField: "company_id",
            as: "employees_array"
        }
    },
    {
        $lookup: {
            from: "products",
            localField: "_id",
            foreignField: "company_id",
            as: "products_array"
        }
    },
    {
        $project: {
            employees: {
                $map: {
                    input: {
                        "$filter": {
                            "input": "$employees_array",
                            "as": "employee",
                            "cond": {
                                "$gte": ["$$employee.salary", 10000]
                            }
                        }
                    },
                    as: "emp",
                    in: {
                        "salary": "$$emp.salary",
                        "name": "$$emp.employeename"
                        // You can add more fields
                    }
                }
            },
            products: {
                $map: {
                    input: {
                        "$filter": {
                            "input": "$products_array",
                            "as": "product",
                            "cond": {
                                "$gte": ["$$product.price", 500]
                            }
                        }
                    },
                    as: "prd",
                    in: {
                        "price": "$$prd.price",
                        "productname": "$$prd.productname"
                        // you can add more fields
                    }
                }
            }

        }
    }
])

如果某些阵列不满足此salary >= 10000（对于员工）或此price >= 500（对于产品），则可能会得到0结果，但这是正常的。