I have tried this both via rest and influxdb, but cannot get the value I need. I usually query influxdb with a REST client (insomnia) for debugging; this query will provide the desired output http://<ip>:<port>/query?db=people&q=SELECT%20gewicht%20from%20gewicht%20WHERE%20%22person%22%20%3D%20%27somebody%27%20GROUP%20BY%20*%20ORDER%20BY%20DESC%20LIMIT%201. I can also curl this URL and get the same output.
The output will look like this {"results":[{"statement_id":0,"series":[{"name":"gewicht","columns":["time","gewicht"],"values":[["2020-02-18T08:55:57.259891138Z",60.9]]}]}]}.
Here is my rest attempt to get the 60.9 from the output above; I want the sensor to only display 60.9 or 60.9 kg:
sensor:
- platform: rest
method: GET
username: !secret influxdb_username
password: !secret influxdb_password
authentication: basic
resource: "http://<ip>:<port>/query?db=people&q=SELECT%20gewicht%20from%20gewicht%20WHERE%20%22person%22%20%3D%20'somebody'%20GROUP%20BY%20*%20ORDER%20BY%20DESC%20LIMIT%201"
value_template: ' {{ value_json.results.statement_id.values }}'
# tried countless variatons of the value_template above without success
scan_interval: 3600
I had an influxdb sensor as well, but due to overwriting it with the rest one, I cannot paste it. However, neither worked and just displayed unknown as value. I don’t quite understand how to structire the value_template properly. Would somebody please help me out?
Now, the value for the results key is an array (designated by the square brackets), with another dictionary as the first element of the array. So you need value_json.results[0].
After that, the nicer structure should make it fairly clear what you need. I think value_json.results[0].series.values[1] should do the trick.
Thank you. Unfortunately, your template does not seem to work. I get “unknown” again. I will try and change the rest sensor to influxdb (which it queries either way, anyway) and see if that makes a difference.
Since it is kind of a pain to do this in Home Assistant (because I constantly have to restart it to test the templte), I used curl and jq for this. When I run curl <url> | jq '.results[0].series[0].values[0], I will receive
[
"<timestamp>",
60.9
]
This is as far as I can get with jq; so in Home Assistant I use this format, and instead of unknown, I just get an empty field for value. However, this does output both the timestamp and the actual value. I assume I need to specify this somehow…?
UPDATE curl <url> | jq '.results[0].series[0].values[0][1] will work; when I use it in Home Assistant, I will get unknown again