Perl driver and aggregation sorting

[JohnM]

Hello,
Having a bit of trouble getting the sort to work on an aggregation query.  I have a few documents(only 3 or 4 hundred) that have a group name taken from a substring of what precedes the first dot in the FQDN of the server.  There can be up to 5 servers in each group.

in the shell I can run:
db.Hosts.find( { "Group Name" :{$exists: true}}, { _id:0, "Group Name":1 } ).sort( { "Group Name": 1 } );
{ "Group Name" : "ambler" }
{ "Group Name" : "ambler" }
{ "Group Name" : "ambler" }
{ "Group Name" : "ambler" }
{ "Group Name" : "ambler" }
{ "Group Name" : "anaktuvuk" }
{ "Group Name" : "anaktuvuk" }
{ "Group Name" : "anaktuvuk" }
{ "Group Name" : "anaktuvuk" }
{ "Group Name" : "anvik" }
{ "Group Name" : "anvik" }
{ "Group Name" : "anvik" }
{ "Group Name" : "anvik" }
{ "Group Name" : "atqasuk" }
{ "Group Name" : "atqasuk" }
{ "Group Name" : "atqasuk" }
{ "Group Name" : "atqasuk" }
{ "Group Name" : "atqasuk" }
{ "Group Name" : "bethel1" }
{ "Group Name" : "bethel1" }
{ "Group Name" : "bethel1" }
{ "Group Name" : "bethel1" }
{ "Group Name" : "bethel1" }
{ "Group Name" : "bethel3" }
{ "Group Name" : "buckland" }
{ "Group Name" : "buckland" }
{ "Group Name" : "buckland" }
{ "Group Name" : "buckland" }

And the results are as expected.
When I move over to the perl I am writing and try to aggregate all the info about each of the hosts in each group, I get all the info I want but the sort is off so that the groups are not presented in alphabetical order as they are in the above simple shell query.
This is my Perl query:
my $all = $coll->aggregate( [ { "\$sort" => { "Group Name" => -1 } }, { "\$group" => { _id => "\$Group Name", SERVER => { "\$push" => "\$HostName"}, TYPE => { "\$push" => "\$Type" } } } ] );

I then run through the results as such ( I actually do the same for each of the 4 possible servers that could be in the group). I also use a count var to number each group.

while (my @a = keys %$dts ) {

 if ( defined $dts->{ '_id' } ) {
   $loc =  $dts->{ '_id' };
   }

  if ( defined $dts->{ 'SERVER'}[0] ) {
   $type = $dts->{'TYPE'}[0];
     if ( $type eq "DM" ) {
         $dm = $dts->{ 'SERVER'}[0];
      } elsif ( $type eq "TCP" ) {
         $tcp = $dts->{ 'SERVER'}[0];
      } elsif ( $type eq "DC" ) {
         $dc = $dts->{ 'SERVER'}[0];
      } elsif ( $type eq "CDR" ) {
         $cdr = $dts->{ 'SERVER'}[0];
      } elsif ( $type eq "HOST" ) {
         $host = $dts->{ 'SERVER'}[0];
      }
   }


as I said the query brings back all the correct info but not in the correct order.  Example of grouping order:
12      elim   
13      golovin
14      holycross      
15      dillingham     
16      ptheiden       
17      whitemountain  
18      ptheiden_rtr1  
19      stpaul 
20      egegik 
21      manley
22      savoong
23      yakutat
24      stuyahok       
25      shageluk       
26      perryville     
27      ksalmon
28      shungnak       
29      ftyukon
30      stebbins       
31      pilotpt
32      anaktuvuk

Any pointers to what I may be doing wrong?

Thanks,
JohnM

Running on CentOS 6.7

[kickstart info]$ perl -v

This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi
installed via yum or as OS initial install (it was a while ago, been to sleep since then)


Driver version
MongoDB-v1.2.3  installed via cpan (with some mucking about due to conflicts and dependencies not fully meet by initial cpan install)

[Wan Bachtiar]

in the shell I can run:
db.Hosts.find( { “Group Name” :{$exists: true}}, { _id:0, “Group Name”:1 } ).sort( { “Group Name”: 1 } );

And the results are as expected.

Hi John,

You can do the equivalent of the find() query above in Perl’s aggregate like below:

my @pipeline = (
     { '$match' => { 'Group Name' => { '$exists' => 1 }}},
     { '$group' => { '_id' => '$Group Name' }},
     { '$sort' => { '_id' => 1 }}
);
my $cursor = $collection->aggregate(\@pipeline);

It should returns the result sorted by Group Name field. You could also rename _id field back to Group Name by using $project operator.

When I move over to the perl I am writing and try to aggregate all the info about each of the hosts in each group, I get all the info I want but the sort is off so that the groups are not presented in alphabetical order as they are in the above simple shell query.

You should be able to get the result you wanted by modifying the $group stage from the example above to include HostName and Type; Keeping the $sort on the last stage of the pipeline.

Regards,

Wan.

​

[John Millican]

Wan,

Thank You sir, This worked perfect.  Apologies for taking so long to get back to you, got sidetracked by other projects higher up on the bosses want list.

Final code snippet:

my @pipeline = (
     { '$match' => { 'Group Name' => { '$exists' => 1 }}},

     { '$group' => { '_id' => '$Group Name', SERVER => { "\$push" => "\$HostName"}, TYPE => { "\$push" => "\$Type" } } },

     { '$sort' => { '_id' => 1 }}
);

my $all= $coll->aggregate(\@pipeline);

my $dts = $all->next;

I decided that I would not use $project to rename things for now so that the code folllowing this would not have to change( I am lazy at times :-) ) 

Thank You again,

JohnM

--
You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 
For other MongoDB technical support options, see: https://docs.mongodb.org/manual/support/
---
You received this message because you are subscribed to a topic in the Google Groups "mongodb-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mongodb-user/7wf5UOaCYOQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mongodb-user...@googlegroups.com.
To post to this group, send email to mongod...@googlegroups.com.
Visit this group at https://groups.google.com/group/mongodb-user.
To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/0aa6765c-d79b-4219-8ff9-a86cbdf2755c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.