Code Summary: Time Series Collections

To create a time series collection, called weather, use the db.createCollection method. The first argument should be the name of the collection, and the second argument should be an options object. The options object should contain the following fields, which align with the three components in a time series collection — time, metadata, and measurements.

• timeField - corresponds to the name of the field in the document that contains a valid BSON date type.
• metaField - optional but highly recommended field that is a label or tag associated with the time series. Typically this would be something like a device id or the name of the sensor that produces the measurements
• granularity - another optional field that indicates the frequency at which we want to collect data (seconds, minutes, hours).

db.createCollection("weather", {
  timeseries: {
    timeField: "timestamp",
    metaField: "metadata",
    granularity: "hours",
  },
})

After running the command you will see an output that looks like this:

{ ok: 1 }

To test that the internal clustered index is being used, run the following command. This command uses JavaScript dot notation to access fields relevant to index usage. It accesses the stages object, and the first array element within it – the $cursor object. This stage contains the queryPlanner.winningPlan object.

Note: explain output for queries on time-series collections will look different than your typical explain output. It should be read as an aggregation output.

db.weather.find({
  timestamp: ISODate("2021-05-18T00:00:00.000Z")
}).explain().stages[0].$cursor.queryPlanner.winningPlan

After running the query, you should see a stage property with a value of CLUSTERED_IXSCAN, indicating that the internal clustered index was used for the query:

{
  stage: 'CLUSTERED_IXSCAN',
  filter: {  },
  direction: 'forward',
  minRecord: ObjectId("607b76800000000000000000"),
  maxRecord: ObjectId("60a30380ffffffffffffffff")
}

MongoDB recommends adding one or more secondary compound indexes on the fields designated as the timeField and metaField. To create a secondary index on the metadata.sensorid and timestamp fields, use the following command:

db.weather.createIndex( { "metadata.sensorId": 1, "timestamp": 1 } )

After creating the secondary compound index, test it with the following command. This is a query (with explain) to find all documents with a specific sensorid sorted by timestamp:

db.weather.explain().find({ "metadata.sensorId": 5578 }).sort({ "timestamp": 1 })

In the explain output, check to see if the queryPlanner.winningPlan within the $cursor object/stage has an inputStage of IXSCAN and an indexName of metadata.sensorId_1_timestamp_1. If so, this indicates that the secondary index was used.

// truncated for space
stages: [
  {
    $cursor: {
      queryPlanner: {
...        
        winningPlan: {
          stage: "FETCH",
          inputStage: {
            stage: "IXSCAN",
            keyPattern: {
              "meta.sensorId": 1,
              "control.min.timestamp": 1,
              "control.max.timestamp": 1,
            },
            indexName: "metadata.sensorId_1_timestamp_1"
         ...
            },
          },
        },
      },
    },
  },