How can I use aggreagate and want to see executionStats infomation in the explain method.

[byeonghun-lee]

Hello, I tried to see executionStats at explain method.

User.aggregate()
        .match({ age: { $gt: 30 } })
        .lookup({ from: "campaign", localField: "campaign", foreignField: "_id", as: "campaign" })
        .unwind("$campaign")
        .match({ "campaign.title": "test" })
        .explain();

The result of this is:

{ stages: [ { '$cursor': [Object] }, { '$lookup': [Object] } ],
  serverInfo:
   { host: '-',
     port: '-',
     version: '4.4.10',
     gitVersion: '-' },
  ok: 1,
  '$clusterTime':
   { clusterTime:
      Timestamp { _bsontype: 'Timestamp', low_: 387, high_: 1640589687 },
     signature: { hash: [Binary], keyId: [Long] } },
  operationTime:
   Timestamp { _bsontype: 'Timestamp', low_: 387, high_: 1640589687 } }

But if I use aggregate in mongo shell I can see executionStats;

$db.user.explain("executionStats").aggregate([{$match: {age: {$gt: 30}, ....])

{queryPlanner: ... , executionStats: ...}

Is there any way to see the executionStats when using explain in monoose like the mongo shell?

node: v10
mongoose : v5.8.9

[Uzlopak]

Yes. As you would do in the mongo shell

User.aggregate()
        .match({ age: { $gt: 30 } })
        .lookup({ from: "campaign", localField: "campaign", foreignField: "_id", as: "campaign" })
        .unwind("$campaign")
        .match({ "campaign.title": "test" })
        .explain("executionStats");

[vkarpov15]

The below script includes both queryPlanner and executionStats. Make sure that there's data in the collection you're aggregating on: a common mistake is that explain() returns an object like the one you specified, no queryPlanner or executionStats, if you're aggregating on a collection that doesn't exist.

'use strict';
  
const mongoose = require('mongoose');

const { Schema } = mongoose;

run().catch(err => console.log(err));

async function run() {
  await mongoose.connect('mongodb://localhost:27017/test');

  await mongoose.connection.dropDatabase();

  const User = mongoose.model('User', Schema({ name: String }));

  await User.create({ name: 'test' });

  const res = await User.aggregate([{ $match: { name: 'test' } }])
        .explain();

  console.log(res);
}

Output:

{
  queryPlanner: {
    plannerVersion: 1,
    namespace: 'test.users',
    indexFilterSet: false,
    parsedQuery: { name: [Object] },
    optimizedPipeline: true,
    winningPlan: { stage: 'COLLSCAN', filter: [Object], direction: 'forward' },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1,
    executionTimeMillis: 0,
    totalKeysExamined: 0,
    totalDocsExamined: 1,
    executionStages: {
      stage: 'COLLSCAN',
      filter: [Object],
      nReturned: 1,
      executionTimeMillisEstimate: 0,
      works: 3,
      advanced: 1,
      needTime: 1,
      needYield: 0,
      saveState: 0,
      restoreState: 0,
      isEOF: 1,
      direction: 'forward',
      docsExamined: 1
    },
    allPlansExecution: []
  },
  serverInfo: {
    host: 'localhost',
    port: 27017,
    version: '4.4.0',
    gitVersion: '563487e100c4215e2dce98d0af2a6a5a2d67c5cf'
  },
  ok: 1
}

That being said, it does look like we don't support passing a verbosity param to explain(), so we'll add that for the next release.