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.
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 }]