CODEWITHSUNDEEP
javascriptnode.jsmongodbmongoosenosql
Fuso
Fuso

Reputation: 909

How do I pick then sort MongoDB documents and then find next document to mine?

I have a collection of documents, which I need to first narrow down by set criteria, then sort alphabetically by string value inside those documents — let's say that's a "search result". I then need to find document that matches a given _id and then pick a document next to it (before or after) from the above "search result".

Background:

I use mongoose to query my database via Node.js.

I have a set of "special sections" in my blog that are comprised of all the articles that must have three particular conditions associated within the keys in the document. I can get the list of articles belonging to said section like so:

const specialSectionListQuery = Article.find({
  tag: { $ne: "excluded" },
  [`collections.cameras`]: { $exists: true },
  status: "published",
})

To finish creating the "special section," I must sort the documents alphabetically via their title attribute:

.sort({ [`collections.cameras.as.title`]: "asc" })

Now I want to add a link to "next article within the same special section" at the bottom of such articles. I know _id and any other value needed from the current article. The above query gives me an ordered list of documents within the section so I can easily find it within that list specialSectionListQuery.findOne({ _id: "xxx" }).exec().

However, I need to find the next article within the above list. How do I do that?

My attempts thus far:

I tried to create article list via aggregation, which led me nowhere (I simply made my app do exactly the same thing — make a list for a "special sectin"):

Article.aggregate([
  {
    $match: {
      tag: { $ne: "excluded" },
      [`collections.cameras`]: { $exists: true },
      status: "published",
    },
  },
  {
    $sort: {
      [`collections.cameras.as.title`]: 1,
    },
  }
]).exec()

But I can't for the life of me figure out how to iterate to the next document in the list properly.

I have thought of saving the list in Node.js memory (variable) and then finding what I need via JavaScript but that can't be scalable.

I have considered creating a new collection and saving the above list there but that would require me to either 1) do it every time a document is altered/added/deleted via Node.js — which is a lot of code and it may break if I interact with database another way 2) rebuild the colleciton every time I run the query, but that feels like it'll lack in performance.

Please help and thank you!

P.S.: Example collection which should cover most of the cases I'm looking to solve for:

[
  {
    _id: 1,
    name: "Canon",
    collections: { cameras: { as: { title: "Half-Frame" } } },
    tag: "included",
    status: "published"
  },
  {
    _id: 2,
    name: "Pentax",
    collections: { cameras: { as: { title: "Full-Frame" } } },
    tag: "included",
    status: "published"
  },
  {
    _id: 3,
    name: "Kodak",
    collections: { film: { as: { title: "35mm Film" } } },
    tag: "included",
    status: "published"
  },
  {
    _id: 4,
    name: "Ricoh",
    collections: { cameras: { as: { title: "Full-Frame" } } },
    tag: "included",
    status: "published"
  },
  {
    _id: 5,
    name: "Minolta",
    collections: { cameras: { as: { title: "Half-Frame Review" } } },
    tag: "excluded",
    status: "published"
  },
  {
    _id: 4,
    name: "FED",
    collections: { cameras: { as: { title: "Full-Frame" } } },
    tag: "included",
    status: "draft"
  }
]

Upvotes: 2

Views: 664

Answers (1)

mickl
mickl

Reputation: 49975

One thing you can try is to extend your $sort by adding _id so that it always returns documents in deterministic order:

{
    $sort: {
        "collections.cameras.as.title": 1,
        _id: 1
    }
},
{
    $limit: 1
}

Once your first query returns the document with _id: 2 and collections.cameras.as.title: Full-Frame, you can use below query to get subsequent document:

{
    $match: {
        $and: [
            {
                tag: { $ne: "excluded" },
                "collections.cameras": { $exists: true },
                status: "published",
            },
            {
                $or: [
                    { 
                        $and: [
                            { "collections.cameras.as.title": {  $eq: "Full-Frame" } },
                            { "_id": { $gt: 2 } }
                        ]
                    },
                    { "collections.cameras.as.title": {  $gt: "Full-Frame" } }
                ]
            }
        ]
    }
},
{
    $sort: {
        "collections.cameras.as.title": 1,
        _id: 1
    }
},
{
    $limit: 1
}

In this case due to deterministic $sort you can exclude previously found document by adding additional filtering criteria and the order should be preserved.

Mongo Playground

Upvotes: 1

Related Questions