Return the last “true” value for each group

by

The best way to do this is using the aggregation framework. You need to $group your documents by “user” and return the last document for each user using the $last accumulator operator but for this to work, you need a preliminary sorting stage using the $sort aggregation pipeline operator. To sort your documents, you need to consider both the “createdAt” field and the “user” field.

The last stage in the pipeline is the $match stage where you select only those last documents where “isAbandoned” equals true.

db.students.aggregate([
    { "$sort": { "user": 1, "createdAt": 1 } }, 
    { "$group": { 
        "_id": "$user", 
        "last": { "$last": "$$ROOT" }
    }}, 
    { "$match": { "last.isAbandoned": true } }
])

which returns something like this:

{ 
    "_id" : ObjectId("56c85244bd5f92cd78ae4bc1"),
    "last" : {
        "_id" : ObjectId("56cee51503b7cb7b0eda9c4c"),
        "user" : ObjectId("56c85244bd5f92cd78ae4bc1"),
        "studentName" : "Rajeev",
        "createdAt" : ISODate("2016-02-25T11:27:17.281Z"),
        "isAbandoned" : true
    }
}

To get the expected result, we need to use the $replaceRoot pipeline operator starting from verion 3.4 to promote the embedded document to the top level

{
    $replaceRoot: { newRoot: "$last" }
}

In older version, you need to use the $project aggregation pipeline operation to reshape our documents. So if we extend our pipeline with the following stage:

{ 
    "$project": { 
        "_id": "$last._id", 
        "user": "$last.user", 
        "studentName": "$last.studentName", 
        "createdAt": "$last.createdAt", 
        "isAbandoned": "$last.isAbandoned"
}}

it produces the expected output:

{
    "_id" : ObjectId("56cee51503b7cb7b0eda9c4c"),
    "user" : ObjectId("56c85244bd5f92cd78ae4bc1"),
    "studentName" : "Rajeev",
    "createdAt" : ISODate("2016-02-25T11:27:17.281Z"),
    "isAbandoned" : true
}

More Related Contents:

  1. Retrieve only the queried element in an object array in MongoDB collection
  2. Update MongoDB field using value of another field
  3. Get names of all keys in the collection
  4. How to filter array in subdocument with MongoDB [duplicate]
  5. Does MongoDB’s $in clause guarantee order
  6. MongoDB: Combine data from multiple collections into one..how?
  7. Multiple join conditions using the $lookup operator
  8. Conditional $sum in MongoDB
  9. Return only matched sub-document elements within a nested array
  10. Converting string to date in mongodb
  11. MongoDB Full and Partial Text Search
  12. Mongodb Join on _id field from String to ObjectId
  13. exclude fields in $lookup aggregation
  14. Add some kind of row number to a mongodb aggregate command / pipeline
  15. Mongo sort on a calculated condition
  16. MongoDB sort documents by array elements
  17. MongoDB: Calculate dwell time between every status value change
  18. MongoDB: how to parse date in 3.6 mongoDb version?
  19. Search on multiple collections in MongoDB
  20. Using stored JavaScript functions in the Aggregation pipeline, MapReduce or runCommand
  21. MongoDB – The argument to $size must be an Array, but was of type: EOO / missing
  22. convert iso date to timestamp in mongo query
  23. Remove Duplicates from MongoDB
  24. Using $slice operator to get last element of array
  25. How to access overall document count during arithmetic aggregation expression
  26. Mongodb aggregation pipeline how to limit a group push
  27. Mongodb concat int and string
  28. Mongodb, aggregate query with $lookup
  29. mongodb aggregate query isn’t returning proper sum on using $sum
  30. MongoDB aggregate within daily grouping [duplicate]

Leave a Comment