Conditionally order results in the one response

Question

So I have a mongo collection with an array of chapters inside it. What I want to do is do a find, sorted by date - but push all the documents that have only 1 chapter to the end of the list.
Note: I don't want to sort by chapters count - only push the 1 chapter documents to the end of the cursor.

Now I could do 2 finds:

cursor1 = collection.find({'chapters.1':{$exists: true}})
//iterate over it
cursor2 = collection.find({'chapters.1':{$exists: false}})
//iterate over it

The problem is - I'm doing pagination. Currently without the seperation is:
(sorry for pseudocode - translating from PHP)

cursor = collection.find()
cursor.sort({created: -1})
cursor.skip($page * $page_size)
cursor.limit($page_size);

When trying the above solution to separate - it creates quite a nasty code

Do I have a choice? Any magic "push 1 chapters to the end" to push into the find itself?

For starters, detecting the arrays with only one item in it would be `"chapters.0"` as `0` is the index position of the first element. n-1 — Blakes Seven, Aug 26 '15 at 10:35
I first need the "more than 1" and wanted to use this trick:http://stackoverflow.com/questions/7811163/how-to-query-for-documents-where-array-size-is-greater-than-one-1-in-mongodb/15224544#15224544 — Boaz, Aug 26 '15 at 10:41

score 1 · Accepted Answer · edited May 23 '17 at 11:43

It sounds like you want to "weight" things with only one array element so that you can "sort" the results on that weight. This effectively would be "chapters longer than 1" with a 0 score, and "chapters with length 1" with a 1 score, and ascending sort on that weight score so the 1 lengths are at the end of the list.

To do this you use something that can modify the document, such as .aggregate(). Where we are basically going to inspect each document and calculate the weight:

collection.aggregate([
    { "$project": {
        "someField": 1,
        "chapters": 1,
        "created": 1,
        "weight": {
            "$cond": [
                { "$lte": [ { "$size": "$chapters" }, 1 ] },
                1,
                0
            ]
        }
    }},
    { "$sort": { "weight": 1, "created": -1 } },
    { "$skip": numToSkip },
    { "$limit": numToLimit }
])

That means that all the documents with a chapters that only have one or less items in the array will be "sorted" to the bottom of the results.

Read about $project as you must explicitly name each field you want in the results. Or you can alter the document to contain the original document under a single result field and project using $$ROOT.

For a better approach to forward paging see, Calculate skip value for given record for sorted paging and impelement the principles there if you can.

Wow... this is awesome. Thanks for both tips, I will try it! — Boaz, Aug 26 '15 at 10:54
Just a quick fix (your answer is awesome so it is better to edit it) I had a problem with empty chapters array so the working $cond is: { "$lte": [ { "$size": { "$ifNull": [ "$chapters", [] ] } }, 1 ] } — Boaz, Aug 26 '15 at 11:46
@Boaz Yes. That is the correct way to do it if the field does not exist. Guess I'm too used to uniform structure. — Blakes Seven, Aug 26 '15 at 11:47

Conditionally order results in the one response

1 Answers1