Query to Match on nth Document of an Array

I am new to MongoDB and I am doing some exercises on it. In particular I got stuck on this exercise, of which I report here the question:

Given the following structure for document "Restaurant":

{
        "_id" : ObjectId("5704adbc2eb7ebe23f582818"),
        "address" : {
                "building" : "1007",
                "coord" : [
                        -73.856077,
                        40.848447
                ],
                "street" : "Morris Park Ave",
                "zipcode" : "10462"
        },
        "borough" : "Bronx",
        "cuisine" : "Bakery",
        "grades" : [
                {
                        "date" : ISODate("2014-03-03T00:00:00Z"),
                        "grade" : "A",
                        "score" : 2
                },
                {
                        "date" : ISODate("2013-09-11T00:00:00Z"),
                        "grade" : "A",
                        "score" : 6
                },
                {
                        "date" : ISODate("2013-01-24T00:00:00Z"),
                        "grade" : "A",
                        "score" : 10
                },
                {
                        "date" : ISODate("2011-11-23T00:00:00Z"),
                        "grade" : "A",
                        "score" : 9
                },
                {
                        "date" : ISODate("2011-03-10T00:00:00Z"),
                        "grade" : "B",
                        "score" : 14
                }
        ],
        "name" : "Morris Park Bake Shop",
        "restaurant_id" : "30075445"
}

Write a MongoDB query to find the restaurant Id, name and grades for those restaurants where 2nd element of grades array contains a grade of "A" and score 9 on an ISODate "2014-08-11T00:00:00Z".

I wrote this query:

db.restaurants.find(
{
    'grades.1': {
        'score': 'A',
        'grade': 9,
        'date' : ISODate("2014-08-11T00:00:00Z")
    }
},
{
    restaurant_id: 1,
    name: 1,
    grades: 1
});

which is not working. The solution provided is the following:

db.restaurants.find( 
    { "grades.1.date": ISODate("2014-08-11T00:00:00Z"), 
      "grades.1.grade":"A" , 
      "grades.1.score" : 9
    }, 
    {"restaurant_id" : 1,"name":1,"grades":1}
);

My questions are:

  1. is there a way to write the query avoiding to repeat the grades.1 part?
  2. Why is my query wrong, given that grades.1 is a document object?

If it can help answering my question, I am using MongoDB shell version: 3.2.4

EDIT:

I found an answer to question 2 thanks to this question.

In particular I discovered that order matters. Indeed, if I perform the following query, I get a valid result:

db.restaurants.find({'grades.1': {'date': ISODate("2014-08-11T00:00:00Z"), 'grade':'A', score:9}}, {restaurant_id:1, name:1, grades:1})

Note that this query works only because all subdocument's "fields" are specified, and they are specified in the same order.

Answers


Not really. But perhaps an explanation of what you "can" do:

  db.junk.find({
    "grades": { 
      "$elemMatch": {
        "date" : ISODate("2014-03-03T00:00:00Z"),
        "grade" : "A",
        "score" : 2
      }
    },
    "$where": function() {
      var grade = this.grades[0];
      return (
        grade.date.valueOf() == ISODate("2014-03-03T00:00:00Z").valueOf() &&
        grade.grade === "A" &&
        grade.score ==== 2
      )
    }
  })

The $elemMatch allows you to shorten a little, but it is not the "nth" element of the array. In order to narrow that further you need to use the $where clause to inspect the "nth" array element to see if all values are a match.

  db.junk.aggregate([
    { "$match": {
      "grades": { 
        "$elemMatch": {
          "date" : ISODate("2014-03-03T00:00:00Z"),
          "grade" : "A",
          "score" : 2
        }
      }
    }},
    { "$redact": {
      "$cond": {
        "if": {
          "$let": {
            "vars": { "grade": { "$arrayElemAt": [ "$grades", 0 ] } },
            "in": {
              "$and": [
                { "$eq": [ "$grade.date", ISODate("2014-03-03T00:00:00Z") ] },
                { "$eq": [ "$grade.grade", "A" ] },
                { "$eq": [ "$grade.score", 2 ] }
              ]
            }
          }
        },
        "then": "$$KEEP",
        "else": "$$PRUNE"
      }
    }}
  ])

You can do the same logic with $redact as well using .aggregate(). It runs a little quicker, but the basic truth should be clear by now.

So using "dot notation" to specify the "nth" position for each element within the array like you have already done is the most efficient and "brief" way to write this. You cannot make it shorter or better.

Your other attempt is looking for a "document" within "grades.1" that matches exactly the document condition you are providing. If for any reason those are not the only fields present, or if they are indeed in "different order" in the stored document, then such a query condition will not be a match.


Need Your Help

http://dbpedia.org/sparql endpoint not reliable?

sparql

Sometimes a query works sometimes it doesn't. I get sometimes "Virtuoso S1T00 Error SR171: Transaction timed out" (no timeout is set or a big timeout is set - so this is not the problem there is an...

How to select questions which have no answers, in sqlalchemy

python sqlalchemy

I've two classes: Question and Answer. A question may have 0 or many answers.