what is the difference between MongoDB find and aggregate in below queries?-CodePudding

select records using aggregate:

db.getCollection('stock_records').aggregate(
[
  {
    "$project": {
      "info.created_date": 1,
      "info.store_id": 1,
      "info.store_name": 1,
      "_id": 1
    }
  },
  {
    "$match": {
      "$and": [
        {
          "info.store_id": "563dcf3465512285781608802a"
        },
        {
          "info.created_date": {
            $gt: ISODate("2021-07-18T21:07:42.313 00:00")
          }
        }
      ]
    }
  }
])

select records using find:

db.getCollection('stock_records').find(
{
 'info.store_id':'563dcf3465512285781608802a',
 'info.created_date':{ $gt:ISODate('2021-07-18T21:07:42.313 00:00')}
})

What is difference between these queries and which is best for select by id and date condition?

CodePudding user response：

I think your question should be rephrased to "what's the difference between find and aggregate".

Before I dive into that I will say that both commands are similar and will perform generally the same at scale. If you want specific differences is that you did not add a project option to your find query so it will return the full document.

Regarding which is better, generally speaking unless you need a specific aggregation operator it's best to use find instead, it performs better

Now why is the aggregation framework performance "worse"? it's simple. it just does "more".

Any pipeline stage needs aggregation to fetch the BSON for the document then convert them to internal objects in the pipeline for processing - then at the end of the pipeline they are converted back to BSON and sent to the client.

This, especially for large queries has a very significant overhead compared to a find where the BSON is just sent back to the client.

Because of this, if you could execute your aggregation as a find query, you should.

CodePudding user response：

Aggregation is slower than find.

In your example, Aggregation

In the first stage, you are returning all the documents with projected fields

For example, if your collection has 1000 documents, you are returning all 1000 documents each having specified projection fields. This will impact the performance of your query.

Now in the second stage, You are filtering the documents that match the query filter.

For example, out of 1000 documents from the stage 1 you select only few documents

In your example, find

First, you are filtering the documents that match the query filter.

For example, if your collection has 1000 documents, you are returning only the documents that match the query condition.

Here You did not specify the fields to return in the documents that match the query filter. Therefore the returned documents will have all fields.

You can use projection in find, instead of using aggregation

db.getCollection('stock_records').find(
{
  'info.store_id': '563dcf3465512285781608802a',
  'info.created_date': { 
    $gt: ISODate('2021-07-18T21:07:42.313 00:00')
  }
},
{
  "info.created_date": 1,
  "info.store_id": 1,
  "info.store_name": 1,
  "_id": 1
}
)