Example queries of time series data by using the wire listener

These examples show how to query time series data by using the MongoDB API or the REST API.

Before using these examples, you must configure the wire listener for the MongoDB or REST API. For more information, see Configuring the wire listener for the first time. These examples are run against the stores_demo database. For more information, see dbaccessdemo command: Create demonstration databases. These examples query the ts_data_v virtual table that stores the device ID in the loc_esi_id column.

For examples of aggregating or slicing time series data, see Aggregate or slice time series data.

List all device IDs

This query returns all unique device IDs.
MongoDB API
Run a distinct command on the ts_data_v virtual table:
db.ts_data_v.distinct("loc_esi_id")

Results:
["4727354321000111","4727354321046021","4727354321090954",...]
REST API
Request:
Specify the GET method on the stores_demo database with the query parameter specified:
GET /stores_demo/$cmd?query={"distinct":"ts_data_v",
"key":"loc_esi_id"}
Data:
None.
Response:
The following response indicates that the operation was successful:
[{"values":["4727354321000111","4727354321046021",
"4727354321090954",...],"ok":1.0}]

List device IDs that have a value greater than 10

This query returns the list of device IDs that have at least one measured value in the time series that is greater than 10.
MongoDB API
Run a distinct command on the ts_data_v table, with $gt value comparison operator specified:
db.ts_data_v.distinct("loc_esi_id",{"value":{"$gt":10}})

Results:
["4727354321046021","4727354321132574","4727354321289322",...]
REST API
Request:
Specify the GET method with the query condition on the ts_data_v table and the $gt value comparison operator specified:
GET /stores_demo/$cmd?query={"distinct":"ts_data_v",
"key":"loc_esi_id","query":{"value":{"$gt":10}}}
Data:
None.
Response:
The following response indicates that the operation was successful:
[{"values":["4727354321046021","4727354321132574",
"4727354321289322",...],"ok":1.0}]

Find the data for a specific device ID

This query returns the data for the device with the ID of 4727354321046021.
MongoDB API
Run a find command on the ts_data_v virtual table with the loc_esi_id value specified:
db.ts_data_v.find({"loc_esi_id":4727354321046021})

Results:
	{"loc_esi_id":"4727354321046021","measure_unit":"KWH",
 "direction":"P","tstamp":ISODate("2010-11-10T06:00:00Z"),
 "value":0.041}
	{"loc_esi_id":"4727354321046021","measure_unit":"KWH",
 "direction":"P","tstamp":ISODate("2010-11-10T06:15:00Z"),
 "value":0.041}
	{"loc_esi_id":"4727354321046021","measure_unit":"KWH",
 "direction":"P","tstamp":ISODate("2010-11-10T06:30:00Z"),
 "value":0.04}
...]
REST API
Request:
Specify the GET method on the ts_data_v virtual table, with the loc_esi_id specified on the query operator:
GET /stores_demo/ts_data_v?query=
{"loc_esi_id":4727354321046021}
Data:
None.
Response:
The following response indicates that the operation was successful:
[{"loc_esi_id":"4727354321046021","measure_unit":"KWH",
"direction":"P","tstamp":{"$date":1289368800000},"value":0.041},
 {"loc_esi_id":"4727354321046021","measure_unit":"KWH",
"direction":"P","tstamp":{"$date":1289369700000},"value":0.041},
 {"loc_esi_id":"4727354321046021","measure_unit":"KWH",
"direction":"P","tstamp":{"$date":1289370600000},"value":0.040},
 ...]

Find and sort data with multiple qualifications

This query finds all data for the device with the ID of 4727354321046021 with a value greater than 10.0 and a direction of P. The query returns the tstamp and value fields, and sorts the results in descending order by the value field.

To query for specific dates when using the REST API, convert the dates to milliseconds since the epoch. For example:
  • 2011-01-01 00:00:00 = 1293861600000
  • 2011-01-02 00:00:00 = 1293948000000
MongoDB API
Run a find command on the ts_data_v table, with the $and boolean logical operator specified:
db.ts_data_v.find({"$and":[{"loc_esi_id":4727354321046021},
{"value":{"$gt":10.0}},{"direction":"P"}]},
{"tstamp":1,"value":1}).sort({"value":-1})

Results:
	{"tstamp":ISODate("2011-01-25T16:15:00Z"),"value":14.58}
	{"tstamp":ISODate("2011-01-26T00:45:00Z"),"value":12.948}
	{"tstamp":ISODate("2011-01-26T02:30:00Z"),"value":12.768}
	...
REST API
Request:
Specify the GET method on the ts_data_v table, with the $and boolean logical operator specified:
GET /stores_demo/ts_data_v?query={"$and":[{"loc_esi_id":
4727354321046021},{"value":{"$gt":10.0}},{"direction":"P"}]}
&fields={"tstamp":1,"value":1}&sort={"value":-1}
Data:
None.
Response:
The following response indicates that the operation was successful:
[{"tstamp":{"$date":1295972100000},"value":14.580},
 {"tstamp":{"$date":1296002700000},"value":12.948},
 {"tstamp":{"$date":1296009000000},"value":12.768},
 ...]

Find all data for a device in a specific date range

This query returns the data from midnight January 1, 2011 to January 2, 2011 for device ID 4727354321000111. The date that is queried is greater than 1293861600000 and less than 1293948000000. The query returns the tstamp and value fields.

MongoDB API
Run a find command on the ts_data_v table, with values specified for the $and boolean logical query operator:
db.ts_data_v.find({"$and":[{"loc_esi_id":"4727354321000111"},
{"tstamp":{"$gte":ISODate("2011-01-01 00:00:00")}},
{"tstamp":{"$lt":ISODate("2011-01-02 00:00:00")}}]},
{"tstamp":"1","value":"1"})

Results:
	{"tstamp":ISODate("2011-01-01T00:00:00Z"),"value":0.343 }
	{"tstamp":ISODate("2011-01-01T00:15:00Z"),"value":0.349 }
	{"tstamp":ISODate("2011-01-01T00:30:00Z"),"value":1.472 }
...]
REST API
Request:
Specify the GET method on the ts_data_v table in the stores_demo database, with values specified for the $and boolean logical query operator:
GET /stores_demo/ts_data_v?query={"$and":
[{"loc_esi_id":4727354321000111},{"tstamp":{"$gte":
{"$date":1293861600000}}},{"tstamp":{"$lt":
{"$date":1293948000000}}} ]}&fields={"tstamp":1,"value":1}
Data:
None.
Response:
The following response indicates that the operation was successful:
[{"tstamp":{"$date":1293840000000},"value":0.343},
 {"tstamp":{"$date":1293840900000},"value":0.349},
 {"tstamp":{"$date":1293841800000},"value":1.472},
 ...]

Find the latest data point for a specific device

This query sets the sort parameter to order the tstamp field in descending order and sets the limit parameter to 1 to return only the latest value. The device ID is 4727354321000111 and the query returns the tstamp and value fields.
MongoDB API
Run a find command on the ts_data_v table, with sort and limit values specified:
db.ts_data_v.find({"loc_esi_id":"4727354321000111"},
{"tstamp":"1","value":"1"}).sort({"tstamp":-1}).limit(1)

Results:
	{"tstamp":ISODate("2011-02-08T05:45:00Z"),"value":1.412 }
REST API
Request:
Specify the GET method on the ts_data_v table, with sort and limit values specified in the query parameter:
GET /stores_demo/ts_data_v?query={"loc_esi_id":4727354321000111}
&fields={"tstamp":1,"value":1}&sort={"tstamp":-1}&limit=1
Data:
None.
Response:
The following response indicates that the operation was successful:
[{"tstamp":{"$date":1297143900000},"value":1.412}]

Find the 100th data point for a specific device

This query sets the sort parameter to order the tstamp field in ascending order and sets the skip parameter to 100 to return the 100th value. The device ID is 4727354321000111 and the query returns the tstamp and value field.
MongoDB API
Run the find command on the ts_data_v table, with values specified for sort, limit and skip:
db.ts_data_v.find({"loc_esi_id":4727354321000111},
{"tstamp":1,"value":1}).sort({"tstamp":1}).limit(1).skip(100)

Results:
	{"tstamp":ISODate("2010-11-11T07:00:00Z"),"value":0.013}
REST API
Request:
Specify the GET method on the ts_data_v table, with values specified for sort, limit, and skip in the query parameter:
GET /stores_demo/ts_data_v?query={"loc_esi_id":4727354321000111}
&fields={"tstamp":1,"value":1}&sort={"tstamp":1}&limit=1&skip=100
Data:
None.
Response:
The following response indicates that the operation was successful:
[{"tstamp":{"$date":1289458800000},"value":0.013}]
Parent topic: Manage time series through the wire listener
Copyright© 2019 HCL Technologies Limited