$mod only supports numeric types, not array and int in MongoDb

Question

I have this document structure in my collection:

{
  "grades": [
        { "grade": "A", "score": 8 },
        {"grade": "A", "score": 7 }
    ]
}

I need to filter for those documents when their score is divided by 7 has 0 as a reminder. Original question is asked here (question 30). Their solution is simple:

db.restaurants.find(
                  {"grades.score" :
                     {$mod : [7,0]}
                  },
                     {"restaurant_id" : 1,"name":1,"grades":1}
                );

However, in order to practice, I wanted to to my own solution, which doesn't work out.

db.restaurants.aggregate([
    {
        "$match": {
           "$expr": {"$in": [0,   [ { $mod: [ "$grades.score", 7 ] }    ]  ]}
        }
    },
])

The error is:

PlanExecutor error during aggregation :: caused by :: $mod only supports numeric types, not array and int

Basically I want to check if 0 is in custom made array which will contain only single value after modulo operation, as explained in the docs for $in when used in aggregation.

Is there a way to make $mod work as part of aggregation?

I see that similar question is asked here, but it is only referencing first score in grades array not all of them. So in my case I should see example document given on the beginning, since 7 % 7 = 0

Worth noting that { "grades.score" : { $mod : [7,0] } } is better since you don't have to actually write any loop logic or current value steps, etc. My answers below were wrt "I wanted to to my own solution". — aneroid
– aneroid, Commented Mar 27, 2024 at 11:54

aneroid · Accepted Answer · 2024-03-27 10:08:27Z

2

Use the $map function to map each value of the array to the modulo result, and then check for 0 being in the resulting array.

You can use one of these:

db.restaurants.aggregate([
  {
    $set: {
      mods: {
        "$map": {
          "input": "$grades.score",
          "as": "score",
          "in": {
            $mod: ["$$score", 7]
          }
        }
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$in": [0, "$mods"]
      }
    }
  }
])

Mongo Playground

And if you want that as a single stage within the pipeline:

db.restaurants.aggregate([
  {
    "$match": {
      "$expr": {
        "$in": [
          0,
          {
            "$map": {
              "input": "$grades.score",
              "as": "score",
              "in": {
                $mod: ["$$score", 7]
              }
            }
          }
        ]
      }
    }
  }
])

Mongo Playground

answered Mar 27, 2024 at 10:08

aneroid

16.7k3 gold badges42 silver badges77 bronze badges

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

3 Comments

Wrapper Over a year ago

Would it be correct to say that second query is more efficient because of the possibility to use indexes?

aneroid Over a year ago

No, I wouldn't say that. You can use indexes in both. Mongo doesn't have purely functional loops for $anyElementTrue or $all which shortcut or "exit early". Both require the array to fully exist first anyway. Also, calculating the mods as a separate field first vs within the $match doesn't provide much benefit. At most, if you were iterating over results, they would produce each batch sooner but the total time would be the same.

aneroid Over a year ago

With indexes, you would need to know which index has a 0-modulo result anyway, so you still need to calculate all of them. In theory, you could use the $reduce function to do fewer "calculations" but it would still iterate over the whole array.

aneroid · Accepted Answer · 2024-03-27 11:45:52Z

Here's an alternative with $reduce which may skip some calculations when a 0 modulo is found but would still iterate over the whole array anyway. In this case, since the modulo calculation is very efficient, it's unlikely to actually give any real benefit unless you had very very long lists of grades.

db.restaurants.aggregate([
  {
    $set: {
      final_modulo: {
        $reduce: {
          input: "$grades.score",
          initialValue: 1,
          in: {
            $cond: {
              if: { $eq: [ "$$value", 0] },
              // in theory, once we have a 0
              // don't calculate mods or the minimums any more
              then: "$$value",
              else: { $mod: ["$$this", 7] }
            }
          }
        }
      }
    }
  },
  { $match: { final_modulo: 0 } }
])

Mongo Playground with more example data

Also I skipped the { $min: [{ $mod: ["$$this", 7] }, "$$value"] } since we're only checking for zero vs non-zero remainder. And in case scores are negative, the modulo minimum would be negative:

The $mod expression produces a negative result when the dividend is negative.

Collectives™ on Stack Overflow

$mod only supports numeric types, not array and int in MongoDb

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related