Bosch IoT Insights

Data Explorer: Linking measurement data to Thing history

The Thing history data of a specific device can be displayed with the matching device type configuration that has been configured under Devices > All Devices by using a MongoDB aggregation query. This query can be created with a template that can be created in Explore > Data Explorer.

Linking processed data with the matching device configuration

The following query serves as an example aggregation which can be altered to fit the specific data structure.

images/confluence/download/attachments/1298257686/Thing_History_Example1_Device_Config-version-1-modificationdate-1591620104000-api-v2.png

images/confluence/download/attachments/1298257686/Thing_History_Example1_Device_Config2-version-1-modificationdate-1591620104000-api-v2.png

First, the processed data is filtered by a unique device property, in this example the CCU Identifier, which is entered as an attribute. The ID is then used with the $lookup statement to find the matching device history entries (historyData).

{
"$match": {
"metaData.ccu": "${ccu}"
}
},
{
$lookup: {
"from": "demo-project_thing_history",
"localField": "metaData.ccu",
"foreignField": "snapshots.features.general.properties.ID",
"as": "historyData"
}
}

The history data is also filtered by the CCU, so only matching snapshots are used. The parameter $metaData.eventStartTs contains a timestamp which signifies when the data was created. Therefore, the matching device configuration historyData.snapshots.modified has to be older than this timestamp.

The parameters ${timestamp.from} and ${timestamp.to} filter the result according to a given time range attribute ${timestamp}, e.g. EventStart and TimeRange.

{
"$unwind": "$historyData"
},
{
"$unwind": "$historyData.snapshots"
},
{
"$match": {"$and": [
{
"historyData.snapshots.features.general.properties.ID": "${ccu}"
},
{
"$expr": {
"$lte": ["$historyData.snapshots.modified","$metaData.eventStartTs"]
}},{
"metaData.eventStopTs": {
"$gte": {
"$date": "${timestamp.from}"
},
"$lte": {
"$date": "${timestamp.to}"
}
}}
]}
}

Since the matching device configuration is the one whose timestamp historyData.snapshots.modified is closest to the timestamp of the thing history data metaData.eventStartTs, the parameter $sort can be used to ensure this entry is the first one. By grouping the entries by the Thing History Data ID _id afterwards, only this entry is passed on. If a device configuration attribute was entered, only the output will contain this specific history attribute instead of the whole history entry.

{
"$sort": {
"historyData.snapshots.modified": -1,
"metaData.eventStartTs": 1
}
},
{
"$group":{
_id: "$_id",
"MetaData": {$first:"$metaData"},
"Payload": {$first:"$payload"},
"DataModifiedAt": {$first:"$metaData.eventStartTs"},
"HistoryModifiedAt": {$first: "$historyData.snapshots.modified"},
"VersionHistory": {$first:"$historyData"},
"VersionHistoryProperties": {$first:"$historyData.snapshots.features.general.properties"},
<#if attribute??> "${attribute}": {$first: "$historyData.snapshots.features.general.properties.${attribute}"}</#if>
}
}

Whole query

The following code block contains the whole query of the example:

[
{
"$match": {
"metaData.ccu": "${ccu}"
}
},
{
$lookup: {
"from": "demo-project_thing_history",
"localField": "metaData.ccu",
"foreignField": "snapshots.features.general.properties.ID",
"as": "historyData"
}
},
{
"$unwind": "$historyData"
},
{
"$unwind": "$historyData.snapshots"
},
{
"$match": {"$and": [
{
"historyData.snapshots.features.general.properties.ID": "${ccu}"
},
{
"$expr": {
"$lte": ["$historyData.snapshots.modified","$metaData.eventStartTs"]
}},{
"metaData.eventStartTs": {
"$gte": {
"$date": "${timestamp.from}"
},
"$lte": {
"$date": "${timestamp.to}"
}
}}
]}
},
{
"$sort": {
"historyData.snapshots.modified": -1,
"metaData.eventStartTs": 1
}
},
{
"$group":{
_id: "$_id",
"MetaData": {$first:"$metaData"},
"Payload": {$first:"$payload"},
"DataModifiedAt": {$first:"$metaData.eventStartTs"},
"HistoryModifiedAt": {$first: "$historyData.snapshots.modified"},
"VersionHistory": {$first:"$historyData"},
"VersionHistoryProperties": {$first:"$historyData.snapshots.features.general.properties"},
<#if attribute??> "${attribute}": {$first: "$historyData.snapshots.features.general.properties.${attribute}"}</#if>
}
},
{
"$limit": 1000
}
]