MongoDB: $mod operator in aggregation pipeline

Question

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"
}

Do you need `$mod` for only first element in the "grade" array? Can you show the expected output? — styvane, May 15 '16 at 17:42
Was always `"remainder": { "$mod": [ { "$arrayElemAt": [ "$grades.score", 0 ] }, 7 ] }`. See [`$arrayElemAt`](https://docs.mongodb.com/manual/reference/operator/aggregation/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, Jul 01 '17 at 08:54

score 1 · Answer 1 · edited Sep 30 '17 at 11:45

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 }

Tried this out and the value for `remainder` seems to come up as `null` for all the documents — Calculus5000, May 15 '16 at 20:42
@Calculus5000 you should really consider to edit your question and add additional informations if you really need help. [See my comment above](https://stackoverflow.com/questions/37241080/mongodb-mod-operator-in-aggregation-pipeline#comment62012113_37241080) — styvane, May 15 '16 at 21:20
Please stop posting an image. You should consider to copy/past your code. — styvane, May 16 '16 at 09:08
@ user3100115 thanks, I did it, initially I found it very problematic to put all codes together, but now I think I got it. — amitava, May 16 '16 at 12:03

score 0 · Answer 2 · edited May 23 '17 at 12:23

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}}}
    ])

MongoDB: $mod operator in aggregation pipeline

2 Answers2