Using JSONata in the Qubitro platform for data transformation allows for on-the-fly manipulation of data, turning complex JSON structures into straightforward key-value pairs.

The JSONata expressions you've been working with can be conveniently applied within Qubitro using Transformation Functions. These functions allow you to manipulate and process real-time data streams using JSONata syntax.

Understanding JSONata

JSONata is akin to SQL for databases, but designed specifically for JSON. It allows users to perform advanced data search, filter, and transformation with simplicity.

Basic Syntax and Operators

Familiarize yourself with some of the key JSONata syntax and operators:

• Field Access: Use fieldname to access a specific property.
• String Literals: Specify "string" to represent text.
• Numeric Literals: Use 42 to represent numbers.
• Root Object: The $ symbol denotes the root of the data.
• Ternary Operator: Apply condition ? trueResult : falseResult to determine outputs based on a condition.

Example 1: Flattening Nested JSON

Convert nested JSON objects into a flat key-value structure.

Incoming JSON Data

{
  "sensor": {
    "type": "temperature",
    "readings": {
      "current": 22,
      "average": 21
    }
  }
}

JSONata Transformation

{
  "sensor_type": sensor.type,
  "current_temperature": sensor.readings.current,
  "average_temperature": sensor.readings.average
}

Transformed JSON Data

{
  "sensor_type": "temperature",
  "current_temperature": 22,
  "average_temperature": 21
}

Example 2: Simplifying Array Data

Turn an array of objects into a series of individual key-value pairs.

Incoming JSON Data

{
  "sensors": [
    {"type": "temperature", "value": 22},
    {"type": "humidity", "value": 55}
  ]
}

JSONata Transformation

{
  "temperature_value": sensors[type='temperature'].value,
  "humidity_value": sensors[type='humidity'].value
}

Transformed JSON Data

{
  "temperature_value": 22,
  "humidity_value": 55
}

Example 3: Complex Data Transformation

Convert complex, multi-level IoT data into a single-level object with clear key-value pairs.

Incoming JSON Data

{
  "device": {
    "id": "device123",
    "status": {
      "temperature": {
        "value": 4,
        "unit": "C"
      },
      "humidity": {
        "value": 75,
        "unit": "%"
      },
      "light": "on"
    }
  }
}

JSONata Transformation

{
  "device_id": device.id,
  "temperature": device.status.temperature.value,
  "temperature_unit": device.status.temperature.unit,
  "humidity": device.status.humidity.value,
  "humidity_unit": device.status.humidity.unit,
  "light_status": device.status.light
}

Transformed JSON Data

{
  "device_id": "device123",
  "temperature": 4,
  "temperature_unit": "C",
  "humidity": 75,
  "humidity_unit": "%",
  "light_status": "on"
}

Example 4: Mathematical Operations

Perform basic arithmetic on sensor data, such as calculating the sum, average, or other mathematical transformations.

Incoming JSON Data

{
  "values": [5, 10, 15, 20, 25]
}

JSONata Transformation for Sum and Average

{
  "sum": $sum(values),
  "average": $average(values),
  "max": $max(values),
  "min": $min(values)
}

Transformed JSON Data

{
  "sum": 75,
  "average": 15,
  "max": 25,
  "min": 5
}

Example 5: Time Formatting

Transform and format timestamps from sensors to more human-readable formats or calculate durations between timestamps.

Incoming JSON Data

{
  "event": {
    "start_time": "2020-01-01T08:00:00Z",
    "end_time": "2020-01-01T12:00:00Z"
  }
}

JSONata Transformation for Human-Readable Date and Duration

{
  "start_date": $fromMillis($toMillis(event.start_time), '[Y0001]-[M01]-[D01]'),
  "end_date": $fromMillis($toMillis(event.end_time), '[Y0001]-[M01]-[D01]'),
  "duration_hours": ($toMillis(event.end_time) - $toMillis(event.start_time)) / (1000*60*60) & " hours"
}

Transformed JSON Data

{
  "start_date": "2020-01-01",
  "end_date": "2020-01-01",
  "duration_hours": "4 hours"
}

Example 6: Accessing Data Dynamically Using $ Syntax

Dynamically access properties in a JSON object without directly referencing the keys, useful for schemas where the exact keys might not be known in advance or may vary.

Incoming JSON Data

{
  "sensorData": {
    "temperature": 22,
    "humidity": 78
  }
}

JSONata Transformation Using $ to Dynamically Access Values

{
  "temp_value": $lookup(sensorData, 'temperature'),
  "humidity_value": $lookup(sensorData, 'humidity')
}

Transformed JSON Data

{
  "temp_value": 22,
  "humidity_value": 78
}

Example 7: Performing Calculations Dynamically

Use $ syntax to apply a generic calculation across multiple fields dynamically, which is helpful when dealing with varying data structures.

Incoming JSON Data

{
  "readings": {
    "value1": 10,
    "value2": 20,
    "value3": 30
  }
}

JSONata Transformation to Sum Values Dynamically

{
  "total": $sum(readings.*)
}

Transformed JSON Data

{
  "total": 60
}

Example 8: Dynamic Date Formatting

Format any date value in a JSON object dynamically, without knowing the key under which the date is stored.

Incoming JSON Data

{
  "eventInfo": {
    "startTime": "2021-12-01T09:00:00Z",
    "endTime": "2021-12-01T17:00:00Z"
  }
}

JSONata Transformation for Dynamic Date Formatting

{
  "formattedStart": $fromMillis($toMillis($lookup(eventInfo, 'startTime')), '[Y0001]-[M01]-[D01]'),
  "formattedEnd": $fromMillis($toMillis($lookup(eventInfo, 'endTime')), '[Y0001]-[M01]-[D01]')
}

Transformed JSON Data

{
  "formattedStart": "2021-12-01",
  "formattedEnd": "2021-12-01"
}

Conclusion

These examples illustrate the power of JSONata in transforming complex IoT data into easily manageable forms. Qubitro's integration of JSONata facilitates efficient data handling and decision-making processes in IoT systems.

For more comprehensive guides and concepts, visit the official JSONata Documentation.