# Sorting MySQL-Payload Data for Apex-Charts using JSONata

**URL:** <https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258>\
**Category:** General\
**Created:** [26 September 2022 19:27 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258 "2022-09-26T19:27:55Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Martin12](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@Martin12](https://discourse.nodered.org/u/Martin12)\
**Post date:** [26 September 2022 19:27 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/1 "2022-09-26T19:27:56Z")

</div>

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](https://try.jsonata.org/FrXQ2bR5j)

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

```auto
[
	{
		"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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [26 September 2022 22:18 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/2 "2022-09-26T22:18:19Z")

</div>

Try

```auto
$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] }
       ) 
   } 
)

```

---

<div class="post-metadata">

**Author:** ![Martin12](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@Martin12](https://discourse.nodered.org/u/Martin12)\
**Post date:** [26 September 2022 22:29 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/3 "2022-09-26T22:29:17Z")

</div>

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

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [26 September 2022 22:36 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/4 "2022-09-26T22:36:44Z")

</div>

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().

---

<div class="post-metadata">

**Author:** ![Martin12](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@Martin12](https://discourse.nodered.org/u/Martin12)\
**Post date:** [27 September 2022 18:56 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/5 "2022-09-27T18:56:18Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [27 September 2022 19:09 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/6 "2022-09-27T19:09:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Martin12](https://avatars.discourse-cdn.com/v4/letter/m/e47c2d/32.png) [@Martin12](https://discourse.nodered.org/u/Martin12)\
**Post date:** [27 September 2022 19:20 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/7 "2022-09-27T19:20:30Z")

</div>

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 🙂

Thanks again for the great help!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [11 October 2022 19:20 UTC](https://discourse.nodered.org/t/sorting-mysql-payload-data-for-apex-charts-using-jsonata/68258/8 "2022-10-11T19:20:43Z")

</div>

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