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 https //datatracker ietf org/doc/rfc9535/ 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 r ead json data processing a json message with jsonpath can either yield no value, a single value, or an array of values the docid\ wzx5o0po00yfahzxqixzw 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 docid\ wzx5o0po00yfahzxqixzw 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 measurementpath $ value timestamppath $ ts statuspath (optional) $ status tagpath (optional) $ location timestamplayout rfc3339 = "2006 01 02t15 04 05z07 00" result $ value > \[42 5] $ ts > \["2024 03 31t14 30 00z"] $ status > \["good"] $ location > \["factorya"] 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 measurementpath $ values\[ ] value timestamppath $ values\[ ] ts each object in the array results in a separate data point \[ ] operator is used to select all elements in an array timestamplayout rfc3339 = "2006 01 02t15 04 05z07 00" result $ values\[ ] value > \[42 5, 43] $ values\[ ] ts > \["2024 03 31t14 30 00z", "2024 03 31t14 31 00z"] t i mestamp 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 measurementpath $ measurements\[?(@ silo=='silo 1')] points\[ ] value timestamppath $ measurements\[?(@ silo=='silo 1')] points\[ ] ts \[?(@ 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 rfc3339 = "2006 01 02t15 04 05z07 00" result $ measurements\[?(@ silo=='silo 1')] points\[ ] value > \[55 2, 56 1] $ measurements\[?(@ silo=='silo 1')] points\[ ] ts > \["2024 03 31t14 30 00z", "2024 03 31t14 31 00z"] t i mestamp 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 measurementpath $ sensors value timestamppath $ sensors ts 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 rfc3339 = "2006 01 02t15 04 05z07 00" result $ sensors value > \[20 5, 21, , 30 0] $ sensors ts > \["2024 03 31t14 30 00z", "2024 03 31t14 31 00z", , "2024 03 31t14 40 00z"] t i mestamp 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 https //support smartbear com/alertsite/docs/monitors/api/endpoint/jsonpath html also https //www site24x7 com/tools/json path evaluator html r 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