Querying, Analyzing, and Presenting Time Series Data in MongoDB

In this third part of the series on time‑series data and MongoDB, the article reviews methods for querying, analyzing, and presenting time‑series data stored in MongoDB.

The MongoDB aggregation framework provides a pipeline of stages such as $match, $project, $group, $sort, etc., enabling data preparation, transformation, and analysis directly within the database. An example pipeline is shown that extracts the daily maximum price for a stock from a collection containing per‑minute price documents.

Using MongoDB Compass, the same pipeline can be built visually and exported as code. The article includes a sample query:

db.getCollection('StockDocPerMinute').aggregate([
  { $match: { "symbol" : "FB" } },
  { $project: {
      "year": { $year: "$d" },
      "month": { $month: "$d" },
      "dayOfMonth": { $dayOfMonth: "$d" },
      "p": { $objectToArray: "$p" },
      "Symbol": 1
    }
  },
  { $addFields: { tmax: { $max: "$p.v" } } },
  { $group: {
      _id: { year: "$year", month: "$month", dayOfMonth: "$dayOfMonth" },
      "DayMax": { $max: "$tmax" }
    }
  },
  { $sort: { _id: -1 } }
])

Read‑only MongoDB views can be created to encapsulate such pipelines, providing an additional security layer by exposing only the view to clients. The article shows a view definition that converts per‑minute price subdocuments to a timestamped price series.

db.createView("ViewStock","StockDocPerMinute",[
  { $project: { p: { $objectToArray: "$p" }, d:1, symbol:1 } },
  { $unwind: "$p" },
  { $project: {
      _id:0,
      symbol:1,
      Timestamp: {
        $dateFromString: {
          dateString: {
            $concat: [
              { $dateToString: { format:"%Y-%m-%dT%H:%M:", date:"$d" } },
              { $concat: ["$p.k","Z"] }
            ]
          }
        }
      },
      price:"$p.v"
    }
  }
])

The MongoDB BI Connector allows SQL‑based BI tools such as Tableau, Power BI, QlikView, and TIBCO Spotfire to query MongoDB data directly, translating SQL queries to MongoDB’s query language and returning tabular results.

For large‑scale analytics, the MongoDB Spark connector exposes MongoDB collections to Apache Spark, enabling real‑time analysis and machine‑learning workloads without ETL. Similarly, the R driver (mongolite) lets data scientists retrieve time‑series data into R for statistical analysis and visualization, as demonstrated with a simple plot script.

library(mongolite)
ts = mongo(collection="StockDocPerSecond", db="Stock", url="mongodb://localhost:27020")
fbquery = ts$find('{ "symbol":"FB"}', fields='{"_id":0, "d":1, "p":1 }')
plot(fbquery$d, fbquery$p, xlab="Date", ylab="Price", main="Price over Time")
lines(lowess(fbquery$d, fbquery$p), col=2)

In summary, MongoDB’s flexible query capabilities, visual tools, BI connector, and integrations with Spark and R provide a comprehensive stack for handling time‑series workloads at scale, supporting both operational queries and advanced analytics.