Sort by date Aggregation mongodb

Question

I am trying to sort documents by date using aggregation. But I was unable to. My attempt as below. Am I missing something?

public static JSONArray get_users_from_db(String botId, String pageId, MongoTemplate mongoTemplate) throws Exception {
        AggregationResults<AgentUserLogs> groupResults = mongoTemplate.aggregate(makeQuery(botId, pageId), "chatuser_log", AgentUserLogs.class);
        List<AgentUserLogs> list = groupResults.getMappedResults();
        JSONArray array = new JSONArray();
        Gson gson = new Gson();
        for (AgentUserLogs obj : list) {
            array.put(new JSONObject(gson.toJson(obj)));
        }
        return array;
    }

 private static Aggregation makeQuery(String botId, String pageId) {
        return newAggregation(
            match(Criteria.where("bot_id").is(botId).and("page_id").is(pageId)),
            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("last_message", "$live_agent.last_message").and("last_access_time", "$meta_data.last_access_time")),
            sort(Sort.Direction.DESC, "last_access_time")
        );
    }

public class AgentUserLogs {

    private String first_name;
    private String last_name;
    private String profile_pic;
    private String user_id;
    private Instant last_access_time;
    private Object last_message;

    @Override
    public String toString() {
        return "{" +
            "first_name='" + first_name + '\'' +
            "last_name='" + last_name + '\'' +
            "profile_pic='" + profile_pic + '\'' +
            "user_id='" + user_id + '\'' +
            "last_access_time='" + last_access_time + '\'' +
            "last_message='" + last_message + '\'' +
            "}";
    }
}

Sample document

{
    "_id" : ObjectId("5a0698755a640c6324a17581"),
    "bot_id" : "1",
    "page_id" : "2039339889632748",
    "user_id" : "1258922750901107"
    "meta_data" : 
        "user_data" : {
            "first_name" : "Januka",
            "last_name" : "Samaranayake",
            "profile_pic" : "https://scontent.xx.fbcdn.net/v/t1.0-1/23172506_1725189057492533_3460235097206138375_n.jpg?oh=5183e7dd4e8ac49a49491055c24696d6&oe=5AA59955",
        },
    },
    "live_agent" : {
        "last_message" : {
            "time" : "Sun Nov 12 12:24:53 IST 2017",
            "message" : "hh",
            "status" : "notRead"
        },
        "thread" : [ 
            {
                "from" : "user",
                "time" : "Sat Nov 11 15:23:10 IST 2017",
                "message" : {
                    "message" : "Default",
                    "type" : "init"
                }
            }, 
            {
                "from" : "user",
                "time" : "Sun Nov 12 11:08:55 IST 2017",
                "message" : {
                    "message" : "hi",
                    "type" : "text"
                }
            }, 
            {
                "from" : "agent",
                "time" : "Sun Nov 12 11:38:14 IST 2017",
                "message" : {
                    "message" : "hello",
                    "type" : "text"
                }
            }, 
            {
                "from" : "agent",
                "time" : "Sun Nov 12 11:42:31 IST 2017",
                "message" : {
                    "message" : "hi",
                    "type" : "text"
                }
            }, 
            {
                "from" : "agent",
                "time" : "Sun Nov 12 12:23:31 IST 2017",
                "message" : {
                    "message" : "hi",
                    "type" : "text"
                }
            }, 
            {
                "from" : "user",
                "time" : "Sun Nov 12 12:24:53 IST 2017",
                "message" : {
                    "message" : "hh",
                    "type" : "text"
                }
            }
        ],
        "connect" : false,
        "status" : "New"
    } }

What are you expecting? This is simply asking to "group by" a list of fields, and nothing else. Those are all "grouped" under _id BTW. So it's "_id.last_access_time". But somehow I don't think this is actually the query you even intend to do. — Neil Lunn
– Neil Lunn, Commented Nov 12, 2017 at 7:51
What I am trying to do is group documents with above fields and sorting results "last_access_time". — Januka samaranyake
– Januka samaranyake, Commented Nov 12, 2017 at 7:54
What do you "think" you mean by "group"? If you actually intend what is happening then sorting by the correct naming I gave you will actually work. Otherwise you actually mean something different to what you think "group" means to what it actually does. — Neil Lunn
– Neil Lunn, Commented Nov 12, 2017 at 7:57

Alex P. · Accepted Answer · 2017-11-12 12:28:03Z

I think you have a mistake in your $group as well as in your $sort

From the sample document that you pasted I can't tell where your last_access_time is actually stored, but from your model (AgentUserLogs) it looks like you have stored it with the rest fields that can be found under the field user_data. This is your last grouping field that is wrong:

and("last_access_time", "$meta_data.last_access_time")

I believe what you meant to write is:

and("last_access_time", "$meta_data.user_data.last_access_time")

Then in your $sort, since you are grouping by it it means it is accessible through _id.last_access_time. So your last sort pipeline has to look as follows:

sort(Sort.Direction.DESC, "_id.last_access_time")

According to your code, your aggregation pipeline all together should look like this (this is just an example):

db.getCollection('yourCollectionName').aggregate([{
        $match: {
            "bot_id": "1",
            "page_id": "2039339889632748"
        }
    },
    {
        $group: {
            _id: {
                first_name: "$meta_data.user_data.first_name",
                last_name: "$meta_data.user_data.last_name",
                profile_pic: "$meta_data.user_data.profile_pic",
                user_id: "$user_id",
                last_message: "$live_agent.last_message",
                last_access_time: "$meta_data.user_data.last_access_time"
            }
        }

    },

    {
        $sort: {
            "_id.last_access_time": -1
        }
    }
])

Collectives™ on Stack Overflow

Sort by date Aggregation mongodb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related