One aggregate query that changes the position of the results based on an attribute


i have this different object that i query
{id: 1}
{id: 2}
{id: 3, stickyIndex:2}

currently i am querying it to get all the objects and then sort them by stickyIndex first, then by id:
i get in return {3, 1, 2}

however i want the stickyIndex to change the order and kind of splice the array of result and push it to the second position.

i want to get this result {1, 3, 2}
is there a way to get this order with one aggregate query (and not with manipulating the data after i get it back in the server side)?

To manipulate data interpretation like this, you will need to use the aggregation framework.
As a first step, examine the output of:

db.test.insert({a: 1});
db.test.insert({a: 2});
db.test.insert({a: 3, stickyIndex: 2});
db.test.aggregate([{$addFields: {stickyIndex: {$ifNull: ['$stickyIndex', '$a']}}}]);

You can see that using $ifNull is a way to get what you want. Then, to sort:

 db.test.aggregate([{$addFields: {stickyIndex: {$ifNull: ['$stickyIndex', '$a']}}}, {$sort: {'stickyIndex': 1}}]);

If you want to ensure the ordering of a stickyIndex that also has a duplicate a field, then you could use $add to differentiate the null field, or utilize an alternate name for the addFields operation, and sort by both fields.

thanks for the quick answer. however it is a bit more complicated from that.

actually i have data set like this:
{id: 1, date: ISODate(“2018-01-03T13:45:38.909Z”)}
{id: 2, date: ISODate(“2018-01-02T13:45:38.909Z”)}
{id: 3, stickyIndex:2, date: ISODate(“2018-01-06T13:45:38.909Z”)}

i want to get it all and then sort it by this order:

  1. by date descending
  2. put the stickyIndex items in the according place (no matter what is there date)

so the result should be:
{id: 1, id: 3, id: 2}

is there a way to get this? (it isn’t the same measurement the stickyIndex and the date variables)

In the sample collection you are showing three documents. What if there are four or more documents; and what would be the stickyIndex's document _id's position?

let’s take a more complicated data set and i will try to explain again the logic.

{id: 1, date: ISODate(“2018-01-03T13:45:38.909Z”)}
{id: 2, date: ISODate(“2018-01-02T13:45:38.909Z”)}
{id: 3, stickyIndex:4, date: ISODate(“2018-01-06T13:45:38.909Z”)}
{id: 4, stickyIndex:2, date: ISODate(“2018-01-01T13:45:38.909Z”)}
{id: 5, date: ISODate(“2018-01-01T13:45:38.909Z”)}

what i want it to do is:

  1. sort all the items by date descending
  2. put the items with the stickyIndex in the correct position of the result set
  • the id doesn’t have any meaning - just a reference for us to understand which item is which.

after this query i am supposed to get this result set:
{1, 4, 2, 3, 5} - each one represnts its id.