Query
select Temperatur from AquaraBluetoothSensoren where Sensorname='Zimmer1' and time > now()-2h limit 1
Is Sensorname a tag? (sorry I shoud have asked you to show the tags also)
Yeah, Sensorname is a tag.
So you are returning one value back from influx, how do you end up with two values?
Where did you put the function(warn)?
Flow please
Flow:
In function node is
node.warn(typeof msg.payload[0].Temperatur);
node.warn(typeof msg.payload[0].time);
return msg;
because it is done for Temperatur and time there are two warn messages. Or when you exceute this flow with both simultaneously attached to the function node, 4 warn messages.
what do you get when you attach a debug node (set to display the complete msg object) to the output of the influx node?
So Influx is returning a array with one occurrence. You can access the information in the array a couple ways.
(1) without changing anything, you can access the temperature as msg.payload[0].Temperatur
(2) add a change node after the influx node and set msg.payload to msg.payload[0]. As a result, msg.payload will now be an object and you can access the temperature as msg.payload.Temperatur
Knowing the type of the data coming out of the influx node is important so you know how to access it.
However, when I manually inject the data it is also an array and leads to no error. Here the full flow, however due to influx node probably not so much useful. At least you can test the normal Inject node
[{"id":"85f4802f.350e6","type":"inject","z":"b8e3d69e.392308","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":880,"y":200,"wires":[["edf76622.862618"]]},{"id":"edf76622.862618","type":"influxdb in","z":"b8e3d69e.392308","influxdb":"52cc6f72.aa656","name":"influx","query":"select Temperatur from AquaraBluetoothSensoren where Sensorname='Zimmer1' and time > now()-2h limit 4","rawOutput":false,"precision":"","retentionPolicy":"","x":1030,"y":200,"wires":[["2cefcd6.978ec32","f3d969b3.f4cd18","e4953337.af321"]]},{"id":"2cefcd6.978ec32","type":"json","z":"b8e3d69e.392308","name":"","property":"payload","action":"","pretty":false,"x":1170,"y":200,"wires":[["f3d60839.ebf498"]]},{"id":"f3d60839.ebf498","type":"json","z":"b8e3d69e.392308","name":"","property":"payload","action":"","pretty":false,"x":1290,"y":200,"wires":[[]]},{"id":"f3d969b3.f4cd18","type":"debug","z":"b8e3d69e.392308","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1120,"y":60,"wires":[]},{"id":"e2596bf5.14df98","type":"inject","z":"b8e3d69e.392308","name":"Alle Daten","topic":"","payload":"[{\"time\":\"2020-05-19T23:40:29.000Z\",\"Temperatur\":23.4},{\"time\":\"2020-05-19T23:45:55.000Z\",\"Temperatur\":23.4},{\"time\":\"2020-05-19T23:46:39.000Z\",\"Temperatur\":23.5},{\"time\":\"2020-05-19T23:46:45.000Z\",\"Temperatur\":23.4},{\"time\":\"2020-05-19T23:52:25.000Z\",\"Temperatur\":23.4},{\"time\":\"2020-05-19T23:54:49.000Z\",\"Temperatur\":23.4},{\"time\":\"2020-05-19T23:54:56.000Z\",\"Temperatur\":23.3},{\"time\":\"2020-05-19T23:55:02.000Z\",\"Temperatur\":23.3},{\"time\":\"2020-05-19T23:55:09.000Z\",\"Temperatur\":23.2},{\"time\":\"2020-05-19T23:55:15.000Z\",\"Temperatur\":23.1},{\"time\":\"2020-05-19T23:55:25.000Z\",\"Temperatur\":22.9},{\"time\":\"2020-05-19T23:55:31.000Z\",\"Temperatur\":22.7},{\"time\":\"2020-05-19T23:55:37.000Z\",\"Temperatur\":22.5},{\"time\":\"2020-05-19T23:55:45.000Z\",\"Temperatur\":22.3},{\"time\":\"2020-05-19T23:55:52.000Z\",\"Temperatur\":22.1},{\"time\":\"2020-05-19T23:55:58.000Z\",\"Temperatur\":21.9},{\"time\":\"2020-05-19T23:56:05.000Z\",\"Temperatur\":21.7},{\"time\":\"2020-05-19T23:56:11.000Z\",\"Temperatur\":21.5},{\"time\":\"2020-05-19T23:56:17.000Z\",\"Temperatur\":21.3},{\"time\":\"2020-05-19T23:56:23.000Z\",\"Temperatur\":21.2},{\"time\":\"2020-05-19T23:56:33.000Z\",\"Temperatur\":20.8},{\"time\":\"2020-05-19T23:56:39.000Z\",\"Temperatur\":20.6},{\"time\":\"2020-05-19T23:56:47.000Z\",\"Temperatur\":20.4},{\"time\":\"2020-05-19T23:56:53.000Z\",\"Temperatur\":20.2},{\"time\":\"2020-05-19T23:56:59.000Z\",\"Temperatur\":20},{\"time\":\"2020-05-19T23:57:06.000Z\",\"Temperatur\":19.8},{\"time\":\"2020-05-19T23:57:12.000Z\",\"Temperatur\":19.6},{\"time\":\"2020-05-19T23:57:19.000Z\",\"Temperatur\":19.4},{\"time\":\"2020-05-19T23:57:25.000Z\",\"Temperatur\":19.3},{\"time\":\"2020-05-19T23:57:32.000Z\",\"Temperatur\":19},{\"time\":\"2020-05-19T23:57:39.000Z\",\"Temperatur\":18.9},{\"time\":\"2020-05-19T23:57:45.000Z\",\"Temperatur\":18.7},{\"time\":\"2020-05-19T23:57:51.000Z\",\"Temperatur\":18.6},{\"time\":\"2020-05-19T23:57:57.000Z\",\"Temperatur\":18.4},{\"time\":\"2020-05-19T23:58:03.000Z\",\"Temperatur\":18.3},{\"time\":\"2020-05-19T23:58:09.000Z\",\"Temperatur\":18.1},{\"time\":\"2020-05-19T23:58:15.000Z\",\"Temperatur\":17.9},{\"time\":\"2020-05-19T23:58:21.000Z\",\"Temperatur\":17.8},{\"time\":\"2020-05-19T23:58:27.000Z\",\"Temperatur\":17.6},{\"time\":\"2020-05-19T23:58:35.000Z\",\"Temperatur\":17.4},{\"time\":\"2020-05-19T23:58:44.000Z\",\"Temperatur\":17.2},{\"time\":\"2020-05-19T23:58:51.000Z\",\"Temperatur\":17},{\"time\":\"2020-05-19T23:58:57.000Z\",\"Temperatur\":16.9},{\"time\":\"2020-05-19T23:59:04.000Z\",\"Temperatur\":16.7},{\"time\":\"2020-05-19T23:59:10.000Z\",\"Temperatur\":16.6},{\"time\":\"2020-05-19T23:59:16.000Z\",\"Temperatur\":16.4},{\"time\":\"2020-05-19T23:59:22.000Z\",\"Temperatur\":16.2},{\"time\":\"2020-05-19T23:59:28.000Z\",\"Temperatur\":16.1},{\"time\":\"2020-05-19T23:59:37.000Z\",\"Temperatur\":15.9},{\"time\":\"2020-05-19T23:59:44.000Z\",\"Temperatur\":15.8},{\"time\":\"2020-05-19T23:59:50.000Z\",\"Temperatur\":15.6},{\"time\":\"2020-05-19T23:59:56.000Z\",\"Temperatur\":15.5},{\"time\":\"2020-05-20T00:00:05.000Z\",\"Temperatur\":15.3},{\"time\":\"2020-05-20T00:00:10.000Z\",\"Temperatur\":15.3},{\"time\":\"2020-05-20T00:00:16.000Z\",\"Temperatur\":15.1},{\"time\":\"2020-05-20T00:00:24.000Z\",\"Temperatur\":15},{\"time\":\"2020-05-20T00:00:30.000Z\",\"Temperatur\":14.9},{\"time\":\"2020-05-20T00:00:36.000Z\",\"Temperatur\":14.7},{\"time\":\"2020-05-20T00:00:42.000Z\",\"Temperatur\":14.6},{\"time\":\"2020-05-20T00:00:49.000Z\",\"Temperatur\":14.4},{\"time\":\"2020-05-20T00:01:01.000Z\",\"Temperatur\":14.3},{\"time\":\"2020-05-20T00:01:07.000Z\",\"Temperatur\":14.1},{\"time\":\"2020-05-20T00:01:13.000Z\",\"Temperatur\":14},{\"time\":\"2020-05-20T00:01:22.000Z\",\"Temperatur\":13.9},{\"time\":\"2020-05-20T00:01:28.000Z\",\"Temperatur\":13.8},{\"time\":\"2020-05-20T00:01:36.000Z\",\"Temperatur\":13.7},{\"time\":\"2020-05-20T00:01:41.000Z\",\"Temperatur\":13.6},{\"time\":\"2020-05-20T00:01:48.000Z\",\"Temperatur\":13.5},{\"time\":\"2020-05-20T00:01:56.000Z\",\"Temperatur\":13.3},{\"time\":\"2020-05-20T00:02:02.000Z\",\"Temperatur\":13.2},{\"time\":\"2020-05-20T00:02:09.000Z\",\"Temperatur\":13.1},{\"time\":\"2020-05-20T00:02:15.000Z\",\"Temperatur\":13},{\"time\":\"2020-05-20T00:02:22.000Z\",\"Temperatur\":12.9},{\"time\":\"2020-05-20T00:02:28.000Z\",\"Temperatur\":12.8},{\"time\":\"2020-05-20T00:02:34.000Z\",\"Temperatur\":12.7},{\"time\":\"2020-05-20T00:02:40.000Z\",\"Temperatur\":12.6},{\"time\":\"2020-05-20T00:02:46.000Z\",\"Temperatur\":12.5},{\"time\":\"2020-05-20T00:02:58.000Z\",\"Temperatur\":12.4},{\"time\":\"2020-05-20T00:03:04.000Z\",\"Temperatur\":12.3},{\"time\":\"2020-05-20T00:03:11.000Z\",\"Temperatur\":12.2},{\"time\":\"2020-05-20T00:03:17.000Z\",\"Temperatur\":12.1},{\"time\":\"2020-05-20T00:03:23.000Z\",\"Temperatur\":12},{\"time\":\"2020-05-20T00:03:29.000Z\",\"Temperatur\":11.9},{\"time\":\"2020-05-20T00:03:42.000Z\",\"Temperatur\":11.8},{\"time\":\"2020-05-20T00:03:49.000Z\",\"Temperatur\":11.7},{\"time\":\"2020-05-20T00:03:58.000Z\",\"Temperatur\":11.6},{\"time\":\"2020-05-20T00:04:06.000Z\",\"Temperatur\":11.5},{\"time\":\"2020-05-20T00:04:12.000Z\",\"Temperatur\":11.5},{\"time\":\"2020-05-20T00:04:18.000Z\",\"Temperatur\":11.4},{\"time\":\"2020-05-20T00:04:25.000Z\",\"Temperatur\":11.3},{\"time\":\"2020-05-20T00:04:31.000Z\",\"Temperatur\":11.2},{\"time\":\"2020-05-20T00:04:37.000Z\",\"Temperatur\":11.1},{\"time\":\"2020-05-20T00:04:50.000Z\",\"Temperatur\":11},{\"time\":\"2020-05-20T00:04:56.000Z\",\"Temperatur\":10.9},{\"time\":\"2020-05-20T00:05:05.000Z\",\"Temperatur\":10.8},{\"time\":\"2020-05-20T00:05:18.000Z\",\"Temperatur\":10.7},{\"time\":\"2020-05-20T00:05:29.000Z\",\"Temperatur\":10.6},{\"time\":\"2020-05-20T00:05:36.000Z\",\"Temperatur\":10.5},{\"time\":\"2020-05-20T00:05:42.000Z\",\"Temperatur\":10.4},{\"time\":\"2020-05-20T00:06:01.000Z\",\"Temperatur\":10.3},{\"time\":\"2020-05-20T00:06:07.000Z\",\"Temperatur\":10.2},{\"time\":\"2020-05-20T00:06:13.000Z\",\"Temperatur\":10.1},{\"time\":\"2020-05-20T00:06:26.000Z\",\"Temperatur\":10},{\"time\":\"2020-05-20T00:06:44.000Z\",\"Temperatur\":9.9},{\"time\":\"2020-05-20T00:06:50.000Z\",\"Temperatur\":9.8},{\"time\":\"2020-05-20T00:06:56.000Z\",\"Temperatur\":9.8},{\"time\":\"2020-05-20T00:07:04.000Z\",\"Temperatur\":9.7},{\"time\":\"2020-05-20T00:07:22.000Z\",\"Temperatur\":9.7},{\"time\":\"2020-05-20T00:07:28.000Z\",\"Temperatur\":9.7},{\"time\":\"2020-05-20T00:07:34.000Z\",\"Temperatur\":9.9},{\"time\":\"2020-05-20T00:07:43.000Z\",\"Temperatur\":10.2},{\"time\":\"2020-05-20T00:07:49.000Z\",\"Temperatur\":10.4},{\"time\":\"2020-05-20T00:07:56.000Z\",\"Temperatur\":10.7},{\"time\":\"2020-05-20T00:08:02.000Z\",\"Temperatur\":10.9},{\"time\":\"2020-05-20T00:08:08.000Z\",\"Temperatur\":11.1},{\"time\":\"2020-05-20T00:08:14.000Z\",\"Temperatur\":11.3},{\"time\":\"2020-05-20T00:08:21.000Z\",\"Temperatur\":11.6},{\"time\":\"2020-05-20T00:08:27.000Z\",\"Temperatur\":11.8},{\"time\":\"2020-05-20T00:08:33.000Z\",\"Temperatur\":12},{\"time\":\"2020-05-20T00:08:39.000Z\",\"Temperatur\":12.2},{\"time\":\"2020-05-20T00:08:45.000Z\",\"Temperatur\":12.4},{\"time\":\"2020-05-20T00:08:53.000Z\",\"Temperatur\":12.7},{\"time\":\"2020-05-20T00:08:59.000Z\",\"Temperatur\":12.9},{\"time\":\"2020-05-20T00:09:07.000Z\",\"Temperatur\":13.1},{\"time\":\"2020-05-20T00:09:14.000Z\",\"Temperatur\":13.3},{\"time\":\"2020-05-20T00:09:22.000Z\",\"Temperatur\":13.4}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":880,"y":60,"wires":[["f3d969b3.f4cd18","e4953337.af321"]]},{"id":"e4953337.af321","type":"change","z":"b8e3d69e.392308","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $min := $.payload^(Temperatur)[0];\t $max := $.payload^(Temperatur)[-1];\t\t ($min.time) > ($max.time) and /* falling Temperatur */\t ($max.Temperatur - $min.Temperatur) > 0.5 and /* 0.5 degrees falling Temperatur in the interval */\t ($min.Temperatur < 20) /* warning only when temperature below 20 °C */\t \t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1350,"y":120,"wires":[[]]},{"id":"52cc6f72.aa656","type":"influxdb","z":"","hostname":"192.168.178.2","port":"8086","protocol":"http","database":"openhab_db","name":"","usetls":false,"tls":"b0b9273e.9f1208"},{"id":"b0b9273e.9f1208","type":"tls-config","z":"","name":"","cert":"","key":"","ca":"","certname":"m2mqtt_srv.crt","keyname":"m2mqtt_srv.key","caname":"m2mqtt_ca.crt","verifyservercert":false}]
hi, I edited your code to properly format it.
for code and json, please use this format...
```
// paste flow, code etc between 3 backticks (graves accent)
var why = "because it looks much better than reams of JSON"
let numbers_be_formatted = 12345; //and comments look better too
let arrays = [ "look good too", FuncCall(why), 12345 /*inline comment*/];
```
(PS Ignore the demo code in my example - it doesnt do anything)
If you are trying to get the max and min values, why not just just use
select max(Temperatur), min(Temperatur) from AquaraBluetoothSensoren where Sensorname='Zimmer1' and time > now()-2h
I can't use that because it returns only one timestamp and so I don't know if the temperature is rising or falling.
so run two queries, one to select max and one to select min and compare those to results.
Does the database support unions? You could do a union query.
Alternatively, does the node support multiple queries? You could as @zenofmud says, run 2 queries. The database will likely be a lot faster than JS.
Ps, what database is this getting data from?
I can "join" the two queries via semicolon. But as I stated above, I don't want two queries because if I change for example instead of the last two hours to the last five hours, then I have to change on two places and that is more error prone.
I've found now a way to do it without the two JSON nodes and that works with the inject node.
(
$min := $.payload^(Temperatur)[0];
$max := $.payload^(Temperatur)[-1];
$string($min.time) > $string($max.time) and /* falling Temperatur */
($max.Temperatur - $min.Temperatur) > 0.5 and /* 0.5 degrees falling Temperatur in the interval */
($min.Temperatur < 20) /* warning only when temperature below 20 °C */
)
The trick is to explicitely convert the times to string. $string($min.time) > $string($max.time)
Debug node seems to do this implicitly because there output of influxdb node and inject node look the same. Since time format is yyyy-mm-ddThh:mm:ss.MMMZ" where M = milliseconds comparing strings with time works.
Many thanks to all helping to do this with jsonata.
The data is queried from influxdb.
Concerning performance. Yeah, youre right, doing it in database is much more perfomant.
For a resultset with about 1500 objects above JSONata expression takes almost 8 seconds on a Raspberry PI4.
It seems that it is the array sorting process which is so slow. With this code execution time is approximately cut in half (measured with a stopwatch).
(
$sortiert := $.payload^(Temperatur)[[0,-1]];
$min := $sortiert[0];
$max := $sortiert[1];
$string($min.time) > $string($max.time) and /* falling Temperatur */
($max.Temperatur - $min.Temperatur) > 0.5 and /* 0.5 degrees falling Temperatur in the interval */
($min.Temperatur < 20) /* warning only when temperature below 20 °C */
)
Since I'll consider only the last 30 minutes performance difference doing it with JSONata or influxdb is not noticeable. However this is an example that for performance you should filter at first place, here at database level.
Why not build the query dynamical? You could even pass the time in from the dashboard
1 Like
I cannot imagine this will work properly. It compares a string > string which will aways be true (?)
Note that influxdb has functions like SPREAD()
Returns the difference between the minimum and maximum field values
example query
select SPREAD(Temperature) FROM "netatmo" WHERE ("weather" = 'internal') and (time > now()-1h) and Temperature>20 group by time(1h)
So you could expand this query where spread>0.5
The query can be created dynamically (eg select the timeframe from dashboard)
I cannot imagine this will work properly. It compares a string > string which will aways be true (?)
It works. Try with this data:
[
{
"time": "2020-05-21T06:09:11.000Z",
"Temperatur": 21.7
},
{
"time": "2020-05-21T06:09:25.000Z",
"Temperatur": 21.6
}
]
And that JSONata expression. It prints "false true", so it works
(
$firstcomp := $string(time[[0]])>$string(time[[1]]);
$secondcomp := $string(time[[0]])<$string(time[[1]]);
$string($firstcomp) & ' ' & $string($secondcomp)
)
You can also try directly here https://try.jsonata.org/saORdBSOE
Thanks for the function spread. However spread is always positive so I don't know if temperature is rising or falling and alarm should only trigger on falling temperature.
I would convert the date string to an actual numeric date that can be compared as such:
(
$firstcomp := $toMillis(time[[0]])>$toMillis(time[[1]]);
$secondcomp := $toMillis(time[[0]])<$toMillis(time[[1]]);
$firstcomp & ' ' & $secondcomp
)
This doesn't work in this case, since influxdb returns for the time some object where JSONata has some problems with. If you try, you get "Ungültiger JSONata Ausdruck: Argument 1 of function "toMillis" does not match function signature"
If you try first converting it to string and then to milliseconds like
(
$sortiert := $.payload^(Temperatur)[[0,-1]];
$min := $sortiert[0];
$max := $sortiert[1];
$toMillis($string($min.time)) > $toMillis($string($max.time)) and /* falling Temperatur */
($max.Temperatur - $min.Temperatur) > 0.5 and /* 0.5 degrees falling Temperatur in the interval */
($min.Temperatur < 20) /* warning only when temperature below 20 °C */
)
then you end up with:
"Ungültiger JSONata Ausdruck: The argument of the toMillis function must be an ISO 8601 formatted timestamp. Given ""2020-05-21T22:01:08.000Z"""
So I'll use the string comparison.
