I hope somebody with more programming skills can help me. I created a sqlsensor which reads the latest data of a dummy binary sensor. The problem is that it outputs more than 255 characters. I tried to “downsize” it with a value_template but i have no idea how to get the data I want. The sensor now looks like this :
- platform: sql
queries:
- name: 'RadioState'
query: "SELECT * FROM states WHERE entity_id = 'binary_sensor.dummy' AND attributes LIKE '%media_title%' ORDER BY state_id DESC LIMIT 1;"
column: 'attributes'
value_template: "{{ attributes[0]['media_title'] }}"
When i run the SQL query below in DB browser i also get to much data.
query :
SELECT attributes FROM states WHERE entity_id = 'binary_sensor.dummy' AND attributes LIKE '%media_title%' ORDER BY state_id DESC LIMIT 1
output :
{"round_trip_time_avg": "0.056", "round_trip_time_max": "0.069", "round_trip_time_mdev": "", "round_trip_time_min": "0.045", "friendly_name": "dummy", "device_class": "connectivity", "entity_picture": "https://hass.heurik.nl/local/icons/radiozenders/radio-veronica.png", "media_title": "Radio Veronica", "last_station": "radiozender_veronica"}
I wan’t my sensor to only output the data of the “media_title” attribute. So i have to extract it from the json array above.