需要返回超过 60 的分数总和，其中 is score 是数组 JSON 对象中的一个元素

Question

我在MongoDB集合中的文档如下所示，JSON对象的数组等级的长度各不相同：

{
"address": {
    "building": "1007",
    "coord": [-73.856077, 40.848447],
    "street": "Morris Park Ave",
    "zipcode": "10462"
},
"borough": "Bronx",
"cuisine": "Bakery",
"grades": [{
    "date": {
        "$date": 1393804800000
    },
    "grade": "A",
    "score": 2
}, {
    "date": {
        "$date": 1378857600000
    },
    "grade": "A",
    "score": 6
}, {
    "date": {
        "$date": 1358985600000
    },
    "grade": "A",
    "score": 10
}, {
    "date": {
        "$date": 1322006400000
    },
    "grade": "A",
    "score": 9
}, {
    "date": {
        "$date": 1299715200000
    },
    "grade": "B",
    "score": 14
}],
"name": "Morris Park Bake Shop",
"restaurant_id": "30075445"
}
{
"address": {
    "building": "469",
    "coord": [-73.961704, 40.662942],
    "street": "Flatbush Avenue",
    "zipcode": "11225"
},
"borough": "Brooklyn",
"cuisine": "Hamburgers",
"grades": [{
    "date": {
        "$date": 1419897600000
    },
    "grade": "A",
    "score": 8
}, {
    "date": {
        "$date": 1404172800000
    },
    "grade": "B",
    "score": 23
}, {
    "date": {
        "$date": 1367280000000
    },
    "grade": "A",
    "score": 12
}, {
    "date": {
        "$date": 1336435200000
    },
    "grade": "A",
    "score": 12
}],
"name": "Wendy'S",
"restaurant_id": "30112340"
}

任务是编写一个MongoDB查询来返回分数总和大于60的集合。

Answer 1

这是

如何完成的：

db.collection.aggregate({
    $unwind: "$grades" // flatten the "grades" array
}, {
    $group: {
        "_id": "$_id",
        "sumOfScores": { // calculate the sum of all grades for all documents with the same "_id"
            $sum: "$grades.score"
        },
        "docs": {
            $push: "$$ROOT" // remember all affected documents per group
        }
    }
}, {
    $match: {
        "sumOfScores": {
            $gt: 60 // filter out everything that we don't care about
        }
    }
}, {
    $unwind: "$docs" // flatten the "docs" array
},  {
    $group: { // restore the original document structure
        "_id": "$docs._id",
        "address": { $first: "$docs.address" },
        "borough": { $first: "$docs.borough" },
        "cuisine": { $first: "$docs.cuisine" },
        "grades": {
            $push: "$docs.grades"
        },
        "name": { $first: "$docs.name" },
        "restaurant_id": { $first: "$docs.restaurant_id" }
    }
})