CODEWITHSUNDEEP
javascriptmongodbmongoosemongodb-queryaggregation-framework
Noah
Noah

Reputation: 4741

How to improve this aggregate with many $projects

I have created an aggregate function and I feel it's pretty long and non-DRY. I'm wondering what ways I can improve it.

My Thread model has a sub-document called revisions. The function tries to get the most recent revision that has the status of APPROVED.

Here is the full model.

{
  "_id": ObjectId("56dc750769faa2393a8eb656"),
  "slug": "my-thread",
  "title": "my-thread",
  "created": 1457249482555.0,
  "user": ObjectId("56d70a491128bb612c6c9220"),
  "revisions": [
    {
      "body": "This is the body!",
      "status": "APPROVED",
      "_id": ObjectId("56dc750769faa2393a8eb657"),
      "comments": [

      ],
      "title": "my-thread"
    }
  ]
}

And here is the aggregate function I want to improve.

Thread.aggregate([
  { $match: {
    slug: thread
  } },
  { $project: {
    user: '$user',
    created: '$created',
    slug: '$slug',
    revisions: {
      $filter: {
        input: '$revisions',
        as: 'revision',
        cond: { $eq: [ '$$revision.status', 'APPROVED' ] }
      }
    }
  } },
  { $sort: { 'revisions.created': -1 } },
  { $project: {
    user: '$user',
    created: '$created',
    slug: '$slug',
    revisions: { $slice: ["$revisions", 0, 1] }
  } },
  { $unwind: '$revisions'},
  { $project: {
    body: '$revisions.body',
    title: '$revisions.title',
    user: '$user',
    slug: '$slug',
    created: '$created'
  }}
])

Upvotes: 1

Views: 27

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50406

Well you cannot really since there are $sort and $unwind stages in between on purpose. It's also basically "wrong", since the $sort cannot re-order the array until you $unwind it first.

Then it is better to use $group and $first instead, to just get the first element from the sort in each document:

Thread.aggregate([
  { "$match": {
    "slug": thread
  } },
  { "$project": {
    "user": 1,
    "created": 1,
    "slug": 1,
    "revisions": {
      "$filter": {
        "input": "$revisions",
        "as": "revision",
        "cond": { "$eq": [ "$$revision.status", "APPROVED" ] }
      }
    }
  } },
  // Cannot sort until you $unwind
  { "$unwind": "$revisions" },

  // Now that will sort the elements
  { "$sort": { "_id": 1, "revisions.created": -1 } },

  // And just grab the $first boundary for everything
  { "$group": {
    "_id": "$_id",
    "body": { "$first": "$revisions.body" },
    "title": { "$first": "$revisions.title" },
    "user": { "$first": "$user" },
    "slug": { "$first": "$slug" },
    "created": { "$first": "$created" }
  }}
])

You could always reform the array with $push and then apply $arrayElemAt instead of the $slice to yield just a single element, but it's kind of superflous considering it would need another $project after the $group in the first place.

So even though there are "some" operations you can do without using $unwind, unfortunately "sorting" the arrays generated out of functions like $filter is not something that can be presently done, until you $unwind the array first.

If you didn't "need" the $sort on the "revisions.created" ( notably missing from your sample document ) then you can instead just use normal projection instead:

Thread.find(
    { "slug": slug, "revisions.status": "APPROVED" },
    { "revisions.$": 1 },
)

Only when sorting array elements would you need anything else, since the $ positional operator will just return the first matched element anyway.

Upvotes: 1

Related Questions