mongodb aggregation compare documents and i don't want to show incompatible ones

Question

I have three documents. First one is percentages, the others discardeditems and filtereditems. Sample data of these documents are as follows.

db = {
  "percentages": [
    {
      "_id": 1,
      "base": "A",
      "buy": "BUY_1",
      "sell": "SELL_1",
      "item": "ITEM_B",
      "ask": 100,
      "bid": 114,
      "percentage": 14
    },
    {
      "_id": 2,
      "base": "B",
      "buy": "BUY_2",
      "sell": "SELL_2",
      "item": "ITEM_G",
      "ask": 50,
      "bid": 90,
      "percentage": 80
    },
    {
      "_id": 3,
      "base": "A",
      "buy": "BUY_2",
      "sell": "SELL_2",
      "item": "ITEM_G",
      "ask": 10,
      "bid": 15,
      "percentage": 50
    }
  ],
  "discardeditems": [
    {
      "_id": 1,
      "buy": "BUY_1",
      "sell": "SELL_1",
      "item": "ITEM_B"
    },
    {
      "_id": 2,
      "buy": "BUY_2",
      "sell": "SELL_2",
      "item": "ITEM_G"
    }
  ],
  "filtereditems": [
    {
      "_id": 2,
      "buy": "BUY_2",
      "sell": "SELL_2",
      "item": "ITEM_G",
      "percentage": "55"
    }
  ]
}

Actually, I want to compare percentages document with discardeditems and filtereditems documents. If the buy, sell and item values in percentages document are equal to those in discardeditems document, I would like to add isdiscardeditem:true to percentages document.

And if the buy, sell, and item values in filtereditems are equal to those in percentages document and the percentage value in filtereditems document is greater than in percentages document, I no longer want to show this record.

The final version of the data I want to see should be as follows;

{
  "_id": 1,
  "base": "A",
  "buy": "BUY_1",
  "sell": "SELL_1",
  "item": "ITEM_B",
  "ask": 100,
  "bid": 114,
  "percentage": 14,
  "isdiscarded": true
},
{
  "_id": 2,
  "base": "B",
  "buy": "BUY_2",
  "sell": "SELL_2",
  "item": "ITEM_G",
  "ask": 50,
  "bid": 90,
  "percentage": 80,   
  "isdiscarded": true   
}

Percentages document count was three. But now i want to show two record. The other record of percentages document must not come because of the less than the percentage in filtereditems.

I can add isdiscardeditem using $lookup, $match $addFields keyword but I have not been successful in showing two record. https://mongoplayground.net/p/_XZoqGTnIyz

How can I write?

Answer 1

You can try below aggregation:

db.percentages.aggregate([
    {
        $lookup: {
            from: "discardeditems",
            let: {
                item_src: "$item",
                buy_src: "$buy",
                sell_src: "$sell"
            },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: [ "$$item_src", "$item" ] },
                                { $eq: [ "$$buy_src", "$buy" ] },
                                { $eq: [ "$$sell_src", "$sell" ] },
                            ]
                        }
                    }
                }
            ],
            as: "discarded"
        }
    },
    {
        $lookup: {
            from: "filtereditems",
            let: {
                item_src: "$item",
                buy_src: "$buy",
                sell_src: "$sell",
                percentage_src: "$percentage"
            },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { $eq: [ "$$item_src", "$item" ] },
                                { $eq: [ "$$buy_src", "$buy" ] },
                                { $eq: [ "$$sell_src", "$sell" ] },
                                { $lt: [ "$$percentage_src", { $toInt: "$percentage" } ] }
                            ]
                        }
                    }
                }
            ],
            as: "filtered"
        }
    },
    {
        $match: {
            filtered: { $eq: [] }
        }
    },
    {
        $addFields: {
            isdiscarded: { $gt: [ { $size: "$discarded" }, 0 ] }
        }
    },
    {
        $project: {
            discarded: 0,
            filtered: 0
        }
    }
])

Please note that percentage fields have to have the same type so $toInt is needed for conversion.

Mongo Playground