JSON time series

You can create a time series that contains JSON documents. Because JSON documents do not have a rigid structure, you avoid schema changes when the structure of the data changes. You can easily load and query the data.

The advantage of having time-based data unstructured is that the schema of the TimeSeries data type is simple and does not need to be altered as the data changes.

A JSON time series has the following advantages over creating a column for every type of value in the TimeSeries data type:

MongoDB and REST API clients

You can load JSON documents directly from MongoDB and REST API clients without formatting the data.

Application compatibility

The changes that you need to make to your application when you move your data into time series is minimized because you do not need to perform schema migration.

Variable schema

If the structure of your time-based data is likely to change, storing that data as unstructured data in JSON documents prevents the need to update your schema or your application. For example, if you have sensors that monitor every machine in a factory, when you add a machine, the new sensors might collect different types of data than existing sensors.

Simplified schema

If your schema for time-based data includes more columns than each record typically uses, or your records typically contain many NULL values, you can easily load data as unstructured JSON documents. For example, if you have 50 different measurements but each sensor collects only 5 of those measurements, each record has 45 NULL values.

Storing data as JSON documents might require more storage space and result in slower queries than storing data in individual columns in the TimeSeries row type.

Time series definition

Although you load JSON documents into your time series table, internally the database server stores the JSON documents as BSON. Therefore, when you create a TimeSeries row type, you include a BSON column to store the JSON documents. You can include only one BSON column in a TimeSeries data type. You can also include columns that have other data types.

The maximum size of a document in a BSON column in a TimeSeries data type is 4 KB.

Loading and querying JSON time series

You can load JSON documents into a time series with the time series input statement, through a virtual table, or by writing a loader program.

When you query a time series that contains JSON documents, the results are automatically cast from BSON to JSON. However, when you select data from a virtual table on a JSON time series, you must cast the BSON column to JSON to view the documents.

You can aggregate individual fields within JSON documents by specifying the field name in the aggregate operation. You cannot aggregate an entire BSON column. When you run the TSRollup, AggregateBy, or AggregateRange function, you must cast the results to a TimeSeries row type that has the appropriate types of columns for the results of the aggregate operation.