Sorting on fields from documents that are nested in an array

[Jamey Wood]

When sorting on a field from a document that is nested in an array, is it possible to apply some kind of selection logic to control which of those array-nested documents are elegible for inclusion in the sort?

I think that it's easiest to illustrate what I'd like to do with an example.  The attached "nested_doc_sort" script attempts to do that.  Please let me know if it isn't clear.

If such a sort is not possible, is there some other way I should be structuring this data?  I considered using a map instead of an array for the publication items.  If I understand things correctly, that would indeed allow me to express this sort--but I don't see how I'd be able to construct appropriate indexes to ensure that these queries perform well with non-trivial datasets.

Thanks,

Jamey

[Gijs]

AFAIK doing it the way you discribed isn't possible, however if you'd
change the data structure slightly by making publications an object
instead of an array and using the author as a key, that would make
sorting by author publication date possible:

var item1 = {
id: 1,
text : 'Item that Joe Published First',
publications : {
'joe': {
user : 'joe',
time : d1_joe.getTime()
},
'bob': {
user : 'bob',
time : d1_bob.getTime()
}
}
};

like that, you'd use 'publications.joe.time' as sort key. However, I'm
not sure if it's possible to index for this kind of sorting
efficiently.

Regards,

PS.
You can insert the Date object directly, you don't have to
call .getTime, unlike JSON, BSON supports the Date type.
Also, instead of the 'id' field, use '_id' as it saves you an index
assuming your id is supposed to be unique.

>  nested_doc_sort
> 1KViewDownload

[Jamey Wood]

Thanks for responding!  What you note below is the same possibility I was referencing when I mentioned using a map instead of an array.  But if it isn't possible to construct indexes that the sorts will use with that approach (as we both noted), I don't think it's a viable solution for anything but trivially-small datasets.

So if I'm understanding things correctly, I'm in a bit of a catch-22:

• If I use a nested array, I can construct efficient indexes--but can't express a proper query to use them
• If I use a nested object, I can express the query I'm seeking--but can't construct indexes to make it efficient
• If I get rid of the nesting entirely (and use a separate collection for the publications stuff), I'll lose any ability to query across attributes of both the publication and the published item (due to the joinless nature of MongoDB)

I certainly hope that I'm wrong about one or more of these cases, though.

Thanks,

Jamey

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[Tony Hannan]

You could try using a separate collection (your 3rd option) but keep data you would normally join to in the collection redundantly (denormalize).

[axlfu]

Would you pls explain this use case?
Let's think about another way to workaround

On Sep 20, 12:24 am, Jamey Wood <jamey.w...@gmail.com> wrote:
> Thanks for responding!  What you note below is the same possibility I was
> referencing when I mentioned using a map instead of an array.  But if it
> isn't possible to construct indexes that the sorts will use with that
> approach (as we both noted), I don't think it's a viable solution for
> anything but trivially-small datasets.
>
> So if I'm understanding things correctly, I'm in a bit of a catch-22:
>

>    - If I use a nested array, I can construct efficient indexes--but can't

>    express a proper query to use them

>    - If I use a nested object, I can express the query I'm seeking--but

>    can't construct indexes to make it efficient

>    - If I get rid of the nesting entirely (and use a separate collection for

[Gijs]

Ahh, when reading your mail, in your second paragraph you were
referring to the attached document which caused me to completely skip
your last paragraph in which you described exactly what I tried to
say. I did exactly what support staff from all over the world do: not
reading the question carefully. I apologize. I do have one more
thought, although it comes with a couple of assumptions:

1. Your running MongoDB 1.8+
2. When you want to sort on Bob's publication date, you don't want to
return any documents Bob didn't contribute to
3. The number of authors is relatively limited

If these three hold you might be able to create a sparse index per
author, assuming you don't run into the namespace limit (http://
www.mongodb.org/display/DOCS/Using+a+Large+Number+of+Collections).

Otherwise as axifu noted, you might want to elaborate more on your
problem as there's always a chance this may be an XY-problem

On Sep 19, 6:24 pm, Jamey Wood <jamey.w...@gmail.com> wrote:
> Thanks for responding!  What you note below is the same possibility I was
> referencing when I mentioned using a map instead of an array.  But if it
> isn't possible to construct indexes that the sorts will use with that
> approach (as we both noted), I don't think it's a viable solution for
> anything but trivially-small datasets.
>
> So if I'm understanding things correctly, I'm in a bit of a catch-22:
>

>    - If I use a nested array, I can construct efficient indexes--but can't

>    express a proper query to use them

>    - If I use a nested object, I can express the query I'm seeking--but

>    can't construct indexes to make it efficient

>    - If I get rid of the nesting entirely (and use a separate collection for