'MongoDB Aggregation - get docs where property value is of certain value inside another collection

I am new to mongo and therefore having a hard time achieving what I want to do. I try to explain as best as I can so hopefully you will understand my question.

I have an aggregation like this running a collection named TokenBalance where the documents inside have this structure

{"address": string, "balance": [array of balance objects]}

 [
  {
    $sort: {
      updatedAt: -1
    }
  },
  {
    $group: {
      _id: "$address",
      balance: {
        $last: "$balance"
      }
    }
  },
  {
    $unwind: {
      path: "$balance"
    }
  },
  {
    $group: {
      _id: "$balance.symbol",
      count: {
        $sum: {
          $toDecimal: "$balance.balance"
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      symbol: "$_id",
      total: "$count"
    }
  }
];

which is almost what I am trying to do. The output of this looks like this

{
 symbol:"Playboy"
 total:10193456759513379777140001203028
}

What I usually wanted tho, is to only get the TokenBalance documents where the TokenBalance.address is inside another collection _AddressSyncStatus where AddressSyncStatus.value is greater than numberX

This is how a document of _AddressSyncStatus looks like

{
 "address": "some string address", "value": someNumber
}

So basically, get all TokenBalance where TokenBalance.address is inside _AddressSyncStatus and its value is greater than whatever I specify.

From there on I would then add the stages I already managed to code.

What I don't know and don't seem to get to work, is how to add a $lookup (if that is what I need for that) and where to add that.

My last try was adding these as the first stages which I initially thought work.

    {
        '$lookup': {
            'from': '_AddressSyncStatus', 
            'localField': 'address', 
            'foreignField': 'address', 
            'as': 'synced_address'
        }
    }, {
        '$unwind': {
            'path': '$synced_address'
        }
    }, {
        '$match': {
            'synced_address.value': {
                '$lt': 1
            }
        }
    }

The issue with this is, that AddressSyncStatus has documents with a value of for example 0.2 or 0.4555 etc.

when I put $lt: 1 it is not returning any documents. If I put int $gte:1 it shows a whole bunch of documents where AddressSyncStatus.value is bigger than 1.

Not sure why its not working with $lt or $lte when AddressSyncStatus has values less than that.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source