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? — Sede
– Sede, Commented May 15, 2016 at 17:42
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
– Neil Lunn, Commented Jul 1, 2017 at 8:54

logoff · Accepted Answer · 2017-09-30 11:45:21Z

1

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 }

edited Sep 30, 2017 at 11:45

logoff

3,4525 gold badges46 silver badges63 bronze badges

answered May 15, 2016 at 20:07

novice_2

5051 gold badge5 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Calculus5000 Over a year ago

Tried this out and the value for remainder seems to come up as null for all the documents

Sede Over a year ago

@Calculus5000 you should really consider to edit your question and add additional informations if you really need help. See my comment above

Sede Over a year ago

Please stop posting an image. You should consider to copy/past your code.

novice_2 Over a year ago

@ user3100115 thanks, I did it, initially I found it very problematic to put all codes together, but now I think I got it.

Community · Accepted Answer · 2017-05-23 12:23:56Z

0

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

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered May 15, 2016 at 21:57

Atchoum

73310 silver badges16 bronze badges

Collectives™ on Stack Overflow

MongoDB: $mod operator in aggregation pipeline

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related