How to find array's object property(string) by its numeric value in mongodb query?

user_18052928 0
Viewed 68

I have a restaurant collection with its documents formed like this one:

{
        "address": {
            "building": "1007",
            "coord": [
                -73.856077,
                40.848447
            ],
            "street": "Moris Park Ave",
            "zipcode": "10462"
        },
        "borough": "Bronx",
        "cuisine": "Bakery",
        "grades": [
            {
                "date": {
                    "$date": 1393804800000
                },
                "grade": "A",
                "score": "81"
            },
            {
                "date": {
                    "$date": 1378857600000
                },
                "grade": "A",
                "score": "6"
            },
            {
                "date": {
                    "$date": 1358985600000
                },
                "grade": "A",
                "score": "99"
            },
            {
                "date": {
                    "$date": 11322006400000
                },
                "grade": "B",
                "score": "14"
            },
            {
                "date": {
                    "$date": 1288715200000
                },
                "grade": "B",
                "score": "14"
            }
        ],
        "name": "Morris Park Bake Shop"
        
    }

My homework asked me to find any restaurants having score from 80 to 100 and I do this

db.restaurants.find({ $expr: {$and: [{$gt: [ { $toInt: "$grades.score" }, 80 ]}, {$lt: [ { $toInt: "$grades.score" }, 100 ]}] } }).pretty()

And received "Executor error during find command :: caused by :: Unsupported conversion from array to int in $convert with no onError value". I try

db.restaurants.find({ $expr: {$and: [{$gt: [ { $toInt: "$grades.$score" }, 80 ]}, {$lt: [ { $toInt: "$grades.$score" }, 100 ]}] } }).pretty()

And this returned:"FieldPath field names may not start with '$'. Consider using $getField or $setField." Then i try

db.restaurants.find({$and:[{'grades.score': {$gt: 80}}, {'grade.score':{$lt:100}}]}).collation({locale:'en_US' ,numericOrdering: true})

And that returned nothing. It has to return at least the document i mentioned above, right?.

mongodb-query mongodb arrays
1 Answers

Perhaps this homework is about learning proper field value types or collation. With collation, numericOrdering can be used as commented by @prasad_. If score is truly numeric, for several reasons it's best to store it as numeric.

Unfortunately there doesn't seem to be a way at this time to specify a collation with mongoplayground.net. Without using a collation, there are many ways to achieve your desired output. Here's one way.

db.collection.aggregate([
  {
    // make grades.score numeric
    "$set": {
      "grades": {
        "$map": {
          "input": "$grades",
          "as": "grade",
          "in": {
            "$mergeObjects": [
              "$$grade",
              { "score": { "$toDecimal": "$$grade.score" } }
            ]
          }
        }
      }
    }
  },
  {
    "$match": {
      "grades.score": {
        "$gt": 80,
        "$lt": 100
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "name": 1
    }
  }
])

Try it on mongoplayground.net.

user_14093740
Related