Mongo DB $look up Method for Fields in Arrays instead of Collections

Question

I have a user document with the following structure:

{
    "_id": {
      "$oid": "5e636c552b872f00178033bf"
    },
    "finance": {
      "expenditure": [
        {
          "status": true,
          "_id": {
            "$oid": "5e636d442b872f00178033d4"
          },
          "amount": {
            "$numberInt": "900"
          },
          "category": "Coffee"
        },
        {
          "status": true,
          "_id": {
            "$oid": "5e636d492b872f00178033d5"
          },
          "amount": {
            "$numberInt": "1000"
          },
          "category": "Coffee"
        },
        {
          "status": true,
          "_id": {
            "$oid": "5e636d532b872f00178033d6"
          },
          "amount": {
            "$numberInt": "3000"
          },
          "category": "Sport"
        },
        {
          "status": true,
          "_id": {
            "$oid": "5e636d572b872f00178033d7"
          },
          "amount": {
            "$numberInt": "1000"
          },
          "category": "Sport"
        },

      ],
      "customcategories": [
        {
          "budget": {
            "$numberInt": "200"
          },
          "_id": {
            "$oid": "5e636c552b872f00178033c7"
          },
          "title": "Sport"
        },
        {
          "budget": {
            "$numberInt": "100"
          },
          "_id": {
            "$oid": "5e636c552b872f00178033c8"
          },
          "title": "Coffee"
        }
      ]
    }
}

My previos command is this one (you don't have to mind the status and the currentdate) :

User.aggregate([
  {
    $match: {
      _id: req.user._id
    }
  },
  {
    $unwind: "$finance.expenditure"
  },
  {
    $match: {
      "finance.expenditure.status": true
    }
  },
  {
    $sort: {
      "finance.expenditure.currentdate": -1
    }
  },
  {
    $group: {
      _id: "$finance.expenditure.category",
      amount: {
        $sum: "$finance.expenditure.amount",

      }
    }
  },
  {
    $project: {
      _id: 0,
      category: "$_id",
      amount: 1
    }
  }
])

The Result looks like this :

 {
    "expenditure": [
        {
            "amount": 1900,
            "category": "Coffee"
        },
        {
            "amount": 4000,
            "category": "Sport"
        }
    ]
}

I would like to add the my grouped elements the budget from the associated "customcategory".

So that it looks like this :

{
        "expenditure": [
            {
                "amount": 1900,
                "category": "Coffee",
                "budget" : 100
            },
            {
                "amount": 4000,
                "category": "Sport",
                "budget" : 200

            }
        ]
    }

I tried several things but nothing works of the $lookup method worked for me.

I hope some can help me :)

Answer 1

give this pipeline a try:

db.collection.aggregate([
    {
        $match: {  _id: ObjectId("5e636c552b872f00178033bf") }
    },
    {
        $unwind: "$finance.expenditure"
    },
    {
        $match: { "finance.expenditure.status": true }
    },
    {
        $sort: { "finance.expenditure.currentdate": -1 }
    },
    {
        $group: {
            _id: "$finance.expenditure.category",
            amount: { $sum: "$finance.expenditure.amount"},
            categories: { $first: '$finance.customcategories' }
        }
    },
    {
        $project: {
            _id: 0,
            category: "$_id",
            amount: 1,
            budget: {
                $arrayElemAt: [
                    {
                        $map: {
                            input: {
                                $filter: {
                                    input: '$categories',
                                    cond: { $eq: ['$$this.title', '$_id'] }
                                }
                            },
                            in: '$$this.budget'
                        }
                    },
                    0
                ]
            }
        }
    }
])

https://mongoplayground.net/p/adsWInz3wgY