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

Working with Data
#1

hi,

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)?

#2

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

db.test.drop();
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.

#3

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)

#4

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?

#5

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.