mongodb - query element of nested arrays

Question

Data & Question

Given a collection of horse race meetings (db.sectionals), how do I write the query so that it returns the arrays (i.e. the horses) whose position: [2] ?

[
  {
    "race": {
      "date": ["2013-05-08"],
      "meeting": ["canterbury park"],
      "weather": ["fine"],
      "field": {
        "Belladini": {
          "position": [2],
          "distance": [1100]
        },
        "Bobs Destiny": {
          "position": [7],
          "distance": [1100]
        }
      }
    }
  },
  {
    "race": {
      "date": ["2013-03-27"],
      "meeting": ["canterbury park"],
      "weather": ["fine"],
      "field": {
        "Berna": {
          "position": [5],
          "distance": [1550]
        },
        "Excited Prince": {
          "position": [2],
          "distance": [1550]
        }
      }
    }
  }
]

Desired result

In this exmaple I would like it to return something like

       "Belladini": {
          "position": [2],
          "distance": [1100]
        },
       "Excited Prince": {
          "position": [2],
          "distance": [1550]
        }

Attempts

I've found the the documentation on nested arrays and finding elements of arrays a bit confusing, and have tried various attempts, such as

db.Sectionals.find({ "race.field.position": 2} )

db.Sectionals.find({ "race.field": { $elemMatch: { position: 2} } } )

db.Sectionals.find({ "race.field": {$all: [{"position": 2}] }} )

without success.

Additional information

I'm using Robomongo.

Answer 1

Where you are going wrong here is in the design of your document. Take the following section:

  "field": {
    "Berna": {
      "position": [5],
      "distance": [1550]
    },
    "Excited Prince": {
      "position": [2],
      "distance": [1550]
    }
  }

Without going into asking why you would want inner elements such as "position" and "distance" to be arrays anyway, the key problem here is that "field" is not an array. Instead of using an array, you have just used "object keys" to identify different types of data.

What you need to understand here is that for a "database" this is very bad practice. Your "keys" such as "Berna" and "Excited Prince" contain useful data for which you could query. But presently, you cannot do that as they as "keynames" and not "values" in the data document. And databases are really only concerned with "values", and especially when it comes to "indexing" content.

So getting "Horses" right now is impossible, without resulting to code to iterate though all object keys and extract the "Horses". That sort of thing is possible with a mapReduce operation, but certainly overkill for just fetching the items, and not really efficient.

Basic query arguments are not possible either, as right now you would have to do something like this:

{ "race.field.Excited Prince.position": 2 }

Which would find you all 2nd place for "Excited Prince", but to test all "Horses" you basically need to specify every possible key path. There are no wildcards to use with key names, and any conditions again revert back to JavaScript coding and traversal of objects.

In summary, modelling like you have is a "bad" practice. It is non performant, inflexible and full of issues.

So at the very least then change your structure to an array like this:

  "field": [
      {
        "Horse": "Berna",
        "position": [5],
        "distance": [1550]
      },
      {
        "Horse": "Excited Prince",
        "position": [2],
        "distance": [1550]
      }
 ]

Now you can basically query and match documents with:

db.Sectionals.find({ "race.field.position": 2 })

And match all documents at least.

Or for your specific projection, then use .aggregate():

db.Sectionals.aggregate([
    { "$match": { 
        "race.field.position": 2
    }},
    { "$unwind": "$race.field" },
    { "$match": { 
        "race.field.position": 2
    }},
    { "$project": { 
        "Horse": "$race.field.Horse",
        "position": "$race.field.position",
        "distance": "$race.field.distance"
    }}
])  

Which returns:

{
        "_id" : ObjectId("55e161545c6a4540fa687037"),
        "Horse" : "Belladini",
        "position" : [
                2
        ],
        "distance" : [
                1100
        ]
}
{
        "_id" : ObjectId("55e161545c6a4540fa687038"),
        "Horse" : "Excited Prince",
        "position" : [
                2
        ],
        "distance" : [
                1550
        ]
}

Answer 2

You need to change change your document structure and the best way to do it is using "Bulk" operation.

var bulk = db.sectionals.initializeOrderedBulkOp(),  
    count = 0;

db.sectionals.find().forEach(function(doc){
    var field = doc.race['field']; 
    var newField = []; 
    for (var key in field ){    
        newField.push({
            'Horse': key, 
            'position': field[key]['position'], 
            'distance': field[key]['distance'] 
        }); 
    } 
    bulk.find({ "_id": doc._id }).update({
        "$set": { "race.field":  newField }
    }); 
    count++; 
    if (count % 500 == 0) {    
        bulk.execute();    
        bulk = db.sectionals.initializeOrderedBulkOp(); 
    }
})
if (count % 500 != 0)
    bulk.execute();

---

Your documents then look like this:

{
        "_id" : ObjectId("55e16462d4a8842b9fd90bfa"),
        "race" : {
                "date" : [
                        "2013-05-08"
                ],
                "meeting" : [
                        "canterbury park"
                ],
                "weather" : [
                        "fine"
                ],
                "field" : [
                        {
                                "Horse" : "Belladini",
                                "position" : [
                                        2
                                ],
                                "distance" : [
                                        1100
                                ]
                        },
                        {
                                "Horse" : "Bobs Destiny",
                                "position" : [
                                        7
                                ],
                                "distance" : [
                                        1100
                                ]
                        }
                ]
        }
}

---

Then you can use aggregation as @BlakesSeven already mentions in his answer.

db.sectionals.aggregate([
     { "$match": {
         "race.field.position": 2
     }},
     { "$unwind": "$race.field" },
     { "$match": {
         "race.field.position": 2
     }},
     { "$project": {
         "Horse": "$race.field.Horse",
         "position": "$race.field.position",
         "distance": "$race.field.distance"
     }}
])

---

{ "_id" : ObjectId("55e16462d4a8842b9fd90bfa"), "Horse" : "Belladini", "position" : [ 2 ], "distance" : [ 1100 ] }
{ "_id" : ObjectId("55e16462d4a8842b9fd90bfb"), "Horse" : "Excited Prince", "position" : [ 2 ], "distance" : [ 1550 ] }