MongoDB queries can sometimes get quite complex and not everybody is an expert in writing such queries. It is therefore required that expert users prepare those queries in order to make them reusable for other users. However, some parts of a query might require dynamic user input, for example the match criteria of a query usually involves date ranges, numerical limits, etc. which need to be provided for every single query execution.

The solution to this problem are so-called query templates. A query template is a partially complete MongoDB query, which can be processed by a template engine to generate a fully valid MongoDB query.

Keep in mind that MongoDB queries for Bosch IoT Insights must comply with the extended JSON format.

The query template syntax allows parameter placeholders, loops, boolean conditions, or even simple arithmetic. The template engine then processes the query template together with some given user input to generate the complete MongoDB query.

Template Engine
Conditional query fragments
- Example for conditions
Loops

Template Engine

We use the FreeMarker template engine for processing query templates, which offers a very rich template syntax. The following sections give an overview of the most important features.

Parameters

Parameters are needed to allow user-specific input for each query execution which is based on a query template.

A parameter has a name, a data type, and a parameter type.

The parameter name has to be unique within the query template.

The data type defines the type of the parameter value. Currently, the following data types are supported:

String: a simple text value
Int: an integer value
Float: a floating point value
Boolean: a boolean value
Timestamp: an ISO 8601 string, for example 2017-07-08T12:00:00.000Z
Device: a valid ThingId of an existing device, for example testproject:sensor_2

The parameter type defines the way parameter values have to be given. Currently, the following parameter types are supported:

Scalar: a single parameter value
Range: a from-to value range
List: a list of parameter values
Map: a key-value mapping, while the key is always a string and the value is of the given data type

Within the query template, a parameter placeholder can be defined with ${parameter}, where parameter would be the name of the parameter. The template engine then replaces ${parameter} with the given value of the parameter.

Reserved parameter

There exists a reserved parameter "insights" which can not be used as name for a custom parameter.

This parameter can be used, for example, to enrich the result with the user executing the query or to restrict/expand the result based on the executing user's roles.

The following fields are available:

Field	Content
insights.user.id	Unique user id (User ID in the User information page)
insights.user.email	The email address (E-Mail in the User information page)
insights.user.displayName	The display name (Username in the User information page)
insights.user.internalName	A technical username based on the login provider
insights.user.roles	The roles assigned to the user as a list

Here is an example how to verify the user has a specific role:

<#if insights.user.roles?seq_contains("<project>_access")>
  "has_role":"<project>_access",
</#if>

Example for scalars

The following query template contains a placeholder for a parameter called user. The query template would have to define a parameter called user with the String data type and the Scalar parameter type:

[
    {
        "$match": {
            "name": "${user}"
        }
    }
]

When processing the query template, the placeholder for ${user} is simply replaced with the given input value for that parameter.

Example for ranges

The following query template contains a placeholder for a parameter called measurementDate. The query template would have to define a parameter called measurementDate with the Timestamp data type and the Range parameter type:

[
    {
        "$match": {
            "timestamp": {
                "$gte": {
                    "$date": "${measurementDate.from}"
                },
                "$lte": {
                    "$date": "${measurementDate.to}"
                }
            }
        }
    }
]

When processing the query template with a value { "from": "2017-07-01T12:00:00.000Z2, "to": "2017-07-10T12:00:00.000Z" }, the resulting query would look like this:

[
    {
        "$match": {
            "timestamp": {
                "$gte": {
                    "$date": "2017-07-01T12:00:00.000Z"
                },
                "$lte": {
                    "$date": "2017-07-10T12:00:00.000Z"
                }
            }
        }
    }
]

Conditional query fragments

Fragments of a query template can be made conditional using the if-directive. This directive can check for parameter values or also the existence of a value.

Example for conditions

The following query template contains a placeholder for a parameter called amountOfDocuments to limit the number of query results. The query template would have to define an optional parameter called amountOfDocuments with the Int data type and Scalar parameter type. Additionally, the query template defines an if-directive which checks if the desired parameter is given at all and then decides to include the fragment or not:

[
    {
        "$match": {
            "name": "Philipp"
        }
    }
    <#if amountOfDocuments??>
    , {
        "$limit": ${amountOfDocuments}
    }
    </#if>
]

When processing the query template with a value of 100, the resulting query would look like this:

[
    {
        "$match": {
            "name": "Philipp"
        }
    },
    {
        "$limit": 100
    }
]

Otherwise, if the parameter value is not given at all (since it was defined as optional), the resulting query would look like this:

[
    {
        "$match": {
            "name": "Philipp"
        }
    }
]

Loops

Loops can be used to repeat a query fragment several times using the list-directive. This directive can iterate over parameters of the types List or Map.

Example for lists

The following query template contains a placeholder for a parameter called fieldNames. The query template would have to define a parameter called fieldNames with the String data type and List parameter type. Additionally, the query template defines a list-directive to loop over the values of the parameter. The sep-directive is used to include a separator character (a comma in this case) between the list elements:

[
    {
        "$project": {
            <#list fieldNames as fieldName>
            "${fieldName}": 1 <#sep>,</#sep>
            </#list>
        }
    }
]

When processing the query template with values [ "timestamp", "position" ], the resulting query would look like this:

[
    {
        "$project": {
            "timestamp": 1,
            "position": 1
        }
    }
]

Example for maps

The following query template contains a placeholder for a parameter called fieldNames. The query template would have to define a parameter called fieldNames with String data type and Map parameter type. Additionally, the query template defines a list-directive to loop over the key-value pairs of the parameter. The sep-directive is used to include a separator character (a comma in this case) between the list elements:

[
    {
        "$project": {
            <#list fieldNames as key, value>
            "${key}": "${value}" <#sep>,</#sep>
            </#list>
        }
    }
]

When processing the query template with values { "timestamp": "$metaData.timestamp", "position": "$metaData.position" }, the resulting query would look like this:

[
    {
        "$project": {
            "timestamp": "$metaData.timestamp",
            "position": "$metaData.position"
        }
    }
]

Example for optional lists or maps

Sometimes, parameters of the types List or Map can also be optional. In this case, it is not necessary to use an additional if-directive, but just to split the list-directive into a list- and items-directive. The list-directive then checks if the parameter value is present at all and then iterates over the wrapped items-loop. Imagine the example for lists from above, which could also use an optional list parameter.

[
    {
        "$match": {
            "name": "Philipp"
        }
    }
    <#list fieldNames>
    , {
        "$project": {
            <#items as fieldName>
            "${fieldName}": 1 <#sep>,</#sep>
            </#items>
        }
    }
    </#list>
]