Hi All,
i setup Grafana on my RPI3 and a MariaDB 10 database on my synology Nas.
Now it is time to set up Grafana but i am not able to extract data from the DB on Synology: i can access it and connect to Grafana but i have problem creating the right query to retrieve the data.
I am trying to use this
SELECT UNIX_TIMESTAMP(created) as time_sec,
'yr_temperature' as metric,
CAST(JSON_EXTRACT(JSON_EXTRACT(event_data, '$.new_state'), '$.state') AS DECIMAL(4,2)) as value
FROM `events`
WHERE event_type = 'state_changed'
and JSON_EXTRACT(event_data, '$.entity_id') = 'sensor.yr_temperature'
and $__timeFilter(created)
ORDER BY created ASC
But as far as i understood JSON_EXTRACT is not available in MariaDB 10 (only since 10.2.1) and in fact it does not work.
Now what should be the equivalent of JSON_EXTRACT for MariaDB or any syntax which will correctly extract data?
Thanks in advance to everyone who will help