MongoDB working with indexed array

Question

For example, I have a collection "test" with an index on array field "numbers", I have two documents there:

db.test.createIndex({"numbers": 1})
db.test.insert({"title": "A", "numbers": [1,4,9]})
db.test.insert({"title": "B", "numbers": [2,3,7]})

1) How can I get all results sorted by "numbers" (using index), so for each value from an array I get a full document? Like this:

 {"_id": "...", "title": "A", "numbers": [1,4,9]}
 {"_id": "...", "title": "B", "numbers": [2,3,7]}
 {"_id": "...", "title": "B", "numbers": [2,3,7]}
 {"_id": "...", "title": "A", "numbers": [1,4,9]}
 {"_id": "...", "title": "B", "numbers": [2,3,7]}
 {"_id": "...", "title": "A", "numbers": [1,4,9]}

2) How can I get such results (sorry for no explanation, but I think it's clear what I'm trying to achieve here):

 {"_id": "...", "title": "A", "numbers": 1}
 {"_id": "...", "title": "B", "numbers": 2}
 {"_id": "...", "title": "B", "numbers": 3}
 {"_id": "...", "title": "A", "numbers": 4}
 {"_id": "...", "title": "B", "numbers": 7}
 {"_id": "...", "title": "A", "numbers": 9}

3) How can I get similar results, but ordering by the second element in each array?:

 {"_id": "...", "title": "B", "numbers": 3}
 {"_id": "...", "title": "A", "numbers": 4}

Also I care about the performance, so it'd be great if you explain which technique is faster / slower (if there is more than one way to do it, of course). Thanks.

UPD: Let me clarify. We have an index on "numbers" array. So I want to iterate this index from min to max values and get a document which the current value belongs. So some document will be presented in results N times, where N = number of elements in its array ("numbers" in this case).

Answer 1

Simply use the index in the sort by "dot notation":

db.collection.find().sort({ "numbers.0": 1 })

Which is the fastest way if you now the position of which you want, so just use the "index" ( starting at 0 of course ). So the same applies to any indexed position of the array.

If you want the "smallest" value in an array to sort by, then that takes more work, using .aggregate() to work that out:

db.collection.aggregate([
    { "$unwind": "$numbers" },
    { "$group": {
        "_id": "$_id",
        "numbers": { "$push": "$numbers" },
        "min": { "$min": "$numbers" }
    }},
    { "$sort": { "min": 1 } }
])

And naturally that is going to take more time in execution due to the work done than the earlier form. It of course requires the $unwind in order to de-normalize the array elements to individual documents, and the the $group with specifically $min to find the smallest value. Then of course there is the basic $sort you need.

---

For the full thing then you can basically do this:

  db.test.aggregate([
    { "$project": {
      "title": 1,
      "numbers": 1,
      "copy": "$numbers"
    }},
    { "$unwind": "$copy" },
    { "$group": {
      "_id": {
        "_id": "$_id",
        "number": "$copy"
      },
      "numbers": { "$first": "$numbers" }
    }},
    { "$sort": { "_id.number": 1 } }
  ])

Which produces:

{ 
  "_id" : { 
    "_id" : ObjectId("560545d64d64216d6de78edb"), 
    "number" : 1
  }, 
  "numbers" : [ 1, 4, 9 ]
}
{ 
  "_id" : { 
    "_id" : ObjectId("560545d74d64216d6de78edc"), 
    "number" : 2
  },
  "numbers" : [ 2, 3, 7 ]
}
{ 
  "_id" : { 
    "_id" : ObjectId("560545d74d64216d6de78edc"), 
    "number" : 3
  }, 
  "numbers" : [ 2, 3, 7 ]
}
{ 
  "_id" : { 
    "_id" : ObjectId("560545d64d64216d6de78edb"),
    "number" : 4
  },
  "numbers" : [ 1, 4, 9 ]
}
{ 
  "_id" : { 
    "_id" : ObjectId("560545d74d64216d6de78edc"),
    "number" : 7
  },
  "numbers" : [ 2, 3, 7 ]
}
{ 
  "_id" : { 
    "_id" : ObjectId("560545d64d64216d6de78edb"),
    "number" : 9
  },
  "numbers" : [ 1, 4, 9 ]
}