Help needed with JSONata syntax for replacement of key field

First post, so hi to everybody :slight_smile:

I'm trying to display a mysql result to a chart and for this using JSONata in a change note to rename the key fields of the resulting array to X/Y. Unfortunately one field of my sql return set is named MIN(timeStamp) and I'm not able to find the right syntax for replacing it. If I only have timeStamp as key it works. Below screenshots should help to understand my situation:

Working (w/o MIN in key field of the array):

Not working (w MIN in key field of the array):

If I change line 5 to "x": MIN(timeStamp), I get an Attempted to invoke a non-function error.

Is there an easy way to archive my goal?

Thanks

Try putting a $ eg. $min().

You should also be able to do all this in the mysql request, including changing key names.

$min(..) is not helping / doing anything.
No error but no x values in the resulting array.

The sql stmt was:

SELECT MIN(timeStamp), gasMeterValueVerified FROM gasMeter where gasMeterValueVerified is not null AND DATE(timeStamp) > DATE(NOW() - INTERVAL 1 DAY) GROUP BY gasMeterValueVerified

Your hint with changing the key name in sql was spot on, thanks.
The sql statement is now:

SELECT MIN(timeStamp) as tStamp, gasMeterValueVerified FROM gasMeter where gasMeterValueVerified is not null AND DATE(timeStamp) > DATE(NOW() - INTERVAL 1 DAY) GROUP BY gasMeterValueVerified

With that line 5 is now:

"x": tStamp, 

My initial problem is now solved (in a different way as expected :slight_smile: ).
Still if anyone would know how to do the replacement in JSON I would be interested in the same.

Transform

[{"id":"5fa7772f.5a4538","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"x\":\"2021-01-07T21:24:44\",\"y\":5},{\"x\":\"2021-01-07T21:24:54\",\"y\":4},{\"x\":\"2021-01-07T21:24:59\",\"y\":2}]","payloadType":"json","x":180,"y":3920,"wires":[["62056daf.b0ef74"]]},{"id":"62056daf.b0ef74","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload ~> |[*]|{'newx':x,'newy':y},[\"x\",\"y\"]|","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":3920,"wires":[["3a43c3c5.81a3b4"]]},{"id":"3a43c3c5.81a3b4","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":620,"y":3920,"wires":[]}]

[edit] Or

payload.{'newx':x,'newyy':y}

but in you sql why not
SELECT MIN(timeStamp) as x, gasMeterValueVerified as y

Had not thought of naming both results right away in sql to x and y thou I still need to transfer into the series, label, data array for the chart. Appreciate the new view thou :slight_smile:

What I meant with solution in JSON was to get the transformation with the key name MIN(timeStamp) to x so that JSON is not interpreting it as a function but taking the key name as such for the replacement. As I have a solution this is has now low priority but would be interesting if there is a way.

When i first read your post i confused MIN() the sql, with $min() j: .
Try backticks `MIN()`

[{"id":"62056daf.b0ef74","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.{'newx':`MIN(timeStamp)`,'newyy':y}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":3920,"wires":[["3a43c3c5.81a3b4"]]},{"id":"5fa7772f.5a4538","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"MIN(timeStamp)\":\"2021-01-07T21:24:44\",\"y\":5},{\"MIN(timeStamp)\":\"2021-01-07T21:24:54\",\"y\":4},{\"MIN(timeStamp)\":\"2021-01-07T21:24:59\",\"y\":2}]","payloadType":"json","x":180,"y":3920,"wires":[["62056daf.b0ef74"]]},{"id":"3a43c3c5.81a3b4","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":620,"y":3920,"wires":[]}]

That did the trick, thanks
Will now rather work with your sql solution but good to know the other way :slight_smile:

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.