CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkpymongo
rakesh kandukuri
rakesh kandukuri

Reputation: 305

Mongodb sorting issue when using facet

mongodb sorting is not working when using facet and mongo aggregation. results are not sorting after the sort function used. i am converting the utc date fromat to date string.

also need to remove the _id for each document

please help me on the query

Input json payload which using

{
    "_id": {
        "$oid": "122434543sdf"
    },
    "eventName": "ev1",
    "channelId": "channel1",
    "domain": "domain1",
    "lob": "lob1",
    "eventCategory": "category1",
    "producerCSI": "1234",
    "topicName": "topic1",
    "dateTime": "2021-12-29T20:04:37Z",
    "errorDetailsList": [{
        "errorType": "Missing data",
        "count": {
            "$numberLong": "1"
        }
    }, {
        "errorType": "Invalid Data",
        "count": {
            "$numberLong": "1"
        }
    }]
}
]
}

Mongo query i have written


 db.failureevents.aggregate( [
{$unwind: { "path": "$errorDetailsList", "preserveNullAndEmptyArrays": true} },
{$addFields: {errorType: {$arrayElemAt: [{$objectToArray: "$errorDetailsList"}, 0]}}},
{$addFields: {"errorType": "$errorType.v"}},
{$lookup : { "from": "errordescription", "localField": "errorType", "foreignField": "errorType", "as": "dataset" }},
{$unwind: { "path": "$dataset", "preserveNullAndEmptyArrays": true } },
 {$facet: {
    "top": [
 { "$group": {
        "_id": {
            "lob": "$lob",
            "channel": "$channelId",
            "domain": "$domain",
            "eventCategory": "$eventCategory",
            "prodcuerCSI": "$producerCSI",
            "topicName": "$topicName",
            "eventName": "$eventName",
            "errorType":"$dataset.errorType",
            "errorMessage":"$dataset.errorMessage",
            "dateTime":"$dateTime",
            "date" : { "$dateFromString" : { "dateString" : "$dateTime"} },
        },
        "errorCount": {"$sum" : "$errorDetailsList.count"},
    }
}],
 "rest": [ {$count: 'count'}]
}},
    { "$project": { "_id" : 0,"data": { "$concatArrays": ["$top", "$rest"] }}},
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" }},
  {$sort: {"date" : -1}},
   { $skip: 0 },
 { $limit: 100},
 ]);

Upvotes: 2

Views: 916

Answers (1)

Joe
Joe

Reputation: 28316

A $group stage in MongoDB aggregation retains only those fields in the the group specification.

In your example pipeline the $group stage in the top section will return documents that have only 2 top-level fields, _id and errorCount.

At the point {$sort: {"date" : -1}} is executed, there is no date field at the top level, so the sort has not effect.

Solution: either use {$sort: {"_id.date" : -1}} or use a projection that moves the fields embedded in _id to the top level before sorting.

Upvotes: 2

Related Questions