MongoDB Extract document

I have this Json:

{
    "_id": {
        "$oid": "613a55dccXXXXXXX28b5ba3a1"
    },
    "user": "CHEWBACCA",
    "paymentTypes": [{
            "type": "PILOT",
            "limits": [{
                    "type": "FLIGHT_HOURS",
                    "amount": 10
                },
                {
                    "type": "COMBAT_HOURS",
                    "amount": 80000
                },
                {
                    "type": "TRAINING_HOURS",
                    "amount": 80000
                }
            ]
        },
        {
            "type": "WARRIOR",
            "limits": [{
                    "type": "FLIGHT_HOURS",
                    "amount": 22
                },
                {
                    "type": "COMBAT_HOURS",
                    "amount": 123
                },
                {
                    "type": "TRAINING_HOURS",
                    "amount": 456
                }
            ]
        }
    ],
    "createdDate": "2021-11-11"
}

I would like to filter exclusive for paymentType.type equals “PILOT” and limits equals type equals “COMBAT_HOURS”.

This is the result I would like to receive.

{
    "_id": {
        "$oid": "613a55dccXXXXXXX28b5ba3a1"
    },
    "user": "CHEWBACCA",
    "paymentTypes": [{
        "type": "PILOT",
        "limits": [{
            "type": "COMBAT_HOURS",
            "amount": 80000
        }]
    }],
    "createdDate": "2021-11-11"
}

How to extract the document to reach this result?

Answer

Query

• map paymentTypes
• if paymentType= PILOT, filter the limits to keep only the combat hours
• else null (we don’t want that paymentType)
• remove the null values (those null were paymentTypes that weren’t of type PILOT)

*this can cause empty arrays, either on paymentTypes or on limits,if you dont accept empty arrays, you can match and remove the documents with those empty arrays

PlayMongo

aggregate(
[{"$addFields": 
    {"paymentTypes": 
      {"$map": 
        {"input": "$paymentTypes",
          "in": 
          {"$cond": 
            [{"$eq": ["$$ptype.type", "PILOT"]},
              {"$mergeObjects": 
                ["$$ptype",
                  {"limits": 
                    {"$filter": 
                      {"input": "$$ptype.limits",
                        "cond": {"$eq": ["$$this.type", "COMBAT_HOURS"]}}}}]},
              null]},
          "as": "ptype"}}}},
  {"$addFields": 
    {"paymentTypes": 
      {"$filter": 
        {"input": "$paymentTypes", "cond": {"$ne": ["$$this", null]}}}}}])