How to merge two collections in MongoDB: Transaction and Taxes

I have 2 collections Transaction and Taxes.

Example the models are:

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
}

I want to consult and as a result, return me

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

My query is so far

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

But my result is

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

It does not bring me the fields outside the fiscal model, "taxAmount", "taxBase" and "percentage". Why this happens and I do not get the relationship of the relationship in the agregate

1 answer

  • answered 2019-06-12 04:09 mickl

    You need to merge two arrays using $map with $filter. To combine both objects you can use $mergeObjects and since $filter returns an array you can use $arrayElemAt to get the first item. Try:

    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