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
fieldnameto access a specific property. - String Literals: Specify
"string"to represent text. - Numeric Literals: Use
42to represent numbers. - Root Object: The
$symbol denotes the root of the data. - Ternary Operator: Apply
condition ? trueResult : falseResultto determine outputs based on a condition.
JSONata can be utilized for a wide range of data manipulation tasks from the simplest to the most complex.
Example 1: Flattening Nested JSON
Convert nested JSON objects into a flat key-value structure.
Incoming JSON Data
json{ "sensor": { "type": "temperature", "readings": { "current": 22, "average": 21 } } }
JSONata Transformation
json{ "sensor_type": sensor.type, "current_temperature": sensor.readings.current, "average_temperature": sensor.readings.average }
Transformed JSON Data
json{ "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
json{ "sensors": [ {"type": "temperature", "value": 22}, {"type": "humidity", "value": 55} ] }
JSONata Transformation
json{ "temperature_value": sensors[type='temperature'].value, "humidity_value": sensors[type='humidity'].value }
Transformed JSON Data
json{ "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
json{ "device": { "id": "device123", "status": { "temperature": { "value": 4, "unit": "C" }, "humidity": { "value": 75, "unit": "%" }, "light": "on" } } }
JSONata Transformation
json{ "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
json{ "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
json{ "values": [5, 10, 15, 20, 25] }
JSONata Transformation for Sum and Average
json{ "sum": $sum(values), "average": $average(values), "max": $max(values), "min": $min(values) }
Transformed JSON Data
json{ "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
json{ "event": { "start_time": "2020-01-01T08:00:00Z", "end_time": "2020-01-01T12:00:00Z" } }
JSONata Transformation for Human-Readable Date and Duration
json{ "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
json{ "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
json{ "sensorData": { "temperature": 22, "humidity": 78 } }
JSONata Transformation Using $ to Dynamically Access Values
json{ "temp_value": $lookup(sensorData, 'temperature'), "humidity_value": $lookup(sensorData, 'humidity') }
Transformed JSON Data
json{ "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
json{ "readings": { "value1": 10, "value2": 20, "value3": 30 } }
JSONata Transformation to Sum Values Dynamically
json{ "total": $sum(readings.*) }
Transformed JSON Data
json{ "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
json{ "eventInfo": { "startTime": "2021-12-01T09:00:00Z", "endTime": "2021-12-01T17:00:00Z" } }
JSONata Transformation for Dynamic Date Formatting
json{ "formattedStart": $fromMillis($toMillis($lookup(eventInfo, 'startTime')), '[Y0001]-[M01]-[D01]'), "formattedEnd": $fromMillis($toMillis($lookup(eventInfo, 'endTime')), '[Y0001]-[M01]-[D01]') }
Transformed JSON Data
json{ "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.
