State limit of 255 chars - SQL sensor data

Configuration
1

I want the attributes from a sensor that has been updated.
So, I am extracting the attributes from a ‘states’ record using sql.

  - platform: sql
    db_url: mysql://user:pwd@core-mariadb/homeassistant?charset=utf8
    queries:
      - name: 'ab_booking_prev'
        query: >
          SELECT attributes  FROM `states` WHERE 
          entity_id = 'calendar.airbnb'
          and left(last_changed,10)< left(now(),10)
          order by last_changed DESC;
        column: 'attributes'

The attribute string is too long to load into an entities state’ (255 chars).

Any suggestions?

I would use json to extract three attributes.

2

If you are fine to take first 255 chars of it, you can use select left(attributes,255) as attributes... otherwise you need to store this incoming long field into your entity’s attributes field

3

I tried that idea but you cannot use json on an incomplete structure, and due to potential variability in data content length I can’t easily pad the cut data with a validating terminating string.

Any idea how to write the SQL result into an attribute of an entity ?

4

You are right, there is no way to set this with existing services. If you are going to extract some values from attributes column using json, can you use template processing in sensor definition?

5

So here is my current solution.

If json functions could be embedded in SQL it would be so much simpler.

  - platform: sql
    db_url: mysql://user:pwd@core-mariadb/homeassistant?charset=utf8
    queries:
      - name: 'ab_booking_prev_a'
        query: >
          SELECT left(attributes,120) as attribs  
          FROM `states` WHERE 
          entity_id = 'calendar.airbnb'
          and left(attributes,60) != 
          (select left(attributes,60) from states 
            where entity_id = 'calendar.airbnb' 
            and last_changed=(select max(last_changed) 
                              from states where entity_id = 'calendar.airbnb')
          )
          order by last_changed DESC;
        column: 'attribs'

      - name: 'ab_booking_prev_b'
        query: >
          SELECT substr(attributes,121,300) as attribs
          FROM `states` WHERE 
          entity_id = 'calendar.airbnb'
          and left(attributes,60) != 
          (select left(attributes,60) from states 
            where entity_id = 'calendar.airbnb' 
            and last_changed=(select max(last_changed) 
                              from states where entity_id = 'calendar.airbnb')
          )
          order by last_changed DESC;          
        column: 'attribs'


# A template sensor delivers the answer

      - name: ab prev book msg
        state: >
          {% set js_ab = (states('sensor.ab_booking_prev_a') + states('sensor.ab_booking_prev_b'))|from_json %}
          {{ js_ab.message}}
6

FYI somebody wrote a SQL with json HACS integration at GitHub - crowbarz/ha-sql_json: Updated SQL integration for Home Assistant that supports JSON attributes.