Read data from JSON

introduction to json what? json (javascript object notation) 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 read data from json /#examples section json settings measurementpath description the jsonpath that leads to a value, or a list of values in the message required yes timestamppath description the jsonpath that leads to a timestamp, or a list of timestamps in the message required no if empty the collector's timestamp will be assigned to data points timestamplayout description a timestamp layout is used to correctly interpret the resulting timestamp(s) from the timestamppath a list of available timestamp formats, along with their string representations, can be found in the golang time package documentation required no if empty the default rfc3339 timestamplayout e g 2006 01 02t15 04 05z07 00 is used for timestamps in unix format, one of the following values can be used unix (time in s), unixmillis (time in ms), unixmicros (time in μs), or unixnanos (time in ns) for other timestamp formats, see the read data from json /#examples below warning the measurement timestamplayout setting overrides the collector's timestamplayout setting timestamp layout examples timestamp in json message timestamplayout 2024 03 31t14 30 00z 2006 01 02t15 04 05z07 00 2024 03 31t14 30 00+02 00 2006 01 02t15 04 05z07 00 1711895400 unix 1711895400123 unixmillis custom timestamp layout examples timestamp in json message timestamplayout 31 03 2024 14 30 00 123 02 01 2006 15 04 05 000 sunday, 31 mar 2024 14 30 00 utc monday, 02 jan 2006 15 04 05 mst custom timestamp layouts allow flexibility for different timestamp representations you can define them according to your needs using go’s time layout reference here important if your timestamps use a different structure, make sure to adjust the timestamp layout accordingly statuspath description the jsonpath that leads to a status, or a list of statuses in the message required no if empty the status will default to 'good' tagnamesandtagvaluepaths description a map describing tags, where the map keys are the tag names, and the map values are the jsonpaths that lead to the tag values required no ignorejsonpatherrors collector setting the ignorejsonpatherrors setting determines whether errors in jsonpath expressions should be logged or not use ignorejsonpatherrors = true if your json format is dynamic and you expect some fields to be missing errors resulting from missing paths or invalid expressions are ignored use ignorejsonpatherrors = false if your json structure should always contain specific fields, and you want to log missing or malformed jsonpath expressions r ead 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) 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) 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 stored 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 2\) process flat json object with array of objects { "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 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 \[?(@ 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 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 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 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 here also jsonpath evaluato 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