Sorting and grouping nested subdocument in Mongoose

Question

I have a schema, Comment, like the one below. It's a system of "comments" and "replies", but each comment and reply has multiple versions. When a user wants to view a comment, I want to return just the most recent version with the status of APPROVED.

const Version = new mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User'
  },
  body: String,
  created: Date,
  title: String,
  status: {
    type: String,
    enum: [ 'APPROVED', 'OPEN', 'CLOSED' ]
  }
})

const Reply = new mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User'
  },
  created: Date,
  versions: [ Version ]
})

const Comment = new mongoose.Schema({
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User'
  },
  created: Date,
  versions: [ Version ],
  replies: [ Reply ]
})

I've gotten the parent Comment to display how I want with the code below. However, I've had trouble applying that to the sub-document, Reply.

const requestedComment = yield Comment.aggregate([
  { $match: {
    query
  } },
  { $project: {
    user: 1,
    replies: 1,
    versions: {
      $filter: {
        input: '$versions',
        as: 'version',
        cond: { $eq: [ '$$version.status', 'APPROVED' ] }
      }
    },
  }},
  { "$unwind": "$versions" },
  { $sort: { 'versions.created': -1 } },
  { $group: {
    _id: '$_id',
    body: { $first: '$versions.body' },
    title: { $first: '$versions.title' },
    replies: { $first: '$replies' }
  }}
])
.exec()

Any help achieving the same result with the replies subdocuments would be appreciated. I would like to return the most recent APPROVED version of each reply in a form like this:

comment: {
  body: "The comment's body.",
  user: ObjectId(...),
  replies: [
    {
      body: "The reply's body."
      user: ObjectId(...)
    }
  ]
}

Same result how? As in "What does that mean?" What is being sorted? Is it the "replies" themselves or the "versions" within the replies? Depending on which then to what end? Do you want the same "first by created" out of that "inner versions"? It's not a very clear question. — Blakes Seven
– Blakes Seven, Commented Mar 14, 2016 at 0:53
@BlakesSeven I want to get the latest version of each reply. I've updated the question. — Noah
– Noah, Commented Mar 16, 2016 at 0:24

Blakes Seven · Accepted Answer · 2016-03-21 05:43:55Z

3

+50

Basically you just need to continue the same process on from the existing pipeline. But this time to $unwind out the "versions" per each "replies" entry and $sort them there.

So these are "additional" stages to your pipeline.

// Unwind replies
{ "$unwind": "$replies" },
// Unwind inner versions
{ "$unwind": "$replies.versions" },

// Filter for only approved
{ "$match": { "replies.versions.status": "APPROVED" } },

// Sort on all "keys" and then the "version" date
{ "$sort": { 
    "_id": 1,
    "replies._id": 1,
    "replies.versions.created": -1
}},

// Group replies to get the latest version of each
{ "$group": {
    "_id": {
        "_id": "$_id",
        "body": "$body",
        "title": "$title",
        "replyId": "$replies._id",
        "replyUser": "$replies.user",
        "replyCreated": "$replies.created"
    },
    "version": { "$first": "$replies.version" }
}},

// Push replies back into an array in the main document
{ "$group": {
    "_id": "$_id._id",
    "body": { "$first": "$_id.body" },
    "title": { "$first": "$_id.title" },
    "replies": {
        "$push": {
            "_id": "$_id.replyId",
            "user": "$_id.replyUser" },
            "created": "$_id.replyCreated",       // <-- Value from Reply
            "body": "$version.body",              // <-- Value from specific Version
            "title": "$version.title"
        }
    }
}}

All depending of course on which fields you want, being either from ther Reply or from the Version.

Whichever fields, since you "un-wound" two arrays, you $group back "twice".

Once to get the $first items after sorting per Reply
Once more to re-construct the "replies" array using $push

That's all there is too it.

If you were still looking at ways to "sort" the array "in-place" without using $unwind, well MongoDB just does not do that yet.

Bit of advice on your design

As a note, I see where you are going with this and this is the wrong model for the type of usage that you want.

It makes little sense to store "revision history" within the embdedded structure. You are rarely going to use it in general update and query operations, and as this demonstrates, most of the time you just want the "latest".

So just do that instead, and store a "flag" indicating "revisions" if really necessary. That data can then be stored external to the main structure, and you won't have to jump through these hoops just to get the "latest accepted version" on every request.

answered Mar 21, 2016 at 5:43

Blakes Seven

50.5k14 gold badges132 silver badges136 bronze badges

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

3 Comments

Noah Over a year ago

regarding your suggestion, what do you mean by "stored external"? Within another collection? Then I'd have to use populate, but isn't that expensive? And you mean a "flag" to signify if an item has revisions or not, and basically handle it differently if it does? Then store the main revision at the top level?

Blakes Seven Over a year ago

@Noah That topic is a bit too broad, but I am generally just touching on that as a piece of advice. No I don't mean .populate() I mean to just only store the "latest revision" of anything in this structure that you are using. Then of course leave "revisions" in another collection entrirely. Your general usage pattern indicates that this is the data you are consistently using, and "revisions" are just something that are not really important for general "page display" etc. Only when you specifically "ask for" revisions data ( such as clicking on a link ) then would it be necessary to load it.

Blakes Seven Over a year ago

@Noah Point is. If you don't generally want the information in the display, then don't keep it in the same object. That saves you a lot of "jumping through hoops" with operations like this ( and noting that it is in "combination" with the existing pipeline ) just to get the "latest revision". Just fetching data is a lot faster than constructing such over complicated filtering.

Collectives™ on Stack Overflow

Sorting and grouping nested subdocument in Mongoose

1 Answer 1

Bit of advice on your design

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Bit of advice on your design

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related