# Transform data from array
**URL:** https://discourse.nodered.org/t/transform-data-from-array/72733
**Category:** General
**Created:** [26 December 2022 14:30 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733 "2022-12-26T14:30:11Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [26 December 2022 14:30 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/1 "2022-12-26T14:30:11Z")
How do i transform the below data from this format
`[[a,b,c,d],[1,2,3,4],[10,20,30,40]]`
to
`[[a,1,10],[b,2,20],[c,3,30],[d,4,40]]`
i have got some examples on forum for converting static data from one form to another
but, I have got this output from a mysql query and the results are dynamic coming in `msg.payload`
`[[1672064640,1672064580,1672064520,1672064460],[969,969,969,972],[6,6,7,6]]`
but I need the same data in this form
`[[1672064640,969,6],[1672064580,969,6],[1672064520,969,7],[1672064460,972,6]]`
I thought i had the solution when i started this topic, but now i am unable to get the same result.
> [@Transpose the Object/Array](https://discourse.nodered.org/t/transpose-the-object-array/72170/5):
>
> Try setting msg.labels to JSONata J: [$$.payload.R] and then msg.payload to JSONata J: [[$$.payload.D]] or [$$.payload.D.[$]] in a change node. You have to set msg.labels first, as if you set payload the data will be overwrtten.
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [26 December 2022 15:23 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/2 "2022-12-26T15:23:49Z")
@smanjunath211 I've moved your question to it's own topic (as the original topic was about barcharts & flexdash, you are more likely to get a reply if the title is more direct to the question, as more members will read it)
---
### Author:  [@BartButenaers](https://discourse.nodered.org/u/BartButenaers)
#### Post date: [26 December 2022 16:05 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/3 "2022-12-26T16:05:38Z")
I have tried [chat.openai.com](https://chat.openai.com/chat) for this:
My first question was:
So then I tried it without nested loops:
I also asked him to write a Jsonata expression for this, but I think the Change node will explode if you enter such a long expression 😉
---
### Author:  [@BartButenaers](https://discourse.nodered.org/u/BartButenaers)
#### Post date: [26 December 2022 16:10 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/4 "2022-12-26T16:10:57Z")
And you can ask him also more specific to do it in one or another way:
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 16:14 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/5 "2022-12-26T16:14:59Z")
A JSONata expression in a change node.
```auto
$$.payload[0]#$index.$.[
[
$,
$$.payload[1][$index],
$$.payload[2][$index]
]
]
```
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [26 December 2022 16:30 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/6 "2022-12-26T16:30:55Z")
I have no clue how to use this in my situation. please bear with me
please help in understanding what and where should i replace. i am pasting the values i get from my mysql query below
```auto
[{"Time":1672071900,"m":0,"temp":93.6},{"Time":1672071840,"m":0,"temp":92.7},{"Time":1672071780,"m":0,"temp":93.3},{"Time":1672071720,"m":0,"temp":93.9}]
```
and i need this to be in this format for FlexDash input
`[[1672064640,86.9,6],[1672064580,93.9,6],[1672064520,96.9,7],[1672064460,97.2,6]]`
by using your previous suggestion in another thread, i am able to get upto here.
but it is not in required format.
```auto
[[1672072140,1672072080,1672072020,1672071960],[7,6,6,6],[95.7,95.4,95.1,96]]
```
EDIT
the following change node gives me one set of data in required format. not 4 sets,
what is the change required ?
```auto
[{"id":"1003e61f5e5b0cee","type":"change","z":"bbf5094bc59a03b8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload[0].Time.$.[\t [\t $,\t $$.payload[0].m,\t $$.payload[0].temp \t ]\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":520,"y":1840,"wires":[["acd36e1efad02cf0","9c0c7165cb86de68"]]}]
```
change node content
```auto
$$.payload[0].Time.$.[
[
$,
$$.payload[0].m,
$$.payload[0].temp
]
]
```
results this
`[[1672072980,6,98.1]]`
only one set
---
### Author:  [@E1cid](https://discourse.nodered.org/u/E1cid)
#### Post date: [26 December 2022 16:45 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/7 "2022-12-26T16:45:15Z")
> [@smanjunath211](#):
>
> ```auto
> [{"Time":1672071900,"m":0,"temp":93.6},{"Time":1672071840,"m":0,"temp":92.7},{"Time":1672071780,"m":0,"temp":93.3},{"Time"
>
> ```
That is not the same format as
`[[a,b,c,d],[1,2,3,4],[10,20,30,40]]`
one is an array of objects and the other an array of arrays.
Please be clear with your examples.
To guess on output this may work `$$.payload.[$.*]`
If you require more help, i will require a sample flow with the input data in a inject node and a clear example of the output.
[edit] from examples given
```auto
[{"id":"1b26608e566196d3","type":"inject","z":"da8a6ef0b3c9a5c8","name":"My SQL Query Output (rawdata)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Time\":1672073400,\"m\":6,\"temp\":99},{\"Time\":1672073340,\"m\":7,\"temp\":98.1},{\"Time\":1672073280,\"m\":6,\"temp\":98.7},{\"Time\":1672073220,\"m\":6,\"temp\":98.7}]","payloadType":"json","x":310,"y":600,"wires":[["95f032d857efa5ed"]]},{"id":"95f032d857efa5ed","type":"change","z":"da8a6ef0b3c9a5c8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.[$.*]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":600,"wires":[["22e0d6e0318685cd"]]},{"id":"22e0d6e0318685cd","type":"debug","z":"da8a6ef0b3c9a5c8","name":"debug 86","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":700,"y":600,"wires":[]},{"id":"06fa988581035686","type":"inject","z":"da8a6ef0b3c9a5c8","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[[1672073400,6,99],[1672073340,7,98.1],[1672073280,6,98.7],[1672073220,6,98.7]]","payloadType":"json","x":230,"y":640,"wires":[["d2d1a9c2eae2f28a"]]},{"id":"d2d1a9c2eae2f28a","type":"debug","z":"da8a6ef0b3c9a5c8","name":"debug 87","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":600,"y":640,"wires":[]}]
```
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [26 December 2022 17:00 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/8 "2022-12-26T17:00:13Z")
Sorry for this confusion, i have a totally messed up flow doing so many things
please consider this.
first inject is what i get from mysql query
second inject is what i expect the change node to do.
```auto
[{"id":"1b26608e566196d3","type":"inject","z":"bbf5094bc59a03b8","name":"My SQL Query Output (rawdata)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Time\":1672073400,\"m\":6,\"temp\":99},{\"Time\":1672073340,\"m\":7,\"temp\":98.1},{\"Time\":1672073280,\"m\":6,\"temp\":98.7},{\"Time\":1672073220,\"m\":6,\"temp\":98.7}]","payloadType":"json","x":230,"y":1940,"wires":[["22e0d6e0318685cd"]]},{"id":"06fa988581035686","type":"inject","z":"bbf5094bc59a03b8","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[[1672073400,6,99],[1672073340,7,98.1],[1672073280,6,98.7],[1672073220,6,98.7]]","payloadType":"json","x":150,"y":1980,"wires":[["d2d1a9c2eae2f28a"]]},{"id":"22e0d6e0318685cd","type":"debug","z":"bbf5094bc59a03b8","name":"debug 86","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":520,"y":1940,"wires":[]},{"id":"d2d1a9c2eae2f28a","type":"debug","z":"bbf5094bc59a03b8","name":"debug 87","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":520,"y":1980,"wires":[]}]
```
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [26 December 2022 17:01 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/9 "2022-12-26T17:01:30Z")
Have you tried what @E1cid suggested in his last post?
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [26 December 2022 17:14 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/10 "2022-12-26T17:14:28Z")
He has just edited and added the solution at the end of post. and it **WORKS!**
and amazingly the content of the change node is just this
`$$.payload.[$.*]`
Flexdash is taking the data happily.
---
### Author:  [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)
#### Post date: [26 December 2022 18:19 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/11 "2022-12-26T18:19:45Z")
Just check the final solution, its just one word. Amazing @E1cid and his Jsonata skills.
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [9 January 2023 18:20 UTC](https://discourse.nodered.org/t/transform-data-from-array/72733/12 "2023-01-09T18:20:15Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.