Populate template_sensor values from JSON

nelbu · December 17, 2019, 12:48am

Hi!

I’m trying to scrape price by hour from my local electricity provider with their API, and by now, I have multiple sensors with the value with each hour, so it isn’t really useful for me right now.

What I’m trying, is to populate a template_sensor with the values from the JSON below, each value in his correspondent timestamp, provided by their API, but I can’t figure it right now.

{
   "indicator" : {
      "geos" : [
         {
            "geo_name" : "España",
            "geo_id" : 3
         }
      ],
      "magnitud" : [
         {
            "id" : 23,
            "name" : "Precio"
         }
      ],
      "short_name" : "2.0.DHA Coste de comcercialización",
      "step_type" : "linear",
      "id" : 1389,
      "values_updated_at" : "2019-12-15T20:21:31.000+01:00",
      "disaggregated" : false,
      "values" : [
         {
            "geo_name" : "España",
            "value" : 1.76,
            "geo_id" : 3,
            "tz_time" : "2019-12-15T23:00:00.000Z",
            "datetime_utc" : "2019-12-15T23:00:00Z",
            "datetime" : "2019-12-16T00:00:00.000+01:00"
         },
         {
            "datetime_utc" : "2019-12-16T00:00:00Z",
            "tz_time" : "2019-12-16T00:00:00.000Z",
            "datetime" : "2019-12-16T01:00:00.000+01:00",
            "geo_name" : "España",
            "value" : 1.65,
            "geo_id" : 3
         },
         {
            "geo_id" : 3,
            "value" : 1.61,
            "geo_name" : "España",
            "datetime" : "2019-12-16T02:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T01:00:00Z",
            "tz_time" : "2019-12-16T01:00:00.000Z"
         },
         {
            "datetime" : "2019-12-16T03:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T02:00:00Z",
            "tz_time" : "2019-12-16T02:00:00.000Z",
            "geo_id" : 3,
            "geo_name" : "España",
            "value" : 1.59
         },
         {
            "datetime_utc" : "2019-12-16T03:00:00Z",
            "tz_time" : "2019-12-16T03:00:00.000Z",
            "datetime" : "2019-12-16T04:00:00.000+01:00",
            "geo_name" : "España",
            "value" : 1.59,
            "geo_id" : 3
         },
         {
            "datetime" : "2019-12-16T05:00:00.000+01:00",
            "tz_time" : "2019-12-16T04:00:00.000Z",
            "datetime_utc" : "2019-12-16T04:00:00Z",
            "geo_id" : 3,
            "value" : 1.62,
            "geo_name" : "España"
         },
         {
            "datetime_utc" : "2019-12-16T05:00:00Z",
            "tz_time" : "2019-12-16T05:00:00.000Z",
            "datetime" : "2019-12-16T06:00:00.000+01:00",
            "geo_name" : "España",
            "value" : 1.74,
            "geo_id" : 3
         },
         {
            "tz_time" : "2019-12-16T06:00:00.000Z",
            "datetime_utc" : "2019-12-16T06:00:00Z",
            "datetime" : "2019-12-16T07:00:00.000+01:00",
            "value" : 1.81,
            "geo_name" : "España",
            "geo_id" : 3
         },
         {
            "tz_time" : "2019-12-16T07:00:00.000Z",
            "datetime_utc" : "2019-12-16T07:00:00Z",
            "datetime" : "2019-12-16T08:00:00.000+01:00",
            "geo_name" : "España",
            "value" : 1.75,
            "geo_id" : 3
         },
         {
            "geo_name" : "España",
            "value" : 1.78,
            "geo_id" : 3,
            "datetime_utc" : "2019-12-16T08:00:00Z",
            "tz_time" : "2019-12-16T08:00:00.000Z",
            "datetime" : "2019-12-16T09:00:00.000+01:00"
         },
         {
            "geo_id" : 3,
            "geo_name" : "España",
            "value" : 1.74,
            "datetime" : "2019-12-16T10:00:00.000+01:00",
            "tz_time" : "2019-12-16T09:00:00.000Z",
            "datetime_utc" : "2019-12-16T09:00:00Z"
         },
         {
            "geo_name" : "España",
            "value" : 1.71,
            "geo_id" : 3,
            "datetime_utc" : "2019-12-16T10:00:00Z",
            "tz_time" : "2019-12-16T10:00:00.000Z",
            "datetime" : "2019-12-16T11:00:00.000+01:00"
         },
         {
            "datetime" : "2019-12-16T12:00:00.000+01:00",
            "tz_time" : "2019-12-16T11:00:00.000Z",
            "datetime_utc" : "2019-12-16T11:00:00Z",
            "geo_id" : 3,
            "value" : 1.85,
            "geo_name" : "España"
         },
         {
            "datetime" : "2019-12-16T13:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T12:00:00Z",
            "tz_time" : "2019-12-16T12:00:00.000Z",
            "geo_id" : 3,
            "value" : 1.84,
            "geo_name" : "España"
         },
         {
            "tz_time" : "2019-12-16T13:00:00.000Z",
            "datetime_utc" : "2019-12-16T13:00:00Z",
            "datetime" : "2019-12-16T14:00:00.000+01:00",
            "value" : 1.82,
            "geo_name" : "España",
            "geo_id" : 3
         },
         {
            "datetime" : "2019-12-16T15:00:00.000+01:00",
            "tz_time" : "2019-12-16T14:00:00.000Z",
            "datetime_utc" : "2019-12-16T14:00:00Z",
            "geo_id" : 3,
            "geo_name" : "España",
            "value" : 1.83
         },
         {
            "geo_id" : 3,
            "value" : 1.82,
            "geo_name" : "España",
            "datetime" : "2019-12-16T16:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T15:00:00Z",
            "tz_time" : "2019-12-16T15:00:00.000Z"
         },
         {
            "geo_id" : 3,
            "geo_name" : "España",
            "value" : 1.83,
            "datetime" : "2019-12-16T17:00:00.000+01:00",
            "tz_time" : "2019-12-16T16:00:00.000Z",
            "datetime_utc" : "2019-12-16T16:00:00Z"
         },
         {
            "value" : 1.86,
            "geo_name" : "España",
            "geo_id" : 3,
            "tz_time" : "2019-12-16T17:00:00.000Z",
            "datetime_utc" : "2019-12-16T17:00:00Z",
            "datetime" : "2019-12-16T18:00:00.000+01:00"
         },
         {
            "datetime_utc" : "2019-12-16T18:00:00Z",
            "tz_time" : "2019-12-16T18:00:00.000Z",
            "datetime" : "2019-12-16T19:00:00.000+01:00",
            "value" : 1.91,
            "geo_name" : "España",
            "geo_id" : 3
         },
         {
            "datetime" : "2019-12-16T20:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T19:00:00Z",
            "tz_time" : "2019-12-16T19:00:00.000Z",
            "geo_id" : 3,
            "geo_name" : "España",
            "value" : 1.89
         },
         {
            "geo_id" : 3,
            "value" : 1.82,
            "geo_name" : "España",
            "datetime" : "2019-12-16T21:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T20:00:00Z",
            "tz_time" : "2019-12-16T20:00:00.000Z"
         },
         {
            "datetime" : "2019-12-16T22:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T21:00:00Z",
            "tz_time" : "2019-12-16T21:00:00.000Z",
            "geo_id" : 3,
            "geo_name" : "España",
            "value" : 1.66
         },
         {
            "datetime" : "2019-12-16T23:00:00.000+01:00",
            "datetime_utc" : "2019-12-16T22:00:00Z",
            "tz_time" : "2019-12-16T22:00:00.000Z",
            "geo_id" : 3,
            "geo_name" : "España",
            "value" : 1.63
         }
      ],
      "composited" : false,
      "tiempo" : [
         {
            "id" : 4,
            "name" : "Hora"
         }
      ],
      "name" : "Desglose eficiencia 2 periodos (DHA) 2.0.DHA Coste de comercialización"
   }
}

And my sensor is:

sensor:
  - platform: rest
    name: precioluz_ree
    method: GET
    resource_template: https://api.esios.ree.es/indicators/1389?start_date={{ now().strftime('%Y-%m-%d') }}T00:00:00&end_date={{ now().strftime('%Y-%m-%d') }}T23:50:00"
    headers:
      Accept: application/json; application/vnd.esios-api-v1+json
      Content-Type: application/json
      Host: api.esios.ree.es
      Authorization: !secret token_ree
      # Cookie:    
    value_template: '{{ value_json.indicator.values.value }}'
    scan_interval: 300

Any help is welcomed!

freshcoast · December 17, 2019, 1:16am

Here’s a hint. Use the Dev Tools template editor for testing. If you navigate there the first time, you’ll see an example of using json.

{% set my_test_json = {
  "temperature": 25,
  "unit": "°C"
} %}

That creates a variable containing JSON. You can literally copy and paste your sample JSON into that variable.

{% set my_test_json = {
   "indicator" : {
      "geos" : [
         {
            "geo_name" : "España",
            "geo_id" : 3
         }
      ],
...
} %}

Then you can test your JSON code. It’s easy to explore going piece by piece.

{{ my_test_json }}

Will dump out the complete set of JSON.

{{ my_test_json.indicator }}

Will dump the indicator value. When you dump values it gets interesting.

{{ my_test_json.indicator.values }}

Outputs:

<built-in method values of dict object at 0x69e43990>

It look like the JSON is converted into Python objects. A Python Dictionary has a values() function, so you can’t access using the shortcut, it’s invoking the function instead. Use the typical dictionary key accessor:

{{ my_test_json.indicator['values'] }}

Now it gets more interesting. That will output the values, which is a list, so you also need to index into the list. You’ll need to figure out which index to choose. If the API has a better way of reducing the values returned, look into that as well.

{{ my_test_json.indicator['values'] | length }}

Outputs 24, so there are 24 items in the list.

{{ my_test_json.indicator['values'][0] }}

Outputs the first element in the values list:

{'geo_name': 'España', 'value': 1.76, 'geo_id': 3, 'tz_time': '2019-12-15T23:00:00.000Z', 'datetime_utc': '2019-12-15T23:00:00Z', 'datetime': '2019-12-16T00:00:00.000+01:00'}

I listed everything step by step, hopefully that shows you how you can explore JSON template parsing in the template tool.

nelbu · December 18, 2019, 12:21pm

Thank you, very much @freshcoast
However my problem is with fetching each ‘value’ to create a sensor witch that values at the given timedate, to get something like this:

It would be easier if the API provided only current time price, but works by daterange. So I’m stuck with fetching each ‘value’ inside the main list of ‘values’ and translating it to a sensor

petro · December 18, 2019, 1:48pm

Well, you have a unque scenario. I think you should change your scan interval to be 1 hour, but this will work with a scan interval of 300 seconds.

What it does:

Takes the current utc time and sets minutes, seconds, and microseconds to zero.
Converts the modified utc time to a string formatted to match the utc times in your json response.
Filters your list down to candidate times (should only be 1, the current hour’s response).
Returns the current hours response if the current hour exists, otherwise returns zero.

sensor:
  - platform: rest
    name: precioluz_ree
    method: GET
    resource_template: https://api.esios.ree.es/indicators/1389?start_date={{ now().strftime('%Y-%m-%d') }}T00:00:00&end_date={{ now().strftime('%Y-%m-%d') }}T23:50:00"
    headers:
      Accept: application/json; application/vnd.esios-api-v1+json
      Content-Type: application/json
      Host: api.esios.ree.es
      Authorization: !secret token_ree
      # Cookie:    
    value_template: >
      {%- set time = utcnow().replace(minute=0).replace(second=0).replace(microsecond=0) %}
      {%- set time = time.strftime("%Y-%m-%dT%H:%M:%SZ") %}
      {%- set selections = value_json.indicator['values'] | selectattr('datetime_utc','eq',time) | list %}
      {{ selections[0].value | float if selections | length > 0 else 0.0 }}
    scan_interval: 300

dewy · May 10, 2022, 10:06am

I don’t care how old this post is, this comment needs to be the first thing that comes up in a web search. I struggled for ages trying to get MQTT to play ball with the following data:

{
	"values": [
		{
			"timestamp": "2022-05-10 09:17:27",
			"carType": 1,
			"ignitionOn": 0,
			"chargingOn": 0,
			"socPerc": 23.5,
			"socPercBms": null,
			"sohPerc": 100,
			"batPowerKw": 0,
			"batPowerAmp": 0,
			"batVoltage": 644.7,
			"auxVoltage": 12.3,
			"auxAmp": 0,
			"batMinC": 21,
			"batMaxC": 22,
			"batInletC": 62,
			"batFanStatus": 0,
			"speedKmh": 0,
			"odoKm": 31337,
			"cumulativeEnergyChargedKWh": 133.7,
			"cumulativeEnergyDischargedKWh": 13.37,
			"gpsLat": -0,
			"gpsLon": 0,
			"gpsAlt": 100
		}
	]
}

Using dev tools I was able to change “values” as the array name to something else and fix some stuff, which was explained by your comment that values() is getting called.

Your clear, step by step break down of @nelbu’s issue hopefully both helped nelbu, it has helped me grasp something I’ve spent more time than I care to admit looking at. Your contribution is amazing and thank you for taking the time to author and share it!