MongoDB: $mod operator in aggregation pipeline

0
votes

I have a restaurants collection that contains 3772 documents and I am trying to calculate the total number of documents that contain a score in first element of the grades array that's a multiple of 7 using the aggregation framework.

Query:

db.restaurants.aggregate([
{$project: {remainder: {$mod: ["$grades.0.score", 7]},
            restaurant_id: 1,
            name: 1,
            grades: 1
            }
},
{$match: {remainder: {$eq: 0}}},
{$group: {_id: null, total: {$sum: 1}}}
])

However, I am getting an error message that's caused by the use of the $mod operator in the $project pipeline stage. The error message is the following:

$mod only supports numeric types, not Array and NumberDouble

However, both $grades.0.score and 7 are integers, right? What should I change to make this query work as intended?

Example document:

{
"_id" : ObjectId("57290430139a4a37132c9e93"),
"address" : {
    "building" : "469",
    "coord" : [
        -73.961704,
        40.662942
    ],
    "street" : "Flatbush Avenue",
    "zipcode" : "11225"
},
"borough" : "Brooklyn",
"cuisine" : "Hamburgers",
"grades" : [
    {
        "date" : ISODate("2014-12-30T00:00:00Z"),
        "grade" : "A",
        "score" : 8
    },
    {
        "date" : ISODate("2014-07-01T00:00:00Z"),
        "grade" : "B",
        "score" : 23
    },
    {
        "date" : ISODate("2013-04-30T00:00:00Z"),
        "grade" : "A",
        "score" : 12
    },
],
"name" : "Wendy'S",
"restaurant_id" : "30112340"
}
2
mongodbmongodb-queryaggregation-frameworknosql
Do you need $mod for only first element in the "grade" array? Can you show the expected output? – styvane
Was always "remainder": { "$mod": [ { "$arrayElemAt": [ "$grades.score", 0 ] }, 7 ] }. See $arrayElemAt. It also makes no sense to project fields that will only be discarded in a later $group. And the expression should be $redact, rather than $project and then $match. Much more efficient. – Neil Lunn

2 Answers

1
votes

instead of $grades.0.score
put $grades[0].score in your query.

the above is wrong. see below the correct form. As you want to filter by grades whose first score is a multiple of 7, you aggregation should start like this.

db.restaurants.aggregate([{$match: {"grades.0.score": {$mod: [7, 0]}}},{$group: {_id: null, total: {$sum: 1}}}])

I changed the grade.0.score to 7 and ran the command to check it is working or not, it seems it is working as you wanted.

> db.restaurants.find().pretty();
{
        "_id" : 0,
        "address" : {
                "building" : "469",
                "coord" : [
                        -73.961704,
                        40.662942
                ],
                "street" : "Flatbush Avenue",
                "zipcode" : "11225"
        },
        "borough" : "Brooklyn",
        "cuisine" : "Hamburgers",
        "grades" : [
                {
                        "date" : ISODate("2014-12-30T00:00:00Z"),
                        "grade" : "A",
                        "score" : 7
                },
                {
                        "date" : ISODate("2014-07-01T00:00:00Z"),
                        "grade" : "B",
                        "score" : 23
                },
                {
                        "date" : ISODate("2013-04-30T00:00:00Z"),
                        "grade" : "A",
                        "score" : 12
                }
        ],
        "name" : "Wendy'S",
        "restaurant_id" : "30112340"

> db.restaurants.aggregate([{$match: {"grades.0.score": {$mod: [7, 0]}}},{$group:{_id:null,count:{$sum:1}}} ])    
{ "_id" : null, "count" : 1 }
0
votes

First: why doesn't it work? Try:

    db.restaurants.aggregate([
    {$project: {
        score0: "$grades.0.score",
        restaurant_id: 1,
        name: 1
        }
    }
    ])

You'll see that score0 returns [0 elements] so it does output an array hence the error message.

Based on this other question Get first element in array and return using Aggregate? (Mongodb), here is a solution to your problem:

    db.restaurants.aggregate([
       {$unwind: "$grades"},
       {$group:{"_id":"$_id","grade0":{$first:"$grades"}}},
       {$project: {
          remainder: {$mod: ["$grade0.score", 7]},
          restaurant_id: 1,
          name: 1,
          grade0: 1,
          }
      },
      {$match: {remainder: {$eq: 0}}},
      {$group: {_id: null, total: {$sum: 1}}}
    ])