MQTT Query Editor

Query Editor

The Query Editor is used for two main purposes:

  1. Transforming incoming MQTT payloads into a format compatible with your Tag type.

  2. Customizing outgoing MQTT payloads when publishing.

  3. Defining timestamp extraction for measurements read from MQTT topics.

Layout and Features

MQTT Query Editor based on JsonAta

  • Example JSON Payload Paste an example of the expected payload. (If the payload was already defined in the MQTT Topic configuration, it is auto-filled.)

  • JSONata Query Editor Enter a JSONata expression to extract the desired value from the example payload.

  • JSONata Query Output A preview of the result after applying your expression.

  • Output Validation Indicates whether the output can be parsed into the expected data type:

    • String Tag → String

    • Analog Tag → Double

    • Digital Tag → Boolean

    • Timestamp extraction → ISO8601 timestamp

Example JsonAta Queries

In the following example we assume that the incoming Json Payload is:

Rename fields for outgoing MQTT payload

Scenario: Publish message content using different property names.

JSONata Query

Output

Extract number as Analog Tag value

Scenario: You want to store the amount of delivered items into an Analog Tag. This means the ordered value must be extracted.

JsonAta Query

Output of Query

Extract product Type as Digitial Tag

Scenario: You want to Store whether the product type is "Bottle" as a Digital Tag. This means the query must return a boolen (true or false) whether the productType has the value "Bottle"

JsonAta Query

Output of Query

Add new calculated property to payload

Scenario: Add a quality property based on delivered ÷ ordered.

JsonAta Query

The above query takes the exsisting json payload using the $ character, and merges that with a new property called quality that is a calculation of delivered divided by the ordered items.

Output of Query

Create a compact summary string

Scenario: Create a single string with order number and quality percentage.

JSONata Query

Output

Timestamp Handling

When working with datetime fields in your queries, you may need to convert timestamps from a specific format to ISO8601 standard format. You may also need to convert timestamps from a specific timezone to UTC in ISO8601 standard format.

Tricloud Nexus expects all timestamps to be returned as valid ISO8601 format in UTC timezone

  • ISO8601 format: yyyy-MM-ddTHH:mm:ss.sssZ

  • Examples of valid ISO8601 date in UTC format:

    • 2024-08-16T15:00:00.000Z

    • 2024-08-16T15:00:00.000+00:00

If the payload timestamp is in another format or timezone:

  • Use $toUtcFromTimezoneId() to convert from a known local timezone.

  • Use $fromMillis() with a custom date format string to reformat the timestamp to ISO8601.

Convert timestamp from Local Time (Europe/Copenhagen) to UTC

In the following example we assume that the incoming Json Payload is:

Scenario: interpret the timestamp in orderCompletionTime as a Local Timestamp from the Europe/Copenhagen timzone, and convert this into an ISO8601 UTC timestamp.

JSONata Query

The value provided in the second parameter (Europe/Copenhagen) must be defined as an IANA Time Zone.

Output

As you can see, the Timnestamp i formatted in ISO8601 and is converted to UTC (Two hours substracted during summer time, to find the equivalent UTC timestamp)

Manually convert timestamp from custom format to ISO8601

In the following example we assume that the incoming Json Payload is:

Scenario: A timestamp is not in ISO8601 format, and should be converted into a compatiable format. Use the following syntax to change the format of a timestamp to ISO8601:

JSONata Query

The fromMillis method, interprets the orderCompletionTime timestamp using .Net datetime format string convention, then converts that into a unix timestamp (milliseconds since 1. jan, 1970). The toMillis method is then applied to convert the unix timestamp into an ISO8601 compliant timestamp using .Net format string to define the position of the Year yyyy, Month MM, Day dd, Hour HH, Minute mm, Seconds ss

Output as local time

By moving the characters around, we now have a fully compliant ISO8601 timestamp as Output.

You can also denote the JSONata Query, so the timestamp is inepreted as UTC, using the following syntax:

Output as UTC

Timestamp is outputtet with the Z character as the last char. It essentially represents a UTC offset of zero. The "Z" is often referred to as "Zulu" time, particularly in military and aviation contexts.

You dont have to output timestamps with the Z identifier to denote its UTC.

In Nexus all timestamps is interpreted as UTC timestamps by default.

Manually convert timestamp from custom format to ISO8601 preserving milliseconds

In the following example we assume that the incoming Json Payload is:

Scenario: A timestamp is not in ISO8601 format, and should be converted into a compatiable format and preserving the milliseconds while interpreting the timestamp as UTC.

JSONata Query

The fromMillis method, interprets the orderCompletionTime timestamp using .Net datetime format string convention, then converts that into a unix timestamp (milliseconds since 1. jan, 1970). The toMillis method is then applied to convert the unix timestamp into an ISO8601 compliant timestamp using .Net format string to define the position of the Year yyyy, Month MM, Day dd, Hour HH, Minute mm, Seconds ss and milliseconds fff finally the K is will write the Z identifier, or timezone information.

Output

PreviousMQTTNextOPC-UA

Last updated

Was this helpful?