How to query attributes from Homeassistant SQL Database

Configuration
1

Hi all,

I wanted to extract the geodata of the device tracker from my external maria db. I can extract the state, but how can I see the longitude and the latitude attributes?

SELECT
  FROM_UNIXTIME(last_updated_ts, '%d %m %Y :%H:%i:%S') AS Datum, state
FROM states
WHERE
  state <> 'unknown' and
  state <> 'unavailable' and
  metadata_id = (
    SELECT
      metadata_id
    FROM
      states_meta
    WHERE
      entity_id = 'device_tracker.cupra_born_tracker'
    )
ORDER BY last_updated_ts

Results:

image
image488×665 7.9 KB

2

I use Postgresql rather than Mariadb, however the syntax is most likely similar. I believe you will need to extract the data from the JSON field ‘state_attributes.shared_attrs’ similar to this query against the device tracker from the iCloud3 integration.

Good hunting!

-- get position and altitude from icloud3 json 

SELECT
    states.state,
    to_char(to_timestamp(states.last_updated_ts), 'YYYY-MM-DD HH24:MI:SS') AS last_updated_ts,
    to_char(to_timestamp(states.last_changed_ts), 'YYYY-MM-DD HH24:MI:SS') AS last_changed_ts,
    state_attributes.shared_attrs :: json->'longitude' as longitude,
    state_attributes.shared_attrs :: json->'latitude' as latitude,
    state_attributes.shared_attrs :: json->'altitude' as altitude,
    state_attributes.shared_attrs,
    states.context_id_bin
FROM
    states
    LEFT JOIN states_meta ON (states.metadata_id = states_meta.metadata_id)
    LEFT JOIN state_attributes ON (states.attributes_id = state_attributes.attributes_id)
WHERE (states_meta.entity_id = 'device_tracker.iphonexi'
    AND to_timestamp(states.last_updated_ts) > now() - interval '1 days')
ORDER BY
    states.last_updated_ts DESC;
3

Hi David,

thank you very much for sharing the code.
I am struggling with these lines and I cannot translate it into mariaDB-language.

    state_attributes.shared_attrs :: json->'longitude' as longitude,
    state_attributes.shared_attrs :: json->'latitude' as latitude,
    state_attributes.shared_attrs :: json->'altitude' as altitude,
    state_attributes.shared_attrs,

Maybe someone can help with this, the rest seams to be olk!
Regards,
Spartacus

4

Quick google, return this web page from the MariaDB web site. As I said, I use Postgresql not Maria, so I can not validate this. However, the syntax in Maria while a bit different, does look similar. And hey, any example that uses Chicago Deep Dish Pizza :pizza: is a win 4 me, especially Lou Malnati’s :wink: .

Good hunting!

SELECT name, latitude, longitude,
    JSON_VALUE(attr, '$.details.foodType') AS food_type
FROM locations
WHERE type = 'R';

Using JSON in MariaDB

5

Hi David,

I am a little bit confused, because I will not get any values. I tried this:

SELECT 
JSON_VALUE(shared_attrs,'$.latitude') AS latitude,
JSON_VALUE(state_attributes.shared_attrs,'$.longitude') AS longitude
FROM state_attributes

an I always receive a “NULL”
image

but as you can see, there are data inside the database!

GPS
GPS1318×134 11.8 KB

Any ideas?

6

Unfortunately, you have exceeded my pay grade for MariaDB.
Hope someone with MariaDB expertise can chime in and help you.

My only thoughts:

  1. what version of MariaDB are you using? Maybe a function not supported.
  2. The database client you are using. Maybe an incompatible with it and JSON?
  3. If you are not getting any errors with the query you created the maybe the JSON path is wrong in the query or the data type of the latitude as a float is causing issues. I would try using a JSON path to a string value, such as ‘$.friendly_name’, see if that returns something other than (NULL).
  4. Maybe try and follow the steps in the article I cited to create their test table, insert some test JSON data and they see if you can match the query results for this test table with what the article says. Maybe that will give you some hints.

Good hunting!

7

Hi,

I have it!
This is the code:

> SELECT
>   states.state,
>   FROM_UNIXTIME(last_updated_ts, '%d %m %Y :%H:%i:%S') AS last_updated,
>   JSON_VALUE(state_attributes.shared_attrs, '$.latitude') as latitude,
>   JSON_VALUE(state_attributes.shared_attrs, '$.longitude') as longitude
> FROM states
> LEFT JOIN states_meta ON (states.metadata_id = states_meta.metadata_id)
> LEFT JOIN state_attributes ON (states.attributes_id = state_attributes.attributes_id)
> WHERE (states_meta.entity_id = 'device_tracker.golf_position' AND
>        timestamp(states.last_updated_ts) < NOW()- INTERVAL 1 DAY ) AND
>        state <> 'unknown' AND state <> 'unavailable'
> ORDER BY
>      states.last_updated_ts DESC

THe issue was, that there are so many entries in the list, that I never saw the longitude or latitude. If you filter out, you see the data.

SELECT 
JSON_VALUE(shared_attrs, '$.longitude') as latitude,
JSON_VALUE(shared_attrs, '$.latitude') as longitude
FROM state_attributes
WHERE JSON_VALUE(shared_attrs, '$.longitude')IS NOT null

Thanks for your support,
Spartacus

1 Like