skip to Main Content

In Mongo DB I have a number of documents.

They each have a insertion_date column which is Date type.

Will store data in following format:

2024-04-03T07:00:00:00.000Z

I want to fetch all the latest documents based on latest date.

Lets say the collection has 20 documents.

The insertion_date for 10 of them is: 2024-04-03T07:00:00:00.000Z

And remaining 10 is: 2019-01-02T01:00:00:00.000Z

The latest date between these is 2024-04-03T07:00:00:00.000Z.

So fetch all 10 records that has this latest insertion_date.

There can be multiple dates. So find latest date and all records that fall under this date.

I will not know before hand the number of records. It is 10 in this example only.

I am trying to write a query for this in NodeJS using Mongoose.

I could query it as follows but it ends up returning all 20 records.

I can’t limit it cos I do not know there are 10 records.

Could I get some help with this pls.

router.get('/products/:locale', async (req, res) => {
  const locale = req?.params?.locale;
  try {
    const products = await Product.find({ locale })
      .sort({ insertion_date: -1 })
      // .limit(10);
    res.json(products);
  } catch (error) {
    // error handling
  }
});

2

Answers


  1. If I understood your question correctly, I think this could be done with two queries. First, you will need to find the latest insertion date in your collection. Then, you can query the documents that have this latest insertion date. Something like this should work,

    router.get('/products/:locale', async (req, res) => {
      const locale = req?.params?.locale;
      try {
        // find the latest insertion date
        const latestInsertionDate = await Product.find({ locale })
          .sort({ insertion_date: -1 })
          .select('insertion_date');
    
        // query documents with the latest insertion date
        const products = await Product.find({ locale, insertion_date: latestInsertionDate.insertion_date });
    
        res.json(products);
      } catch (error) {
        // handle error here
      }
    });
    

    Edit: Alternatively, you could make use of the aggregation framework approach. But It still will involve multiple queries under the hood. I can’t seem to find any way around it.

    Login or Signup to reply.
  2. This can be done with one query using Model.aggregate:

    const products = await Product.aggregate([
      {
        $sort: {
          "insertion_date": -1
        }
      },
      {
        $facet: {
          docs: []
        }
      },
      {
        $project: {
          docs: {
            $filter: {
              input: "$docs",
              as: "doc",
              cond: {
                $eq: [
                  "$$doc.insertion_date",
                  {
                    $arrayElemAt: [
                      "$docs.insertion_date",
                      0
                    ]
                  }
                ]
              }
            }
          }
        }
      },
      {
        $unwind: "$docs"
      },
      {
        $replaceWith: "$docs"
      }
    ])
    

    See HERE for a working example with some simple illustrative documents added.

    Explanation:

    1. $sort – by insertion_date: -1.
    2. $facet – using this stage after the $sort is efficient if the $sort made use of an index on insertion_date and I would advise you add one if you haven’t already. It therefore won’t do a COLLSCAN and will transform your collection into a single array named docs.
    3. $project the docs array but…
    4. $filter the docs array to only return documents where the insertion_date is $eq to the insertion_date of the first element from docs array.
    5. $unwind the docs array .
    6. $replaceWith – will set the root of the results to the unwound docs.
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search