mongoDb advanced sorting on aggregation query

Question

I am trying to write a query on mongoDb to group the data by a particular field (storeId). Each group will have multiple records for same storeId. I need the records on each group to be ordered by a field (timeStamp). A yet better solution would be after sorting the records on each group, select the first record only so each group has only one record. This is what I tried:

const result = await deliverydays.aggregate([
    {
        $match: {
            "storeId": { $in: storeIds },
            "eci.openTimeStamp": { $ne: null },
            "eci.dayStatus": "OPEN"
        }

    },
    {
        $group: {
            _id: "$storeId",
            data: {
                $push: { openTimeStamp: "$eci.openTimeStamp", storeId: "$storeId" }
            },
            $sort: { openTimeStamp: 1 }
        }
    }
])

it is complaining with error "The field '$sort' must be an accumulator object" I tried this as well:

const result = await deliverydays.aggregate([
    {
        $match: {
            storeId: { $in: storeIds },
            "eci.openTimeStamp": { $ne: null },
            "eci.dayStatus": "OPEN"
        }

    },
    {
        $group: {
            _id: "$storeId",
            data: {
                $push: { openTimeStamp: "$eci.openTimeStamp", storeId: "$storeId" }
            }
        }
    },
    {
        $sort: { openTimeStamp: 1 }
    }
])

But still doesn't work. I realized that the second structure is only if I want to sort by "_id" or "data"

Any help would be appreciated. I apologize in advance if this is considered easy but I am new on the IT world and also I come from SQL server.

You should use $sort: { "data.openTimeStamp": 1 }

Ayoub
– Ayoub

2020-03-19 19:21:15 +00:00
Commented Mar 19, 2020 at 19:21 — Ayoub
– Ayoub, Commented Mar 19, 2020 at 19:21
It doesn't seem to work.

DoArNa
– DoArNa

2020-03-19 19:49:59 +00:00
Commented Mar 19, 2020 at 19:49 — DoArNa
– DoArNa, Commented Mar 19, 2020 at 19:49

flaviuratiu · Accepted Answer · 2020-03-19 20:28:20Z

1

If you need only the first record, you can try this:

const result = await deliverydays.aggregate([
{
    $sort: {
        "openTimeStamp": -1
    }
},
{
    $group: {
        _id: "$storeId",
        data: {
            $first: {
                'openTimeStamp': '$openTimeStamp',
                'storeId': '$storeId'
            }
        }
    }
}])

And if you need to return the entire record, you can try $$ROOT:

const result = await deliverydays.aggregate([
{
    $sort: {
        "openTimeStamp": -1
    }
},
{
    $group: {
        _id: "$storeId",
        data: {
            $first: '$$ROOT'
        }
    }
}])

I haven't included your match query and the eci prefix, so you can add those.

answered Mar 19, 2020 at 20:28

flaviuratiu

1739 bronze badges

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

1 Comment

DoArNa Over a year ago

Excellent answer, clean and it does exactly what the question is asking.

DoArNa · Accepted Answer · 2020-03-19 20:01:52Z

0

After too many tests and reading documentation I realized that mongo does the sorting a little bit different from SQL world. You need to sort the records before you group them. This is how the final query looks like if anyone needs it in the future:

        const result = await deliverydays.aggregate([
        {
            $match:{storeId:{$in:storeIds}, 
            "eci.openTimeStamp":{$ne:null}, 
            "eci.dayStatus": "OPEN"}

        },
        {
            $sort:{"eci.openTimeStamp": -1}
        },
        {
            $group:{_id: "$storeId", data:{$push:{openTimeStamp:"$eci.openTimeStamp", storeId: "$storeId"}}}
        }   
    ])

answered Mar 19, 2020 at 20:01

DoArNa

5422 gold badges11 silver badges31 bronze badges

Collectives™ on Stack Overflow

mongoDb advanced sorting on aggregation query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related