Read data from JSON
Introduction to JSON
What?
JSON is a lightweight, text-based data format used to represent structured data. It organizes information as key-value pairs and arrays, making it easy for systems to exchange and interpret data.
Why?
JSON is widely used in modern systems, APIs, and IoT devices because of its simplicity, flexibility, and readability. Many collectors, dashboards, and monitoring tools rely on JSON messages to process and analyze real-time data efficiently.
What is JSONPath?
When receiving data in JSON format, it's crucial to accurately extract measurement values, timestamps, statuses, and tags. This is done using JSONPath expressions, which allow precise navigation through JSON structures to find and extract specific pieces of data.
This section outlines how JSONPath is used to process JSON messages in a consistent and structured way, enabling the generation of meaningful data points. For examples on how to use JSONPath, see the examples section.
Read JSON data
Processing a JSON message with JSONPath can either yield no value, a single value, or an array of values.
The JSONPath for the timestamp(s), status(es), and tag value(s)JSONPath for the timestamp(s), status(es), and tag value(s) have to either yield the same amount of results as the JSONPath for the Measurement value(s), or only a single value, in which case that single value will be used for all found measurement values.
The measurement value(s) JSONPathmeasurement value(s) JSONPath must return at least one value to be able to create data points.
Combinations of MeasurementPath and TimestampPath
The following options can be encountered when processing JSON messages with the combination of the JSONPath settings above:
Note: if TimestampPath is left empty, this will always result in 1 available timestamp for the message, which is the time the collector received the message.
- MeasurementPath or TimestampPath result in no value:
- No data points will be processed.
- MeasurementPath results in a single value and TimestampPath results in a single timestamp:
- A single data point will be processed with the value from MeasurementPath and the timestamp from TimestampPath.
- MeasurementPath results in a single value and TimestampPath results in multiple timestamps, or vice versa:
- Only a single timestamp with multiple values is allowed. The reverse is not allowed: if there is a single value and multiple timestamps, an error will occur due to unequal numbers of values and timestamps.
- MeasurementPath results in multiple values and TimestampPath results in multiple timestamps, with the amount of values and timestamps equal:
- Multiple data points are processed, where each value is combined with each timestamp (e.g. value 1 with timestamp 1, value 2 with timestamp 2, ..).
- MeasurementPath results in multiple values and TimestampPath results in multiple timestamps, with the amount of values and timestamps not equal:
- An error is logged on the collector, and no data points are processed.
Combinations of Status and TagNamesAndTagValuePaths
The following logic applies when handling Status and TagNamesAndTagValuePaths in relation to the number of values they return:
Note: Both Status and TagNamesAndTagValuePaths must either apply uniformly to all data points or be clearly aligned one-to-one per data point.
- StatusPath and/or TagNamesAndTagValuePaths return a single value:
- The single status and/or tag value is applied to all data points. This is useful when a constant status or set of tags is intended for every point in the measurement.
- StatusPath and/or TagNamesAndTagValuePaths return multiple values, and the number of values is equal to the number of data points:
- Each status and/or tag value is applied to the corresponding data point as follows: the first value is assigned to the first data point, the second value assigned to the second, and so on.
- StatusPath and/or TagNamesAndTagValuePaths return multiple values, but the number of values is not equal to the number of data points:
- An error is logged on the collector, and no data points are processed.
- StatusPath and/or TagNamesAndTagValuePaths return zero values:
- The status is registered as default status value Good and data points are processed.
- The tag is disregarded and data points are processed.
Important: Mixing a different number of values for different JSONPath expressions (e.g., Status leads to one value, TagNamesAndTagValuePaths leads to multiple values) is only desirable if that one status is meant to apply uniformly to all data points, and there is a tag value to apply to each data point.
Examples
1) Process a flat JSON object:
{
"ts": "2024-03-31T14:30:00Z",
"value": 42.5,
"status": "Good",
"location": "FactoryA"
}JSONPath Expressions
TimestampLayout:
Result:
Timestamp | Value | Status (tag) | Location (tag) |
|---|---|---|---|
2024-03-31T14:30:00Z | 42.5 | Good | FactoryA |
{
"values": [
{
"ts": "2024-03-31T14:30:00Z",
"value": 42.5
},
{
"ts": "2024-03-31T14:31:00Z",
"value": 43.0
}
]
}JSONPath Expressions:
- Each object in the array results in a separate data point.
- [*] operator is used to select all elements in an array.
TimestampLayout:
Result:
Timestamp | Value |
|---|---|
2024-03-31T14:30:00Z | 42.5 |
2024-03-31T14:31:00Z | 43 |
3) Process nested arrays and/or objects in a JSON object with filter
{
"measurements" :
[
{
"silo": "Silo_1",
"points": [
{
"ts": "2024-03-31T14:30:00Z",
"value": 55.2
},
{
"ts": "2024-03-31T14:31:00Z",
"value": 56.1
}
]
},
{
"silo": "Silo_2",
"points": [
{
"ts": "2024-03-31T14:30:00Z",
"value": 65.2
},
{
"ts": "2024-03-31T14:31:00Z",
"value": 66.1
}
]
}
]
}JSONPath Expressions:
To extract points of Silo 1, the following JSONPaths can be used:
- [?(@.silo=='Silo_1')] is a filter expression.
- @ refers to the current element of the array (measurements).
- @.silo=='Silo_1' is a condition: it selects only the elements where the silo key has the value Silo_1.
- So this part of the JSONPath filters the measurements array to only include entries related to Silo_1.
TimestampLayout:
Result:
Timestamp | Value |
|---|---|
2024-03-31T14:30:00Z | 55.2 |
2024-03-31T14:31:00Z | 56.1 |
4) Process dynamic object keys in JSON object with wildcards
Sometimes, measurements in a JSON message are stored under dynamically named keys. For example, the object may contain keys such as temp1, temp2, ..., temp9999, where the numeric suffix can vary and is not known ahead of time.
To handle such cases generically, you can use JSONPath wildcard expressions * to extract values from all keys within an object, regardless of their specific names.
Important: Wildcards will match all keys at that level, not just keys that follow a specific pattern like temp*.
{
"sensors":
{
"temp1": {
"value": 20.5,
"ts": "2024-03-31T14:30:00Z"
},
"temp2": {
"value": 21.0,
"ts": "2024-03-31T14:31:00Z"
},
"pressure1": {
"value": 30.0,
"ts": "2024-03-31T14:40:00Z"
}
}
}JSONPath Expressions:
- These paths will match all child objects under sensors, regardless of whether the keys start with temp, pressure, or any other prefix.
- In the example above, the wildcard * will include both temperature and pressure sensor values.
TimestampLayout:
Result:
Timestamp | Value |
|---|---|
2024-03-31T14:30:00Z | 20.5 |
2024-03-31T14:31:00Z | 21 |
... | ... |
2024-03-31T14:40:00Z | 30.0 |
For a better understanding of JSONPath, compare these examples to those of an external resource here. Also JSONPath Evaluator is a useful tool to test a JSONPath against a JSON message.
Troubleshooting
No data points created
Check that MeasurementPath matches at least one value.
Unequal amount of timestamps and measurements
Ensure arrays of measurements and timestamps are equal in length.