node.js - 如何合并MongoDB中的两个集合: Transaction and Taxes

Question

我有 2 个集合“交易”和“税收”。

模型示例为:

Transaction
{
    "_id" : ObjectId("5cff102b4ef90140801af1e9"),
    "totalPrice" : 3.32,
    "number" : 17,
    "__v" : 0,
    "taxes" : [ 
        {
            "_id" : ObjectId("5cff104b4ef90140801af211"),
            "tax" : ObjectId("5b60ba0b6e7a8a3a101ea73a"),
            "taxAmount" : 0.21,
            "taxBase" : 1,
            "percentage" : 21
        }, 
        {
            "_id" : ObjectId("5cff104b4ef90140801af210"),
            "tax" : ObjectId("5bb1932f2db234342831f99e"),
            "taxAmount" : 0.11,
            "taxBase" : 1,
            "percentage" : 10.5
        }
    ]
}

Taxes
{
    "_id" : ObjectId("5b60ba0b6e7a8a3a101ea73a"),
    "name" : "IVA",
    "percentage" : 21
},
{
    "_id" : ObjectId("5bb1932f2db234342831f99e"),
    "name" : "IVA 10.5",
    "percentage" : 10.5
}

我想咨询，结果给我回复

{
    "_id" : ObjectId("5cff102b4ef90140801af1e9"),
    "totalPrice" : 3.32,
    "number" : 17,
    "__v" : 0,
    "taxes" : [ 
        {
            "_id" : ObjectId("5cff104b4ef90140801af211"),
            "tax" : {
                "_id" : ObjectId("5b60ba0b6e7a8a3a101ea73a"),
                "name" : "IVA",
                "percentage" : 21
            },
            "taxAmount" : 0.21,
            "taxBase" : 1,
            "percentage" : 21
        }, 
        {
            "_id" : ObjectId("5cff104b4ef90140801af210"),
            "tax" : {
                "_id" : ObjectId("5bb1932f2db234342831f99e"),
                "name" : "IVA 10.5",
                "percentage" : 10.5
            }
            "taxAmount" : 0.11,
            "taxBase" : 1,
            "percentage" : 10.5
        }
    ]
}

到目前为止我的查询

db.getCollection('transactions').aggregate(
[{
        "$match": {
            "_id": ObjectId("5cff102b4ef90140801af1e9")
        }
    },
    {
        "$lookup": {
            "from": "taxes",
            "let": {
                "pid": "$taxes.tax"
            },
            "pipeline": [{
                "$match": {
                    "$expr": {
                        "$in": ["$_id", "$$pid"]
                    }
                }
            }],
            "as": "taxes"
        }
    }
])

但是我的结果是

{
    "_id" : ObjectId("5cff102b4ef90140801af1e9"),
    "totalPrice" : 3.32,
    "number" : 17,
    "__v" : 0,
    "taxes" : [ 
        {
            "_id" : ObjectId("5bb1932f2db234342831f99e"),
            "name" : "IVA 10.5",
            "percentage" : 10.5
        }, 
        {
            "_id" : ObjectId("5bb1932f2db234342831f99e"),
            "name" : "IVA 10.5",
            "percentage" : 10.5
        }
    ]
}

它没有给我带来财政模型之外的字段，“taxAmount”，“taxBase”和“percentage”。为什么会发生这种情况并且我没有得到聚合中关系的关系

Answer 1

您需要使用 $map 合并两个数组与 $filter 。要组合这两个对象，您可以使用 $mergeObjects由于 $filter 返回一个数组，您可以使用 $arrayElemAt获得第一个项目。尝试:

db.transaction.aggregate([
    {
        "$match": {
            "_id": ObjectId("5cff102b4ef90140801af1e9")
        }
    },
    {
        $lookup: {
            from: "taxes",
            localField: "taxes.tax",
            foreignField: "_id",
            as: "taxDetails"
        }
    },
    {
        $addFields: {
            taxes: {
                $map: {
                    input: "$taxes",
                    as: "t",
                    in: {
                        $mergeObjects: [
                            "$$t",
                            {
                                tax: {
                                    $arrayElemAt: [
                                        {
                                            $filter: {
                                                input: "$taxDetails",
                                                as: "td",
                                                cond: {
                                                    $eq: [ "$$td._id", "$$t.tax" ]
                                                }
                                            }
                                        }, 0
                                    ]
                                }
                            }
                        ]
                    }
                }
            }
        }
    },
    {
        $project: {
            "taxDetails": 0
        }
    }
])

Mongo Playground