Is there a way to return specific nested fields fr

问题:

Is there a way to get the amount of money paid per Sport's category? It can't be restricted only to "John," but in all collection documents.

Expected query return for data provided:

{
  {"Fight":   [100,95] },
  {"Running": [50]     }
}

Example of data:

{"_id":"5z621578b0ce483b9866fb1f",
 "Name":"John",
 "Sports":[
           {"Category":"Fight",
            "Billing":[
                       {"Month":"Jan",
                        "Status":"Paid",
                        "Price":100},
                      {"Month":"Feb",
                       "Status":"Not Paid",
                       "Price":125}, 
                      {"Month":"Mar",
                       "Status":"Paid",
                       "Price":95}      
                      ]
           },

          {"Category":"Running",
           "Billing":[
                      {"Month":"Jan",
                       "Status":"Not Paid",
                       "Price":200}, 
                      {"Month":"Feb",
                       "Status":"Paid",
                       "Price":50}  
                     ]
          }
      ]
}

In other words: I need to compare the billing Status inside every nested object and check if it's"Paid", then if true, add the respective billing object Price to the respective Sport's Category array.

For all documents in the collection, with multiple sport's categories and multiple billing's month. But always same nested structure.

Thank you in advance!

回答1:

As willis said in his comment, you will want to use aggreagation: https://docs.mongodb.com/manual/aggregation/

---

The following aggregation will give you the data that you are looking for (replace billings with the actual name of your collection):

db.billings.aggregate([
    { $unwind: '$Sports'},
    { $unwind: '$Sports.Billing'},
    { $match: { 'Sports.Billing.Status': 'Paid' } },
    {
        $group: {
            _id: '$Sports.Category',
            Category: {$first: '$Sports.Category'},
            Prices: { $push: '$Sports.Billing.Price' }
        }
    },
    { $project: {_id: 0} }
]);

The result of this aggregation will look something like this:

[
    {
        "Category" : "Running",
        "Prices" : [ 
            50.0
        ]
    },
    {
        "Category" : "Fight",
        "Prices" : [ 
            100.0, 
            95.0
        ]
    }
]

---

The exact format that you requested in your question is a bit abnormal; in my opinion, I think it would be better to keep it in the form the aggregation above outputs. But if you want it in a form like the one in your question, the aggregation is a bit more complex:

db.billings.aggregate([
    { $unwind: '$Sports'},
    { $unwind: '$Sports.Billing'},
    { $match: { 'Sports.Billing.Status': 'Paid' } },
    {
        $group: {
            _id: '$Sports.Category',
            Prices: { $push: '$Sports.Billing.Price' }
        }
    },
    {
        $group: {
            _id: 0,
            Sports: { $push: { Category: '$_id', Prices: '$Prices' } }
        }
    },
    {
        $project: {
            Sports: {
                $arrayToObject: {
                    '$map': {
                        'input': '$Sports',
                        'as': 'el',
                        'in': {
                            'k': '$$el.Category',
                            'v': '$$el.Prices'
                        }
                    }
                }
            }
        }
    },
    { $replaceRoot: { newRoot: '$Sports'} }
]);