sort on field inside an array which is inside a document

[Deepak]

Hi,

I have a document and a document has array collection with some field.

When i try to sort using one field inside the Array, i dont see
desired results. There are few fields at same level at array is and
when i sort on those field, that works fine.

Document
{
Name
DetailArray :{
Major : 2,
minor : 1
}
}


So Document.Name works fine in sort, but Document.DetailArray.Major is
not working ?.... Is i am something missing or this is not achievable
inside array collection...do i need to flatten out the document fo
sorting purpose ?


I tired this in Mongo 1.6 and 1.8 both.

Any pointers will be helpful.

Thanks,

Deepak

[sridhar]

Hi Deepak, your example did not contain an array. But if your
subelement is actually an array the sort does not work if there is no
index on it but does sort if there is an index on it. There is an open
JIRA ticket for this. https://jira.mongodb.org/browse/SERVER-480

A couple of experiments I did
With
db.foo.insert({_id:1,name:'alvin',detailarray:{max:10,min:9}})
db.foo.insert({_id:2,name:'sridhar',detailarray:{max:8,min:9}})
db.foo.insert({_id:3,name:'chris',detailarray:{max:7,min:12}})
db.foo.insert({_id:4,name:'aaron',detailarray:{max:6,min:3}})

if I do a db.foo.find().sort({"detailarray.max":1}) you get the
following irrespective of whether there is an index on detailarray.max
{ "_id" : 4, "name" : "aaron", "detailarray" : { "max" : 6, "min" :
3 } }
{ "_id" : 3, "name" : "chris", "detailarray" : { "max" : 7, "min" :
12 } }
{ "_id" : 2, "name" : "sridhar", "detailarray" : { "max" : 8, "min" :
9 } }
{ "_id" : 1, "name" : "alvin", "detailarray" : { "max" : 10, "min" :
9 } }

If you have
db.foo.insert({_id:1,name:'alvin',detailarray:[{max:10,min:9}]})
db.foo.insert({_id:2,name:'sridhar',detailarray:[{max:8,min:9},{max:
5,min:9}]})
db.foo.insert({_id:3,name:'chris',detailarray:[{max:7,min:12}]})
db.foo.insert({_id:4,name:'aaron',detailarray:[{max:6,min:3}]})
then
db.foo.find().sort({"detailarray.max":1})
gives
{ "_id" : 1, "name" : "alvin", "detailarray" : [ { "max" : 10, "min" :
9 } ] }
{ "_id" : 2, "name" : "sridhar", "detailarray" : [ { "max" : 8,
"min" : 9 }, { "max" : 5, "min" : 9 } ] }
{ "_id" : 3, "name" : "chris", "detailarray" : [ { "max" : 7, "min" :
12 } ] }
{ "_id" : 4, "name" : "aaron", "detailarray" : [ { "max" : 6, "min" :
3 } ] }
without the index
but with the index db.foo.ensureIndex({"detailarray.max":1})
db.foo.find().sort({"detailarray.max":1})
gives
{ "_id" : 2, "name" : "sridhar", "detailarray" : [ { "max" : 8,
"min" : 9 }, { "max" : 5, "min" : 9 } ] }
{ "_id" : 4, "name" : "aaron", "detailarray" : [ { "max" : 6, "min" :
3 } ] }
{ "_id" : 3, "name" : "chris", "detailarray" : [ { "max" : 7, "min" :
12 } ] }
{ "_id" : 1, "name" : "alvin", "detailarray" : [ { "max" : 10, "min" :
9 } ] }

Hope this helps

[Deepak]

Are you sure below is the sorted list ?

{ "_id" : 2, "name" : "sridhar", "detailarray" : [ { "max" : 8,
"min" : 9 }, { "max" : 5, "min" : 9 } ] }
{ "_id" : 4, "name" : "aaron", "detailarray" : [ { "max" : 6, "min" :
3 } ] }
{ "_id" : 3, "name" : "chris", "detailarray" : [ { "max" : 7, "min" :
12 } ] }
{ "_id" : 1, "name" : "alvin", "detailarray" : [ { "max" : 10,
"min" :
9 } ] }

Should be it like as follows:-

{ "_id" : 4, "name" : "aaron", "detailarray" : [ { "max" : 6, "min" :
3 } ] }

{ "_id" : 2, "name" : "sridhar", "detailarray" : [{ "max" : 5, "min" :
9 } ,
{ "max" : 8, "min" : 9 } ] }

{ "_id" : 3, "name" : "chris", "detailarray" : [ { "max" : 7, "min" :
12 } ] }
{ "_id" : 1, "name" : "alvin", "detailarray" : [ { "max" : 10,
"min" :
9 } ] }

On Apr 26, 6:16 pm, sridhar <srid...@10gen.com> wrote:
> Hi Deepak, your example did not contain an array. But if your
> subelement is actually an array the sort does not work if there is no
> index on it but does sort if there is an index on it. There is an open

> JIRA ticket for this.https://jira.mongodb.org/browse/SERVER-480

> > Deepak- Hide quoted text -
>
> - Show quoted text -

[sridhar]

Yes that is the sorted list is. This is because I sorted on
detailarray.max with an index on it. Note for the document with name :
sridhar there is a detail.max element with value 5 and hence sorts
before the document with name:aaron

[Deepak]

Thanks for your reply and input.

I think i need a different result like, records as follows :-

{ "_id" : 2, "name" : "sridhar", "detailarray" : [{ "max" : 5, "min" :
9 } , { "max" : 8, "min" : 9 } ] }

{ "_id" : 4, "name" : "aaron", "detailarray" : [ { "max" : 6, "min" :
3 } ] }
{ "_id" : 3, "name" : "chris", "detailarray" : [ { "max" : 7, "min" :
12 } ] }
{ "_id" : 1, "name" : "alvin", "detailarray" : [ { "max" : 10,"min" :
9 } ] }

When i use -1, i am expecting reverse of this.

Sorted on max and within array too result should be sorted ?

do you think i can get above by indexing ? Like sorting within array
too.

And is it possible to use sort and group together somehow ?Like if i
need to group by using some different field ?

[sridhar]

There is no way to do this directly currently. You need to do this on
your client or use map-reduce to flatten the array and then post
process.

[Deepak]

Thanks for the update. Will explore other options

On Apr 28, 9:17 am, sridhar <srid...@10gen.com> wrote:
> There is no way to do this directly currently. You need to do this on
> your client or use map-reduce to flatten the array and then post
> process.
>
> On Apr 28, 5:03 am, Deepak <deepak.adla...@gmail.com> wrote:
>
>
>
> > Thanks for your reply and input.
>
> > I think i need a different result like, records as follows :-
>
> > { "_id" : 2, "name" : "sridhar", "detailarray" : [{ "max" : 5, "min" :
> > 9 } , { "max" : 8, "min" : 9 } ] }
> > { "_id" : 4, "name" : "aaron", "detailarray" : [ { "max" : 6, "min" :
> > 3 } ] }
> > { "_id" : 3, "name" : "chris", "detailarray" : [ { "max" : 7, "min" :
> > 12 } ] }
> > { "_id" : 1, "name" : "alvin", "detailarray" : [ { "max" : 10,"min" :
> > 9 } ] }
>
> > When i use -1, i am expecting reverse of this.
>
> > Sorted on max and within array too result should be sorted ?
>
> > do you think i can get above by indexing ? Like sorting within array
> > too.
>

> > And is it possible to usesortand group together somehow ?Like if i

> > > > > subelement is actually an array thesortdoes not work if there is no
> > > > > index on it but doessortif there is an index on it. There is an open

> > > > > > When i try tosortusing one field inside the Array, i dont see

> > > > > > desired results. There are few fields at same level at array is and

> > > > > > when isorton those field, that works fine.

>
> > > > > > Document
> > > > > > {
> > > > > >    Name
> > > > > >     DetailArray :{
> > > > > >               Major : 2,
> > > > > >               minor : 1
> > > > > >      }
>
> > > > > > }
>

> > > > > > So Document.Name works fine insort, but Document.DetailArray.Major is

> > > > > > not working ?.... Is i am something missing or this is not achievable
> > > > > > inside array collection...do i need to flatten out the document fo
> > > > > > sorting purpose ?
>
> > > > > > I tired this in Mongo 1.6 and 1.8 both.
>
> > > > > > Any pointers will be helpful.
>
> > > > > > Thanks,
>
> > > > > > Deepak- Hide quoted text -
>

> > > > > - Show quoted text -- Hide quoted text -