JSONata: Get max and minimum temperature with timestamp

[JSONata]

Ok, we have this data from influx. Temperature over the last 30 minutes
[{"time":"2020-05-18T19:09:53.000Z","Temperatur":24.1},{"time":"2020-05-18T19:09:58.000Z","Temperatur":24},{"time":"2020-05-18T19:12:48.000Z","Temperatur":23.9},{"time":"2020-05-18T19:13:27.000Z","Temperatur":24},{"time":"2020-05-18T19:13:33.000Z","Temperatur":23.9},{"time":"2020-05-18T19:13:48.000Z","Temperatur":24},{"time":"2020-05-18T19:13:55.000Z","Temperatur":23.9},{"time":"2020-05-18T19:16:33.000Z","Temperatur":23.8},{"time":"2020-05-18T19:17:46.000Z","Temperatur":23.9},{"time":"2020-05-18T19:18:26.000Z","Temperatur":23.8},{"time":"2020-05-18T19:19:12.000Z","Temperatur":23.8},{"time":"2020-05-18T19:21:07.000Z","Temperatur":23.9},{"time":"2020-05-18T19:21:13.000Z","Temperatur":23.8},{"time":"2020-05-18T19:24:32.000Z","Temperatur":23.7},{"time":"2020-05-18T19:24:51.000Z","Temperatur":23.8},{"time":"2020-05-18T19:25:50.000Z","Temperatur":23.7},{"time":"2020-05-18T19:29:19.000Z","Temperatur":23.7},{"time":"2020-05-18T19:29:25.000Z","Temperatur":23.7},{"time":"2020-05-18T19:29:35.000Z","Temperatur":23.8},{"time":"2020-05-18T19:29:42.000Z","Temperatur":23.7},{"time":"2020-05-18T19:29:48.000Z","Temperatur":23.7},{"time":"2020-05-18T19:32:47.000Z","Temperatur":23.6},{"time":"2020-05-18T19:35:00.000Z","Temperatur":23.7},{"time":"2020-05-18T19:35:06.000Z","Temperatur":23.6},{"time":"2020-05-18T19:36:07.000Z","Temperatur":23.5},{"time":"2020-05-18T19:36:19.000Z","Temperatur":23.6},{"time":"2020-05-18T19:39:22.000Z","Temperatur":23.5}]

Now I want to get min and max temperature with timestamp. Getting the maximum Temperatur is easy, just enter $max(Temperatur) and it returns 24.1 but how to get the corresponding timestamp?

Thanks in advance.

[bakman2]

This should work:

*[$max(Temperatur)].time
or
payload[$max(Temperatur)].time

If you want min/max:

(
    $min := *[$min(Temperatur)].time;
    $max := *[$max(Temperatur)].time;

    {
        "min":$min,
        "max":$max
    }
)

[JSONata]

*[$max(Temperatur)].time

I've tried this myself, but it returns "2020-05-18T19:35:06.000Z", same time for min. Don't know what this returns. At "2020-05-18T19:35:06.000Z" the temperature was 23.6

payload[$max(Temperatur)].time

This returns an error

Third statement gives exactly the same time for min and max.

[TotallyInformation]

Works for me.

[{"id":"850072b6.a969a","type":"inject","z":"ef122f0d.d488b","name":"","topic":"","payload":"[{\"time\":\"2020-05-18T19:09:53.000Z\",\"Temperatur\":24.1},{\"time\":\"2020-05-18T19:09:58.000Z\",\"Temperatur\":24},{\"time\":\"2020-05-18T19:12:48.000Z\",\"Temperatur\":23.9},{\"time\":\"2020-05-18T19:13:27.000Z\",\"Temperatur\":24},{\"time\":\"2020-05-18T19:13:33.000Z\",\"Temperatur\":23.9},{\"time\":\"2020-05-18T19:13:48.000Z\",\"Temperatur\":24},{\"time\":\"2020-05-18T19:13:55.000Z\",\"Temperatur\":23.9},{\"time\":\"2020-05-18T19:16:33.000Z\",\"Temperatur\":23.8},{\"time\":\"2020-05-18T19:17:46.000Z\",\"Temperatur\":23.9},{\"time\":\"2020-05-18T19:18:26.000Z\",\"Temperatur\":23.8},{\"time\":\"2020-05-18T19:19:12.000Z\",\"Temperatur\":23.8},{\"time\":\"2020-05-18T19:21:07.000Z\",\"Temperatur\":23.9},{\"time\":\"2020-05-18T19:21:13.000Z\",\"Temperatur\":23.8},{\"time\":\"2020-05-18T19:24:32.000Z\",\"Temperatur\":23.7},{\"time\":\"2020-05-18T19:24:51.000Z\",\"Temperatur\":23.8},{\"time\":\"2020-05-18T19:25:50.000Z\",\"Temperatur\":23.7},{\"time\":\"2020-05-18T19:29:19.000Z\",\"Temperatur\":23.7},{\"time\":\"2020-05-18T19:29:25.000Z\",\"Temperatur\":23.7},{\"time\":\"2020-05-18T19:29:35.000Z\",\"Temperatur\":23.8},{\"time\":\"2020-05-18T19:29:42.000Z\",\"Temperatur\":23.7},{\"time\":\"2020-05-18T19:29:48.000Z\",\"Temperatur\":23.7},{\"time\":\"2020-05-18T19:32:47.000Z\",\"Temperatur\":23.6},{\"time\":\"2020-05-18T19:35:00.000Z\",\"Temperatur\":23.7},{\"time\":\"2020-05-18T19:35:06.000Z\",\"Temperatur\":23.6},{\"time\":\"2020-05-18T19:36:07.000Z\",\"Temperatur\":23.5},{\"time\":\"2020-05-18T19:36:19.000Z\",\"Temperatur\":23.6},{\"time\":\"2020-05-18T19:39:22.000Z\",\"Temperatur\":23.5}]","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":3680,"wires":[["47b84a7f.d10254"]]},{"id":"47b84a7f.d10254","type":"change","z":"ef122f0d.d488b","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t    $min := *[$min(Temperatur)].time;\t    $max := *[$max(Temperatur)].time;\t\t    {\t        \"min\":$min,\t        \"max\":$max\t    }\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":3680,"wires":[["1571d9b0.7d8e76"]]},{"id":"1571d9b0.7d8e76","type":"debug","z":"ef122f0d.d488b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":580,"y":3680,"wires":[]}]

[JSONata]

Did you check the results?
For me it gives

So min and max would have occured at the same time.

[bakman2]

Yes you are correct, i didnt check the results

Have to look further, then again, your nick is JSONata

[edje11]

Why not change the influx query.
I'm on my tablet so can't test it for you, but something like
Select max(myvalue) from ........ This will return one value from the result with the max. From the result you can extract the time.
Should do the trick for the max value and the same with min.

[JSONata]

Thanks for the hint. I had this query already and then I found JSONata and from a point of view it procuced code which can be better maintained.
Because when I have 2 influxdb queries I have to update them both on change, leading to make more easily an error.

[rewegit]

Maybe this will help?

$filter(*, function($v, $i,$a) {$v.Temperatur = $max(Temperatur)})

Result:
{
"time": "2020-05-18T19:09:53.000Z",
"Temperatur": 24.1
}

$filter(*, function($v, $i,$a) {$v.Temperatur = $max(Temperatur)}).Temperatur

Result:
24.1

$filter(*, function($v, $i,$a) {$v.Temperatur = $max(Temperatur)}).time

Result:
"2020-05-18T19:09:53.000Z"

[bakman2]

I got the following hint from the jsonata guru's.

$.payload^(Temperatur)[[0, -1]]

It orders them by temperature and gets the first and last element, nice.

[rewegit]

@bakman2

$.payload^(Temperatur)[[0, -1]]

that's great,

@JSONata

but how to get the corresponding timestamp?

[bakman2]

Did you actually try it ?

[rewegit]

Thanks for the tip

[JSONata]

@bakman2
Thanks for your great support. Sorting the array should be much more perfomant than searching the highest and lowest value.

With that expression I want to make a window left open alarm. The exact value I'll start to tune, but heres the JSONata Code. It returns true when temperature fell more then 0.5 degrees within the specified values and only when temperature is below 20 °C

(
    $min := $.payload^(Temperatur)[[0, -1]][0];
    $max := $.payload^(Temperatur)[[0, -1]][1];

    $min.time > $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 */
)

When testing it, everything is fine

However it prints an error:

when directly connected to the influx output.
Here one msg comes from inject node, the other directly from influx. In the inject node I've just copied the data coming from the influx-node.

Ok, the message from the inject node always evaluates to false, even it it should raise an alarm. Here a simple testflow with a massive temperature drow which should print "true" like in the evaluation Tab.

Here the code for importing and testing

[{"id":"4338e13c.a17508","type":"change","z":"ddd38a48.2e949","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":590,"y":300,"wires":[["72e06786.4b8768"]]},{"id":"9ca1d9a5.d0a008","type":"debug","z":"ddd38a48.2e949","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":770,"y":460,"wires":[]},{"id":"6502af37.9a544","type":"inject","z":"ddd38a48.2e949","name":"","topic":"","payload":"{\"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}],\"_msgid\":\"1e2a6937.b94d37\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":480,"y":560,"wires":[["4338e13c.a17508","9ca1d9a5.d0a008"]]},{"id":"72e06786.4b8768","type":"debug","z":"ddd38a48.2e949","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":800,"y":380,"wires":[]}]

Here you can see that the expression is basically working https://try.jsonata.org/CScU8OG9u

[bakman2]

In the jsonata exerciser you use:

$min := *.payload^(Temperatur)[0];
$max := *.payload^(Temperatur)[-1];

In the flow:

$min := $.payload^(Temperatur)[0];
$max := $.payload^(Temperatur)[-1];

[JSONata]

Thanks. This mistake was because I was trying a lot.

I'm very confused now. Please look at this flow:

Output from Inject node and influxdb node both go to the same debug node. For testing purposes both just return von Timestamp temperature pair.
Now when using the single inject node, the change node with the JSONata expression works as designed (also when using a lot data which would cause the JSONata expression to return true).
But when I use the influxdb node to get the data out, this error in the right lower corner appears. However if you take a look both msg.payload objects look similar (except other values of course).

So why does this flow / JSONata expression work when used from the inject node, but not when used from the influxdb node?

[JSONata]

Update:
If I set two JSON Nodes inbetween, then it works completely as expected.
So first JSON Node coverts it to string, second converts it back to JSON.
So is this a bug in NODE-Red? Or only in the influx node?

[dceejay]

I suspect the influxdb node is returning something with not quite the standard types in - that the json node manages to stringify ok and then converts it back ok... one possible way to check would be to add a function node after influx, containing

node.warn(typeof msg.payload[0].Temperatur); 
node.warn(typeof msg.payload[0].time); 
return msg;

and see what it reports

[JSONata]

Directly after influx it reports

after the two JSON conversations

So the error of JSONata makes sense, because after directly from influx it tries to compare two objects. Still this is a very confusing situation.

[zenofmud]

What is the influx query?
What is the schema for the measurment? (use show field keys from yourmeasurementname)