Bosch IoT Insights

Creating the query template


images/confluence/download/attachments/1019379892/i-getting-started-template-version-1-modificationdate-1565966466000-api-v2.png

You have uploaded your data to Bosch IoT Insights. This section provides more details of how Bosch IoT Insights evaluates your data using a query template.

The Data Explorer works like the Google search engine. You use the Google search bar to find what you are looking for. The same applies to the Data Explorer: it enables you to find out certain things about your data. The search is done by means of query templates. Query templates consist of individual operators which are small snippets of code. You can read, search, and select your data using a search request. The aggregated search results can be displayed on your dashboard in the form of graphics.

Example:

You want to know the average water consumption of a particular unit. The avg operator (see MongoDB) allows you to aggregate all water consumption data in the system and use it to calculate an average figure. This figure is then output in graph form on the dashboard.

Proceed as follows

  1. To evaluate data, click the Explore menu item.

  2. Click the Data Explorer tab.

  3. To create your first query template, click Add Query Template.
    → The Template Designer is displayed:

    images/confluence/download/attachments/1019379892/getting_started_template_creation-version-3-modificationdate-1687420173000-api-v2.png

  4. In the Name field, enter a name for the query template, e.g. Water consumption as per flat and day.

  5. In the Description field, add a description for the query template, e.g. Get the water consumption for Unit 1 for a specific date range.

  6. Click the Access rights button to define the access rights for the desired user roles. Refer to the Managing query template access rights chapter.

  7. In the Template Definition pane, enter the following content of the query template for our example.

    The actual text of the query template is created using the MongoDB Aggregation Reference and Freemarker Language Reference code languages. MongoDB Framework is a database tool that can handle extremely large volumes of complex data. Bosch IoT Insights thus offers you an optimal basis for your future IoT projects.

    [{
    "$unwind": "$payload.measurements"
    },
    {
    "$match": {
    "payload.measurements.timestamp": {
    "$gte": {
    "$date": "2018-06-30T22:00:00.000Z"
    },
    "$lte": {
    "$date": "2018-07-30T22:00:00.000Z"
    }
    }
    }
    },
    {
    "$project": {
    "_id": 0,
    "metadata": -1,
    "meter": "$payload.measurements.meter",
    "value": "$payload.measurements.value",
    "timestamp": "$payload.measurements.timestamp"
    }
    },
    {
    "$match": {
    "meter": "Flat 1"
    }
    },
    {
    "$group": {
    "_id": {
    "meter": "$meter",
    "day": {
    "$dateToString": {
    "format": "%Y-%m-%d",
    "date": "$timestamp"
    }
    }
    },
    "value": {
    "$sum": "$value"
    }
    }
    },
    {
    "$project": {
    "_id": 0,
    "Flat No": "$_id.meter",
    "Day": "$_id.day",
    "Liter": {
    "$trunc": "$value"
    },
    "Measurements": "$count"
    }
    },
    {
    "$sort": {
    "Flat No": 1,
    "Day": 1
    }
    }
    ]

  8. Click the Create New Query Template button.
    → The query template has been created and can be used as a data source for a widget.