MongoDB - 根据另一个字段的值进行$match筛选

Question

我希望能像SQL中的简单内连接一样获得结果，以获取所有与id_customer相同的账户。 我几乎做到了这一点，但我得到了类似于左连接的结果，并且我不知道如何过滤查询结果。 查询：

db.customer.aggregate([
    {
        $match: {
            "LOAN.AMOUNT": { $gte: 41000 }
        }
    },

    {
        $lookup: {
            from: "department",
            localField: "ID",
            foreignField: "ACCOUNT.ID_CUSTOMER",
            as: "customer"
        }
    },
    { $match: { "myArray": { $ne: [] } } },
    {
        $unwind: {
            path: "$customer",
            preserveNullAndEmptyArrays: false
        }
    }
    ,
    {
        $unwind: "$customer.ACCOUNT"
    },
    {
            $match: {
                "customer.ACCOUNT.ID_CUSTOMER": "$ID" 
            }
    },

]) 

实际结果：

empty

期望的结果：

{ 
    "_id" : ObjectId("5dfccc28d29876c2988c7c05"), 
    "ID" : 4.0, 
    "SECOND_NAME" : "abc", 
    "FIRST_NAME" : "abc", 
    "ID_DISCOUNT" : {
        "ID" : 3.0, 
        "TITLE" : "abc", 
        "AMOUNT" : 5.0
    }, 
    "LOAN" : {
        "ID" : 4.0, 
        "AMOUNT" : 42750.0
    }, 
    "customer" : {
        "_id" : ObjectId("5dfb7f8f5861584bbaedf718"), 
        "ID" : 1.0, 
        "TITLE" : "abc", 
        "ADDRESS" : "abc", 
        "CITY" : "abc", 
        "ACCOUNT" : {
            "ID" : 10.0, 
            "ID_CUSTOMER" : 4.0, 
            "DATE_OPEN" : "2019-09-24 21:03:44"
        }
    }
}
{ 
    "_id" : ObjectId("5dfccc28d29876c2988c7c05"), 
    "ID" : 4.0, 
    "SECOND_NAME" : "abc", 
    "FIRST_NAME" : "abc ", 
    "ID_DISCOUNT" : {
        "ID" : 3.0, 
        "TITLE" : "abc", 
        "AMOUNT" : 5.0
    }, 
    "LOAN" : {
        "ID" : 4.0, 
        "AMOUNT" : 42750.0
    }, 
    "customer" : {
        "_id" : ObjectId("5dfb7f8f5861584bbaedf718"), 
        "ID" : 1.0, 
        "TITLE" : "abc", 
        "ADDRESS" : "abc", 
        "CITY" : "abc", 
        "ACCOUNT" : {
            "ID" : 11.0, 
            "ID_CUSTOMER" : 4.0, 
            "DATE_OPEN" : "2019-08-23 21:03:44"
        }
    }
}

与 id_customer 相同的所有帐户列表，但是...

$match: {
                    "customer.ACCOUNT.ID_CUSTOMER": "$ID" 
                }

没有做我想做的事情。

- Dmitry Sokolov

Answer 1

我找到了解决方案：使用 $expr 运算符。

db.customer.aggregate([
    {
        $match: {
            "LOAN.AMOUNT": { $gte: 41000 }
        }
    },

    {
        $lookup: {
            from: "department",
            localField: "ID",
            foreignField: "ACCOUNT.ID_CUSTOMER",
            as: "customer"
        }
    },
    { $match: { "myArray": { $ne: [] } } },
    {
        $unwind: {
            path: "$customer",
            preserveNullAndEmptyArrays: false
        }
    }
    ,
    {
        $unwind: "$customer.ACCOUNT"
    },
    { $match: { $expr: { $eq: ["$customer.ACCOUNT.ID_CUSTOMER", "$ID"] } } },

])  

- Dmitry Sokolov