Splitting array - from MySQL query

General
1

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

image

[{"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

2

Maybe the split node to split the array ?

3

image
image764Ă—181 8.5 KB

This is what I used, but it returns for each [object Object]

4

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.

5

image
image727Ă—179 9.55 KB

image

image

6

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.

7

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

8

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

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

image
image1094Ă—333 22.3 KB

still just return [object Object]

10

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?

11

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
image

12

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.

13

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

14

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.

15

But you said

16

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
image748Ă—271 14.9 KB

image

image
image

17

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.

1 Like
18

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
Screen Shot 2021-03-09 at 4.30.32 AM292Ă—533 39.4 KB

19

Try...

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

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

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.

1 Like