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).

Blakes Seven · Accepted Answer · 2015-09-25 13:13:03Z

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 ]
}

hi, thanks for your answer, but your solution doesn't do this for each value from an array I get a full document. how can I get it? UPD: I want to get 6 (six) results in the first and second cases
@d-d Of course it does. Sorting by "index" ( your question title, despite the multiple questions - and please don't do that again, first post forgiven ) does not affect the document in any way. As for the .aggregate() approach, when using operators like $group or $project it it up to "you" to define the fields you want to return. Operators like $first will help you here. Read some documentation and don't make hasty comments like "does not work" to those with more knowledge than yourself when you want help.
@d-d And for each value in an array ( I can shout too ) then just use $unwind without the $group and sort on the value. Pretty simple if you spent more than two seconds actually reading the useful information you were just given.
I've just checked again, and your solution returns only two results, not six.
but thanks for pointing to $unwind, it seems to be what I'm looking for

Collectives™ on Stack Overflow

MongoDB working with indexed array

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related