Value_template json: please help structure template

Hi everybody,

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?

Thank you for your input :slight_smile:

It probably helps to start by pretty-formatting the JSON reply. If you expand it out, it looks like:

{
  "results": [
    {
      "statement_id": 0,
      "series": [
        {
          "name": "gewicht",
          "columns": [
            "time",
            "gewicht"
          ],
          "values": [
            [
              "2020-02-18T08:55:57.259891138Z",
              60.9
            ]
          ]
        }
      ]
    }
  ]
}

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

    value_template: "{{ value_json.results[0].series[0].values[0][1] }}"

If in template editor I define value_json like this

{% set value_json = {"results":[{"statement_id":0,"series":[{"name":"gewicht","columns":["time","gewicht"],"values":[["2020-02-18T08:55:57.259891138Z",60.9]]}]}]} %}

this brings me 60.9

{{ value_json['results'][0]['series'][0]['values'][0][1] }}
2 Likes

Thank you. That works perfectly :slight_smile:

This helped me a lot too! Thx.