Sort by array's last element mongodb

Question

I was trying to sort documents by last interaction. meta_data.access_times is an array that update every time when user interacts and new date object append to the last element of the array. Is there any way to sort by array's last element?

Attempt 1 :

private Aggregation makeQuery(String userId) {
     return newAggregation(
          match(Criteria.where("user_id").is(userId)),
          sort(Sort.Direction.DESC, "$meta_data.access_times"),
          group(Fields.fields().and("first_name", "$meta_data.user_data.first_name").and("last_name", "$meta_data.user_data.last_name").and("profile_pic", "$meta_data.user_data.profile_pic").and("user_id", "$user_id").and("access_times", "$meta_data.access_times"))
      );
    }

Attempt 2 :

 private Aggregation makeQuery(String userId) {
        return newAggregation(
            match(Criteria.where("user_id").is(user_id)),
            group(Fields.fields().and("first_name", "$meta_data.user_data.first_name").and("last_name", "$meta_data.user_data.last_name").and("profile_pic", "$meta_data.user_data.profile_pic").and("user_id", "$user_id")).max("$meta_data.access_times").as("access_time"),
            sort(Sort.Direction.DESC, "access_time")
        );
    }

sample meta_data array in document

"meta_data" : { "access_times" : [ 
            ISODate("2017-06-20T14:04:14.910Z"), 
            ISODate("2017-06-22T06:27:32.210Z"), 
            ISODate("2017-06-22T06:27:35.326Z"), 
            ISODate("2017-06-22T06:31:28.048Z"), 
            ISODate("2017-06-22T06:36:19.664Z"), 
            ISODate("2017-06-22T06:37:00.164Z")
        ] }

Well we seem to be missing any representation of what your document actually looks like. Care to share it with us? — Neil Lunn
– Neil Lunn, Commented Jun 22, 2017 at 6:59
So the "last" is always appended and therefore the "latest"? Normally is but just checking. — Neil Lunn
– Neil Lunn, Commented Jun 22, 2017 at 7:06
What do you expect as output? That group is throwing me as it's not clear what to expect. Mostly since it's not valid. How about showing whole documents ( just 2 or 3 ) and what you expect to get as your query result. That would be clearer to interpret than your query attempt. The date part I already understand. — Neil Lunn
– Neil Lunn, Commented Jun 22, 2017 at 7:16

Januka samaranyake · Accepted Answer · 2017-06-25 03:09:02Z

2

I solves the problem by using $unwind operation.

 private Aggregation makeQuery(String userId) {
        return newAggregation(
            match(Criteria.where("user_id").is(userId)),
            unwind("$meta_data.access_times"),
            group(Fields.fields().and("first_name", "$meta_data.user_data.first_name").and("last_name", "$meta_data.user_data.last_name").and("profile_pic", "$meta_data.user_data.profile_pic").and("user_id", "$user_id")).max("$meta_data.access_times").as("access_time"),
            sort(Sort.Direction.DESC, "access_time")
        );
    }

answered Jun 25, 2017 at 3:09

Januka samaranyake

2,6072 gold badges34 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniele Tassone · Accepted Answer · 2017-06-29 14:32:49Z

1

When you don't know if the element to Push is Ordered or not (for example, an User that is pushing him Score...) you can use $push and $sort in order to have an ordered array, then you can just sort by "find({userId:yourUseId}.sort("metadata.access_time.0":-1).

This solution suppose your array are Ordered with $sort at creation/update time: LINK

When you are sure that the Push don't need a sort (for example you are Pushing a Access_Date for that User) you can $push and void $sort by using $operator (tnx Erdenezul). LINK

In theory you don't need an Index on the Array "access_time" if the find() is fetching only fews documents. Otherwise you can just add an index with {"metadata.access_time.0": -1}.

Good Luck!

edited Jun 29, 2017 at 14:32

answered Jun 29, 2017 at 9:13

Daniele Tassone

2,1942 gold badges18 silver badges26 bronze badges

7 Comments

Erdenezul Over a year ago

Add latest access into 0th element with push position 0 would be nice

Daniele Tassone Over a year ago

@Erdenezul do you think is not a viable solution?

Erdenezul Over a year ago

in order to avoid sort, pushing latest element at index 0 is more efficient.

Daniele Tassone Over a year ago

@Erdenezul oh you are right, $position can be used but I have just posted a general solution that work for any scenario.

Erdenezul Over a year ago

could you revise your answer with my idea, then I can vote up your answer

|

Collectives™ on Stack Overflow

Sort by array's last element mongodb

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related