Mongo aggregation in java

Question

Hi my mongo collections contains following documents:

  {
      "_id" : ObjectId("539efd5b254bb5f65c9da94e"),
      "iInfo" : [ 
          { "ifout" : 0, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 },
          { "ifout" : 0, "iferror" : 10, "ifdes" : "N/A", "ifin" : 0 },
          { "ifout" : 0, "iferror" : 4, "ifdes" : "N/A", "ifRemotePort" : "0", "ifin" : 0 },
          { "ifout" : 0, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 } 
      ]
  }
  { 
      "_id" : ObjectId("539efd76254bb5f65c9da94f"),
      "iInfo" : [ 
          { "ifout" : 0, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 },
          { "ifout" : 0, "iferror" : 7, "ifdes" : "N/A", "ifin" : 0 },
          { "ifout" : 0, "iferror" : 4, "ifdes" : "N/A", "ifRemotePort" : "0", "ifin" : 0 },
          { "ifout" : 110, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 }
          { "ifout" : 90, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 }
      ]
  }
  {
      "_id" : ObjectId("539efed7254bb5f65c9da950"),
     "iInfo" : [ 
         { "ifout" : 10, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 }, 
         { "ifout" : 0, "iferror" : 10, "ifdes" : "N/A", "ifin" : 0 },
         { "ifout" : 0, "iferror" : 4, "ifdes" : "N/A", "ifRemotePort" : "0", "ifin" : 0 },
         { "ifout" : 0, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 }
     ] 
  }
  { 
      "_id" : ObjectId("539efeed254bb5f65c9da951"),
      "iInfo" : [
          { "ifout" : 0, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 },
          { "ifout" : 0, "iferror" : 10, "ifdes" : "N/A", "ifin" : 0 },
          { "ifout" : 0, "iferror" : 4, "ifdes" : "N/A", "ifRemotePort" : "0", "ifin" : 0 },
          { "ifout" : 100, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 },
          { "ifout" : 210, "iferror" : 0, "ifdes" : "N/A", "ifin" : 0 }
      ]
  }

Now I want to sort this documents as below:

  { 
      "_id" : ObjectId("539efeed254bb5f65c9da951"),
      "iInfo" : [ 
          { "out" : 210,"iferror" : 0,"ifdes" : "N/A","ifin" : 0} 
      ]
  }
{ 
      "_id" : ObjectId("539efd76254bb5f65c9da94f"),
      "iInfo" : [ 
          { "out" : 110,"iferror" : 0,"ifdes" : "N/A","ifin" : 0} 
      ]
  }
{ 
      "_id" : ObjectId("539efeed254bb5f65c9da951"),
      "iInfo" : [ 
          { "out" : 100,"iferror" : 0,"ifdes" : "N/A","ifin" : 0} 
      ]
  }
  { 
      "_id" : ObjectId("539efed7254bb5f65c9da950"),
      "iInfo" : [
          { "out" : 90,"iferror" : 0,"ifdes" : "N/A","ifin" : 0} 
      ]
  }

So for this I write my mongo query as below

db.demo.aggregate([
    { "$project": { "_id": 1, "out": "$iInfo.ifout"} },
    { "$unwind": "$out" },
    { "$sort": { "_id": 1, "out": -1 } },
    { "$group": { "_id": "$_id" , "iInfo": { "$push": { "out":"$out" } } } }
 ])

and the above query returns output as below:

{
    "_id" : ObjectId("539efeed254bb5f65c9da951"),
    "iInfo" : [ 
        { "out" : 100 }, 
        { "out" : 0 },
        { "out" : 0 }, 
        { "out" : 0 }
    ]
}
{ 
    "_id" : ObjectId("539efed7254bb5f65c9da950"),
    "iInfo" : [ 
        { "out" : 10 },
        { "out" : 0 },
        { "out" : 0 },
        { "out" : 0 }
    ]
}
{
    "_id" : ObjectId("539efd76254bb5f65c9da94f"),
    "iInfo" : [
        { "out" : 90 },
        { "out" : 0 },
        { "out" : 0 },
        { "out" : 0 } 
    ]
}
{
    "_id" : ObjectId("539efd5b254bb5f65c9da94e"),
    "iInfo" : [
        { "out" : 0 },
        { "out" : 0 },
        { "out" : 0 },
        { "out" : 0 }
    ]
}

So, how can I get the desire output using mongo and java code ?

Are you using the Java drivers? The code for your query is JavaScript, which is to be run from a terminal... — Kyte, Jun 16 '14 at 16:09
Hi @Kyte I used java drive also but I want both queries in mongo and java. I tried in java but not found any solutions. — Neo-coder, Jun 16 '14 at 16:19
This question might help http://stackoverflow.com/questions/12296963/mongodb-aggregation-how-to-return-only-matching-elements-of-an-array or this one might help http://stackoverflow.com/questions/18023160/passing-more-than-one-unwind-object-from-java-driver — Kyte, Jun 16 '14 at 18:05

Neil Lunn · Answer 1 · 2014-07-17T04:15:07.023

2

More or less on the right track, but since you seem to want the highest sorted value then you need to filter with the $first operator when you $group:

db.demo.aggregate([
    // Match documents with non 0 values in the array
    { "$match": { "iInfo.ifout": { "$ne": 0 } }},

    // Unwind the array
    { "$unwind": "$iInfo" },

    // Filter any array elements that have 0 - also removes documents where all are 0
    { "$match": { "iInfo.ifout": { "$ne": 0 } }},

    // Sort the array elements within documents
    { "$sort": { "_id": -1, "iInfo.ifout": -1 } },

    // Take only the "first" array element per document
    { "$group": {
        "_id": "$_id",
        "Iifout": { "$first": "$iInfo.ifout" },
        "Iiferror": { "$first": "$iInfo.iferror" },
        "Iifdes": { "$first": "$iInfo.ifdes" },
        "Iifin": { "$first": "$iInfo.ifin" }
    }},

    // Group to push those results as an array
    { "$group": {
        "_id": "$_id",
        "iInfo": {
            "$push": {
                "ifout": "$Iifout",
                "iferror": "$Iiferror",
                "Iifdes": "$Iifdes",
                "Iifin": "$Iifin"
            }
        }
    }}
])

Also noting that your desired output is in _id descending order.

Aggregation operations with $group are separate, and you cannot use sub-document keys in an "group" stage, so if you want things back as an array you do as second $group pipeline as shown in order to then $push the values as an array field.

edited Jul 17 '14 at 04:15

answered Jul 01 '14 at 02:09

Neil Lunn

148,042
36
346
317

hi it work fine, instead of "$sort": { "_id": -11 I replace "$sort": { "_id": -1 it work – Neo-coder Jul 01 '14 at 08:02
@yogesh Just a typo. I must have just "fat fingered" that one while typing. – Neil Lunn Jul 01 '14 at 08:06
@yogesh I'm sure it is. Accept and upvote are the usual currency here when someone helps you out. – Neil Lunn Jul 01 '14 at 15:02
Hi, I change my output structure can is possible in mongo, any hint or help? – Neo-coder Jul 16 '14 at 10:38
@yogesh I don't see anything different in the output from what you are posting. The only possible difference would be the document with all 0 values appearing. There are additional `$match` filters in the pipeline for this now. – Neil Lunn Jul 17 '14 at 04:16
See this object Id "539efeed254bb5f65c9da951" it contains ifout 210 and 100 and other Object Id is 539efd76254bb5f65c9da94f" which contains ifout 110 so when I sort data according to your mongo script It only returns single highest value of ifout. I want my sort ifout as 210,110 and 100 etc. – Neo-coder Jul 17 '14 at 04:42
@yogesh Your "output" only shows one item so that is what we assume you want. If you just want to "sort" then all you do is remove the `$group` stage that uses the `$first` operations. Leaving just one `$group` pushes back the "sorted" array with all contents, or without the 0 matches if you use the `$match` pipeline stages as shown above. Otherwise it is not clear what you are asking since the results match. – Neil Lunn Jul 17 '14 at 04:45
Hi Used following query which give me my desired output which I wanted db.demo.aggregate([ { "$unwind": "$iInfo" }, {"$project":{"iInfo.ifout":1,"iInfo.iferror":1,"iInfo.ifdes":1,"iInfo.ifin":1,"_id":1}}, { "$sort": {"iInfo.ifout": -1 } } ]).pretty() – Neo-coder Jul 18 '14 at 05:03

Mongo aggregation in java

1 Answers1