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
– Kyte, Commented Jun 16, 2014 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
– Neo-coder, Commented Jun 16, 2014 at 16:19
This question might help stackoverflow.com/questions/12296963/… or this one might help stackoverflow.com/questions/18023160/… — Kyte
– Kyte, Commented Jun 16, 2014 at 18:05

Neil Lunn · Accepted Answer · 2014-07-17 04:15:07Z

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, 2014 at 4:15

answered Jul 1, 2014 at 2:09

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

8 Comments

Neo-coder Over a year ago

hi it work fine, instead of "$sort": { "_id": -11 I replace "$sort": { "_id": -1 it work

Neil Lunn Over a year ago

@yogesh Just a typo. I must have just "fat fingered" that one while typing.

Neil Lunn Over a year ago

@yogesh I'm sure it is. Accept and upvote are the usual currency here when someone helps you out.

Neo-coder Over a year ago

Hi, I change my output structure can is possible in mongo, any hint or help?

Neil Lunn Over a year ago

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

|

Collectives™ on Stack Overflow

Mongo aggregation in java

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related