Query over a field which is deep inside MongoDB collection using array of possible options-CodePudding

I have a list of scenarioDocument collection in MongoDB where each has the following structure:

{
    "scenarioName": "test scenario",
    "outputs": {
        "allocation": [{
            "name": "Loan USA",
            "allocatedAmount": [{
                "fund": "INFLOW",       // <--------HERE
                "usdAmount": 80000000,
            },{
                "fund": "STACK_MONEY",  // <--------HERE
                "usdAmount": 50000000,
            },{
                "fund": "ABK3T",        // <--------HERE
                "usdAmount": 150000000,
            }]
        }]
    },
    "lastUpdateDate": {
        "$date": "2020-06-24T04:00:00.000Z"
    },
    "lastUpdatedBy": "michealJackson",
}

I have to write a MongoDB query passing a list of fund names as input. And if the document from the DB has any of the funds from the list I've sent in the allocatedAmound list, the document should be present in the returned list of documents.

Eg: I pass list ['CASH_SHEET', 'STACK_MONEY', 'BALANCE_SHEET'], the above document should be present because it has a document with a fund 'STACK_MONEY'.

How to write the query?

CodePudding user response：

db.collection.aggregate([
  {
    $match: {
      "outputs.allocation.allocatedAmount.fund": {
        $in: [
          "CASH_SHEET",
          "STACK_MONEY",
          "BALANCE_SHEET"
        ]
      }
    }
  }
])

mongoplayground