Forum Discussion
Null values in flattened JSON
One way to verify if you get a correct JSON from Event hub is to do a data dump into a KQL table with a Dynamic column that takes the whole JSON object.
Then try something like this
| TempJSONTable
print extract_json("$.boundQueryResult", json, typeof(string));
I verified all of the above items to be proper and I eliminated the trim function. When I display the boundQueryResult column I get the following (data snipped for brevity):
[{"unit":"RTU01","point":"SetptSchedule","value":"Customer","status":"{ok} @ def"},{"unit":"RTU01","point":"ZoneTemp","value":"16.2 °F","status":"{fault,down,stale} @ def"},{"unit":"RTU01","point":"SupplyTemp","value":"-58.0 °F","status":"{fault,down,stale} @ def"},{"unit":"RTU01","point":"ActiveSetpoint","value":"0.0 °F","status":"{fault,down,stale} @ def"},{"unit":"RTU01","point":"SetpointOffset","value":"0.0 Δ°F","status":"{ok} @ def"},{"unit":"RTU01","point":"ReturnTemp","value":"-58.0 °F","status":"{fault,down,stale} @ def"},{"unit":"RTU01","point":"OutdoorTemp","value":"91.4 °F","status":"{ok} @ def"},{"unit":"RTU01","point":"ZoneAirQuality","value":"0 ppm","status":"{fault,down,stale} @ def"},{"unit":"RTU01","point":"IndoorHumidity","value":"0 %RH","status":"{fault,down,stale} @ def"}]
I put this into a JSON validator and it checked out perfectly yet I am still experiencing null values on my ouput table. When I download the JSON file from the display function above it shows all of the exit backslashes prior to the double quotes. Could these special characters be messing with the parsing?
Advocate IOne way to verify if you get a correct JSON from Event hub is to do a data dump into a KQL table with a Dynamic column that takes the whole JSON object.
Then try something like this
| TempJSONTable
print extract_json("$.boundQueryResult", json, typeof(string));