# 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.\
\&#xNAN;*(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:
```json
{
"name": "Mold",
"orderStartTime": "2024-06-12T07:07:43",
"orderCompletionTime": "2024-06-12T07:19:10",
"orderNo": 21919945,
"ordered": 50,
"delivered": 49,
"productType": "Bottle"
}
```
### Rename fields for outgoing MQTT payload
**Scenario:** Publish message content using different property names.
**JSONata Query**
```json
{
"machineName": name,
"start": orderStartTime,
"end": orderCompletionTime,
"itemsPlanned": ordered,
"itemsMade": delivered
}
```
**Output**
```json
{
"machineName": "Mold",
"start": "2024-06-12T07:07:43",
"end": "2024-06-12T07:19:10",
"itemsPlanned": 50,
"itemsMade": 49
}
```
### **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**
```jsonata
ordered
```
**Output of Query**
```jsonata
50
```
***
### **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**
```jsonata
productType = "Bottle"
```
**Output of Query**
```jsonata
true
```
***
### Add new calculated property to payload
**Scenario:** Add a `quality` property based on delivered ÷ ordered.
**JsonAta Query**
```json
$merge([$,
{
"quality": delivered / ordered
}
])
```
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**
```json
{
"name": "Mold",
"orderStartTime": "2024-06-12T07:07:43",
"orderCompletionTime": "2024-06-12T07:19:10",
"orderNo": 21919945,
"ordered": 50,
"delivered": 49,
"productType": "Bottle",
"quality": 0.98
}
```
### Create a compact summary string
**Scenario:** Create a single string with order number and quality percentage.
**JSONata Query**
```jsonata
"Order " & orderNo & " Quality: " & $string((delivered / ordered) * 100) & "%"
```
**Output**
```
"Order 21919945 Quality: 98%"
```
***
## Timestamp Handling
When working with datetime fields in your queries, you may need to convert timestamps from a specific format to [ISO8601](https://en.wikipedia.org/wiki/ISO_8601) 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:
```json
{
"name": "Line1",
"orderStartTime": "2024-06-20T06:30:38", // ISO8601 compliant timestamp
"orderNo": 21919698,
"orderedQuantity": 40,
"deliveredQuantiy": 38
}
```
**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**
```jsonata
$toUtcFromTimezoneId(orderStartTime, 'Europe/Copenhagen')
```
The value provided in the second parameter (`Europe/Copenhagen`) must be defined as an [IANA Time Zone](https://timeapi.io/documentation/iana-timezones).
**Output**
```json
"2024-06-20T04:30:38.0000000+00:00"
```
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:
```json
{
"name": "Line1",
"orderCompletionTime": "20-06-2024T06:39:18", // Non ISO8601 compliant timestamp
"orderNo": 21919698,
"orderedQuantity": 40,
"deliveredQuantiy": 38
}
```
**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**
```json
$fromMillis(
$toMillis(orderCompletionTime,'dd-MM-yyyyTHH:mm:ss'),
"yyyy-MM-ddTHH':'mm':'ss")
```
The *fromMillis* method, interprets the *orderCompletionTime* timestamp using [.Net datetime format string convention](https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings), 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**
```json
"2024-06-20T06:39:18"
```
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:
```json
$fromMillis(
$toMillis(orderCompletionTime,'dd-MM-yyyyTHH:mm:ss'),
"yyyy-MM-ddTHH':'mm':'ss.fffK")
```
**Output as UTC**
```json
"2024-06-20T06:39:18.000Z"
```
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.
{% hint style="info" %}
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.
{% endhint %}
***
### Manually convert timestamp from custom format to ISO8601 preserving milliseconds
In the following example we assume that the incoming Json Payload is:
```json
{
"name": "Line1",
"orderCompletionTime": "20-06-2024T06:39:18.988Z", // Non ISO8601 compliant timestamp
"orderNo": 21919698,
"orderedQuantity": 40,
"deliveredQuantiy": 38
}
```
**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**
```json
$fromMillis(
$toMillis(orderCompletionTime,'dd-MM-yyyyTHH:mm:ss.fffK'),
"yyyy-MM-ddTHH':'mm':'ss.fffK")
```
The *fromMillis* method, interprets the *orderCompletionTime* timestamp using [.Net datetime format string convention](https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings), 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**
```json
"2024-06-20T06:39:18.988Z"
```
