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

Question

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列表之外，我还想根据companies集合的_id从集合products和employees中获取每个对应公司的产品和员工列表。这是我正在尝试的代码

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中，我希望只获取基于条件的数据，例如employees having salary>=10000，products having price>=500。那么我该如何实现这一点呢？

Answer 1

下面是我添加的文档：

公司

{
    "_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")
        }
    ]
}

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
                    }
                }
            }

        }
    }
])