mongoDB selecting record based on two conditions

Question

A sample record in my database looks like :

{
    "_id" : 2,
    "name" : "Corliss Zuk",
    "scores" : [
            {
                    "type" : "exam",
                    "score" : 87.53859552156015
            },
            {
                    "type" : "quiz",
                    "score" : 24.49132971110967
            },
            {
                    "type" : "homework",
                    "score" : 99.24881912510654
            }
    ]

}

I am trying to select all records that have homework scores > 90 or exam scores < 50. My OR condition is like this:

 DBObject clause1 = new BasicDBObject("scores.type", "homework").append("scores.score", new BasicDBObject("$gt", 90)); 
    DBObject clause2 = new BasicDBObject("scores.type", "exam").append("scores.score", new BasicDBObject("$lt", 50));    
    BasicDBList or = new BasicDBList();
    or.add(clause1);
    or.add(clause2);
    DBObject query = new BasicDBObject("$or", or);

This is picking up all records where the score is > 90 or < 50 but not relating the condition that the score being examined should be either a homework score or an exam score. I am missing a condition that will relate the scores to the type and then examine their value.

Any help would be much appreciated.

Thanks, John

Answer 1

DBObject clause1 = new BasicDBObject("scores", new BasicDBObject("$elemMatch", new BasicDBObject("type", "homework").append("score", new BasicDBObject("$gt", 90))));
DBObject clause2 = new BasicDBObject("scores", new BasicDBObject("$elemMatch", new BasicDBObject("type", "exam").append("score", new BasicDBObject("$lt", 50))));

Use it this way.