Sorting MySQL-Payload Data for Apex-Charts using JSONata

General
1

Hey everyone! I'm trying to use JSONata to get a MySQL-Payload in the right format to display that data with ApexCharts. I'm quite new to Node-Red and not sure if my task is probably too complex for JSONata.

Here's an extract as an example of what I get from the MySQL-Database: https://try.jsonata.org/FrXQ2bR5j

And that's what the JSONata output should look like:

[
	{
		"name": "WR1",
		"data": [
			[1459060500000,317],
			[1459060800000,194],
			[1459061100000,364]
		]
	},
	{
		"name": "WR2",
		"data": [
			[1459060500000,721],
			[1459060800000,472],
			[1459061100000,888]
		]
	},
	{
		"name": "Total",
		"data": [
			[1459060500000,1038],
			[1459060800000,666],
			[1459061100000,1252]
		]
	}
]

For each "WR"-number (it's actually solar inverters), I would like to get a data-object containing arrays of all timestamps followed by the "PAC"-value (inverter ac power). In addition there shall be added another data-object "Total" which also contains all individual timestamps followed by the sum of the "PAC"-value of all "WR" (solar inverters).

I did already fiddle around with the JSONata syntax for about two days but I just don't get there.

2

Try

$append(
   $each(
       $$.payload{    $string($.WR): [$.[$.timestamp, $.PAC]] },
       function($v,$k) {  {"name":"WR" & $k, "data": $v} }
   ),
   $$.payload{
       "name":"total",
       "data":$each(
           {$string($.timestamp): $sum([$.PAC])},
           function($v, $k){        [$number($k), $v]    }
       ) 
   } 
)
1 Like
3

Wow, that works exactly as intended and it looks quite complicated. I wasn't even close with my attempts. :smiley:

Thanks a lot!!

I now try to understand this pattern as it seems that JSONata is genious if you know how to use it properly.

4

It uses group by to group the wr data, then uses $each() to convert the grouped wr in to the array of objects. It then uses group by to group timestamp data, then $each() to sum the PAC and output a object, both are then appended to each other using $append().

5

I did test single elements of the solution and now it makes a lot more sense and gives a better understanding of what each part actually does. I just found that you did add a "$." in front of each variable later on which actually doesn't change anything in the output. Is there a reason why it is "better" to have those included? I know that the dollar signs are required if you have an array at the top level to be able to access the data.

6

Makes no difference most of the time, just reminds me what scope the variable is in.
For example if you are in a map/loop you can not reference msg.payload with payload you need to add $$. to front.
While learning it took me aqes to realise that var scope mattered, and I often could not figure why things did not work, then i started adding $ or $$ scope and i found Thing started working, which previously did not. This led me to a better understanding of JSONata variable scope.

1 Like
7

Good to hear it's not just me who finds this a little confusing. I also started to play around with them and see what I get. But it's more like try and error :slight_smile:

Thanks again for the great help!

8

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