MongoDB sort by nested array

Question

I am now using mongodb to get what I want.

{
  "_id" : ObjectId("533b66f3e4b038c01f6a427f"),
  "ccNameCount" : [{
      "ccName" : "ATCC",
      "paperCount" : 4660,
      "strainCount" : 9339,
      "version" : 1
    }, {
      "ccName" : "BCC",
      "paperCount" : 24,
      "strainCount" : 41
    }, {
      "ccName" : "BCCM/DCG",
      "paperCount" : 3,
      "strainCount" : 7
    }]
}

Sample document is just like this.Now , I want to query the documents whose ccNameCount.ccName = ATCC and sort the result by paperCount.But of cource ,since ccNameCount is an array , just sort by the key ccNameCount will not give me what I want.So , my question is ,what can i do?Using MapReduce?

Neil Lunn · Accepted Answer · 2014-04-30 02:18:34Z

4

Your best option is the aggregation framework:

db.collection.aggregate([

    // Match the "documents" that contain your condition
    // reduces the working size
    { "$match": {
        "ccNameCount.ccName": "ATCC"
    }},

    // Keep the original document, you might not really care about this
    { "$project": {
        "_id": {
            "_id": "$_id",
            "ccNameCount": "$ccNameCount"
        },
        "ccNameCount": 1
   }},

    // Unwind the array to de-normalize
    { "$unwind": "$ccNameCount" },

    // Now just match those elements (now documents) as your condition
    { "$match": {
        "ccNameCount.ccName": "ATCC"
    }},

    // Sort the results on the now selected "paperCount"
    { "$sort": { "ccNameCount.paperCount": -1 } },

    // Put the documents back to normal if you want
    { "$project": {
        "_id": "$_id._id",
        "ccNameCount": "$_id.ccNameCount"
    }}

])

So after this actually "selects" your matching array element, the results can then be sorted by the field in this position. The $project stages are optional if you want to get the orginal state of the array back in your results, otherwise the stages only select the matching entries and sorts them correctly

answered Apr 30, 2014 at 2:18

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wuchang Over a year ago

Hi , thank you for your response,I understand now.But I have a very small quesiton.You have two $match operation ,before $unwind and after $unwind,I know their different function ,but what should I to in my java code to make the $match occur two times?

Neil Lunn Over a year ago

@Vico_Wu As the comments on the stages state, they are doing two completely different things. The first $match filters documents. Filtering documents and filtering array items are very different. The second statement after the unwind actually selects only the matching elements. So the purpose of the first $match is to only select the documents that do have that matching element. If all of your documents have that field, then the first match is redundant. But if not, it is your best performance gain to first remove the documents that do not match at all.

Collectives™ on Stack Overflow

MongoDB sort by nested array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related