# Splitting array - from MySQL query

**URL:** <https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279>\
**Category:** General\
**Created:** [8 March 2021 20:37 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279 "2021-03-08T20:37:19Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [8 March 2021 20:37 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/1 "2021-03-08T20:37:19Z")

</div>

I run a query on MySQL db and I want split array into separate msg

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/5/a5fb857e43aa2ec55458e25734f55aff675a8d2c.png)

```auto
[{"id":"29b47fe9.8b692","type":"mysql","z":"33901399.5a63bc","mydb":"37f92788.1e2638","name":"soiot 2","x":830,"y":580,"wires":[["f2e8ef67.77d44","aa30a3ab.5cca5"]]},{"id":"a0ae458f.3cce58","type":"inject","z":"33901399.5a63bc","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM soiot.tracker_lat_lon;","x":680,"y":580,"wires":[["29b47fe9.8b692"]]},{"id":"aa30a3ab.5cca5","type":"split","z":"33901399.5a63bc","name":"","splt":"/n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":true,"addname":"","x":990,"y":580,"wires":[["b1779eb7.0e9f"]]},{"id":"f2e8ef67.77d44","type":"debug","z":"33901399.5a63bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":910,"y":640,"wires":[]},{"id":"b1779eb7.0e9f","type":"debug","z":"33901399.5a63bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1170,"y":580,"wires":[]},{"id":"37f92788.1e2638","type":"MySQLdatabase","host":"","port":"3306","db":"soiot","tz":""}]

```

As this only returns ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/d/0d191458ae8cd03860032ca9e21aaad3e08999a2.png)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [8 March 2021 21:12 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/2 "2021-03-08T21:12:56Z")

</div>

Maybe the split node to split the array ?

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [8 March 2021 21:15 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/3 "2021-03-08T21:15:17Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/0/100b5165cba65636ed5ed76330cefff3d7664f6a.png)  
This is what I used, but it returns for each [object Object]

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 March 2021 21:19 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/4 "2021-03-08T21:19:04Z")

</div>

Give the debug nodes names so we can see which is which then run it and show us the output from the two debug nodes.

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [8 March 2021 21:22 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/5 "2021-03-08T21:22:28Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/b/fb81e31457953a7c5042500a5316c97bf22ccf15.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/3/73df76935b8f252a1e0c42811d4724002ed01663.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/7/3753f0ae8afa7bf7e4a36eb6edf5e0b91eef2661.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 March 2021 21:26 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/6 "2021-03-08T21:26:02Z")

</div>

Untick 'handle as a stream of messages' in the split node. I don't know whether that will make a difference but ticked is not the default setting.

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [8 March 2021 21:31 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/7 "2021-03-08T21:31:25Z")

</div>

I did try to tick and untick "Handel as a stream messages", no change

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 March 2021 21:35 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/8 "2021-03-08T21:35:50Z")

</div>

What happens if you replace the split node with this Function node

```auto
for (let i=0; i<msg.payload.length; i++) {
    node.send({payload: msg.payload[i]})
}
return null

```

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [8 March 2021 21:39 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/9 "2021-03-08T21:39:08Z")

</div>

> [@Colin](#):
>
> ```auto
> for (let i=0; i<msg.payload.length; i++) {
> node.send({payload: msg.payload[i]})
> }
> return null
> 
> ```

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/8/0858319e69dec9507891d9702570a091a49573c1.png)  
still just return [object Object]

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 March 2021 21:49 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/10 "2021-03-08T21:49:21Z")

</div>

The objects returned by the mysql node are RowDataPackets, which apparently are something that mysql uses, as Google tells me. Are you using the standard mysql node node-red-node-mysql?

If you change the second debug node to display msg.payload.t1\_lat, for example, does it show the correct values?

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [8 March 2021 21:58 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/11 "2021-03-08T21:58:23Z")

</div>

Yes it is the standard node-red-node-mysql.

The values are correct for msg.payload[1].tl\_lat.

As if I query the last entry for the db an send it to the map I can see it is correct on the map

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/d/6da68c2dee8bc3c7d2220889b28c5d410cb1875d.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/d/3d4fcabbbf7697eeb865399f3f4be0ddea466a01.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 March 2021 22:01 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/12 "2021-03-08T22:01:48Z")

</div>

I think the conclusion is that the debug node is not able to show the objects correctly for some reason. Are you on an up to date version of node-red? I don't use mysql so I can't test it myself.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 March 2021 22:02 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/13 "2021-03-08T22:02:24Z")

</div>

You can put back the Split node, you don't need to use the function node.

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [8 March 2021 22:10 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/14 "2021-03-08T22:10:06Z")

</div>

Sorry I think you misunderstood me here, in this second flow I only query the last entry into the db and display it on the map, it works in this instance.

In the first query it some how loses the array data when it gets split.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 March 2021 22:13 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/15 "2021-03-08T22:13:17Z")

</div>

But you said

> [@ScheepersJohan](#):
>
> The values are correct for msg.payload[1].tl\_lat.

---

<div class="post-metadata">

**Author:** ![ScheepersJohan](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/scheepersjohan/32/39556_2.png) [@ScheepersJohan](https://discourse.nodered.org/u/ScheepersJohan)\
**Post date:** [9 March 2021 05:26 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/16 "2021-03-09T05:26:34Z")

</div>

Sorry what I meant were that the actual value of msg.payload[1].tl\_lat. is correct.

If I query just one row from the db I extract the msg.payload[1].tl\_lat value, I can push it to the MAP and it is correct.

As soon as I split it just drops the values from the array.

Here I change it to json and the values are still there and correct.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/2/62a6371a84a4bcf88172db2863b58423246c06bb.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/0/e0e279345c91b5c8958fab63e5d740c445664a26.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/4/d4b91d482f7fa3deb4c705966896e84929e4f7d1.png)  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/5/55c933f67c8e057aff5d45243b21c124331512e9.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 March 2021 09:11 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/17 "2021-03-09T09:11:18Z")

</div>

Since the JSON node appears to have interpreted it correctly, feed it through another JSON node to convert it back to an array, then into the Split node.

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 March 2021 09:27 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/18 "2021-03-09T09:27:47Z")

</div>

If you add a `split` node (as @dceejay suggested) right after the `mysql` node, it will send out a msg/row returned. Edit your "Split" `debug` node and set it to display the 'Complete msg object` and you will see the results.

I'm not sure why just sendig it to a `debug` node displaing just msg.payload displays it as [object Object] but I have duplicated that. (don't worry about my exposing the password - it is a dummy site on my computer)

 ![Screen Shot 2021-03-09 at 4.30.32 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/f/0fd8470a4308b22db86a0ce97480dda1a1404218.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [9 March 2021 09:37 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/19 "2021-03-09T09:37:56Z")

</div>

Try...

```auto
var rows = msg.payload.map((result) => ({
    ...result,
}));

for (let i=0; i<rows.length; i++) {
    node.send({payload: rows[i]})
}
return null

```

if that doesnt work, then a hacky method...

```auto
var rows = JSON.parse(JSON.stringify(msg.payload))
for (let i=0; i<rows.length; i++) {
    node.send({payload: rows[i]})
}
return null

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 March 2021 09:57 UTC](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279/20 "2021-03-09T09:57:40Z")

</div>

The data coming out of the `mysql` node is `an array of `RowDataPacket` objects`.

The good news is if you send it to a `json` node, then to a second `json` node and then the `split` node, the debug with 'msg.payload` will display the contents in a readable format.

[Next page](https://discourse.nodered.org/t/splitting-array-from-mysql-query/42279.md?page=2)