MongoDB Extract document-CodePudding

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 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?

CodePudding user response：

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

Test code here

aggregate(
[{"$set": 
    {"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"}}}},
  {"$set": 
    {"paymentTypes": 
      {"$filter": 
        {"input": "$paymentTypes", "cond": {"$ne": ["$$this", null]}}}}}])