Help with filtering output of sql sensor (json array ?)

Configuration
1

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.

2 
value_template: "{{ value_json.media_title }}"

One tip, copy and paste the whole json output in Dev tools/templates

{% set value_json = {"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"} %}
{{ value_json.media_title }}

Then you can play with it.

1 Like
3

Thanks ! That works ! And i will have a look in Dev tools/templates. Still learning !

1 Like