splitting json data

[mark hubrich]

Hi all. I've been messing around with Node-Red for about a week. I have a php script online that grabs some sql and returns data that looks like this:

{"session":"1487078991856","time":"1487079069609","lat":"42.3789","lon":"-88.0911","bearing":"91.7","accX":"-0.0429688","accY":"-0.0324707","volts":"13.0","fuel":"0","throttle":"25.8824","maf":"35.39","coolant":"92","rpm":"1474","mph":"45","vac":"-6.1427736"},{"session":"1487078991856","time":"1487079070610","lat":"42.3789","lon":"-88.091","bearing":"91.5","accX":"-0.00415039","accY":"-0.0556641","volts":"13.0","fuel":"0","throttle":"26.2745","maf":"33.29","coolant":"92","rpm":"1476.5","mph":"46","vac":"-5.8526974"},{"session":"1487078991856","time":"1487079071609","lat":"42.3789","lon":"-88.0908","bearing":"91.6","accX":"-0.0849609","accY":"-0.205811","volts":"13.0","fuel":"0","throttle":"23.9216","maf":"46.29","coolant":"92","rpm":"1671.5","mph":"48","vac":"-9.4786415"},

I have no issue getting this into NodeRed with a GET request. I can't seem to find too many examples for functions to split this into multiple outputs for dashboard gauges & charts. Does anyone have an example or a link to better/more explanation on how to handle? For testing purposes I query my db for the last 100 rows ordered by time. For my final application I will only be concerned with receiving the last record at 1 sec intervals. 

I pretty much have different php files on my website for querying a mysql database. To make a long story short, I'm using an android app called torque pro to log vehicle data. I have it aiming at a mysql server at my dotster webhost. I don't understand the msg.payload structure. Are my data names above "topics"?

I really appreciate anyone can offer :)

mark

[steve rickus]

The "topic" concept you mentioned is not part of this data structure. It's just a way of tagging messages that can be routed through things like MQTT nodes and other flows -- similar to the way the address on a letter gets its contents to the right place (but may more flexible).

The data shown below appears to represent a JSON serialized string representing the rows of your query result. Each "row" is an object surrounded by curly braces, and the list of rows is comma separated. Each row object is a list of "key": "value" pairs that is also separated by commas.

It is easier to visualize if you pass the string through a json node, and then into a debug node, like this:

(of course, if the output from your web api call is already in object format, you won't need the json node)

Within each object, the "keys" like lat, lon, rpm, mph, etc. are simple javascript properties on the object. How you reference them downstream depends on what you want to do with the data. For instance, within a function node (which uses standard javascript syntax), you can grab any object by its index in the array, and you access individual properties by their names:

var row1 = msg.payload[0]; // since javascript arrays are 0-based
var row2 = msg.payload[1];


var lat1 = row1.lat; // for simple keys with no spaces or punctuation, you can use a named property
var lon1 = row1["lon"]; // alternately, the syntax using index name will work for all properties

Generally, it's best to use the core nodes to manipulate your msg.payload, whenever practical. For instance, in your case you may want to grab lat/lon pairs and send them to a map for real-time tracking. The split and change nodes are designed for this, and avoids the use of javascript syntax for looping and mangling arrays of objects. You can find some great examples of this in the flows library, such as this one.

Hope this gets you going in the right direction.

--

Steve

[mark hubrich]

Thanks Steve. It makes more sense now. My php script is echoing 100 rows and it seems there is 100 entries in the array from what I see. It is an easy indicator that I am receiving all the data.

I have the http request to return a parsed data object. The objects listed like above is what you mean?

Here's what my php code  looks like. 

 <?php

ini_set('memory_limit', '-1'); 

$link = mysql_connect('meistxxxxx', 'torque', 'xxx'); 

if (!$link) { 

    die('Could not connect: ' . mysql_error()); 

} 

 

mysql_select_db("torque"); 

$sql = "SELECT session, time, kff1006 AS lat, kff1005 AS lon, kff1007 AS bearing, kff1220 AS accX, kff1221 AS accY,

        kff1238 AS volts, k2f AS fuel, k11 AS throttle, k10 AS maf, k5 AS coolant, kc AS rpm, kd AS mph, kff1202 AS vac FROM raw_logs

        ORDER BY time ASC

LIMIT 100";

 

$result = mysql_query($sql) or trigger_error(mysql_error().$sql);

$emparray = array();

    while($row =mysql_fetch_assoc($result))

    {

        $emparray[] = $row;

    }

   echo json_encode($emparray);

mysql_close($link);

?> 

Do you see anything that you would do differently? I really don't know what I'm doing but I'm determined to have this working before the next race in April. Pretty much the bottom line is I want to see live data in the pits while the car is running the track. I could just buy a $1000 datalogger but what fun would that be? :)

After I get some gauges going I plan to do like you're saying with the map and plot a little car. 

mark

[Dave C-J]

also check out the http://flows.nodered.org/node/node-red-contrib-web-worldmap node 

you just need to set a few more properties using a change node and you can feed it right into the map

[mark hubrich]

I'm loving that :D If I could put some line charts in there too and play some data as I see that car go around that would be awesome. I have some other ideas like doing some kind of geotagging or whatever its called to areas of a map like turn1, turn2, etc. This will allow me to see logged values filtered based on what turn the data was recorded at. Horse before the cart. 

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/LerT4C281VM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/CACXWFwLmRRtMZofb6YiGaDqN0ZHhzGYuLtn7j-dDxgw-ETxroQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Dave C-J]

There is an example in the worldmap node (menu - import - library - examples)  showing how to use it the dashboard..

[steve rickus]

From this SQL statement, it appears that you will always be returning the oldest 100 records, not the most recent. If you will be showing the latest values in various dashboard gauges, you will need a way to query all the new records since that last time you queried -- unless you can find a more elegant way to stream the data realtime into your node-red instance.

Does the data have to be logged into a database before viewing it? You said you have the Torque Pro app pushing the data into a publicly hosted database, right? Is there an option to push it to a message queue instead? Then you could just connect your flow as a client and receive every new record when it occurs, and send some of the data to your dashboard, while sending the full record to your own node-red connected database.

Oh, and if you need some help "monitoring" the system from the pits, I can be available to help!

--

Steve

[mark hubrich]

Here's my script live.. http://unitthirty2.com/lastline.php

For testing purposes I have it at 100 lines. My Nodered doesn't care much for receiving too much at once but in the end I will only want the last line. 

I can aim the app wherever I want. This is what I used as a base.. https://github.com/econpy/torque  I don't know enough code skills to do too much too modify it to my liking.  I have it hosted on a Dotster server. 

I'm not sure if I'll be using the Torque app at the race. Although it would save me the hassle of adding in an external GPS and IMU stuff.I would need internet, also 2 internet connections at the track. I do have an old Ardupilot Mega 2 that is pretty much an arduino mega with extra crap on it.

I have some 900mhz xBee's that have 14 mile line of site range. The longest span I need to cover at the tracks is about 3/4mile max. I have PYOBD installed and working on the raspi. I can send the same data from that since I don't need any GUI in the car anyhow. I have lots of ideas and I need to lay it all out and weed out what's possible to accomplish in 2 months. I already raced this car at 2 events so at least I don't have to build it again. One of the reasons for data capture though is to scientifically prove if performance tweaks are really improving performance or not. I have a powered rear spoiler wing I'm creating that is going to be controlled from a trailer brake controller. The idea is to act as an air brake so we can still run into a corner and brake later than the other cars need to. Also to help force the inside of the car to the ground to help prevent body roll. God I love this sh1t :P

Whats this message que your talking about?  I could send the data out the serial xBee and GSM so if one fails I  have backup. Regardless, I like the way Node-red works and I'll probably use it either way as a base station in the pit. The dashboard is what I'm after. The driver doesn't have time to view all this data. All we have in the car is std. gauges. The guys in the pit can keep an eye on the details, especially boost so we don't blow the head off. :) 

You're more than welcome to join us at the races. You're probably gonna laugh when you see what it is. But it's the most fun I ever had in a car and we do it 3 times a year in the Midwest. We have a 300hp vr6 5speed Jetta. It's boosted and we converted it to burn e85 fuel. we can probably get 500 hp, but we like to race the full 24 hours without melting the engine. 

24hoursoflemons.com It's pretty much real racing, without all the extra rules :D Where else can you compete with a Cadillac with two 500cid engines, or a Porsche 944 with small block chevy? It makes for a long weekend. Next race is at Gingerman Raceway in Michigan. 

Mark 

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/LerT4C281VM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/7fb10892-8aa4-4687-b061-181e7dc010ce%40googlegroups.com.

[steve rickus]

What a blast! I can see this is going to distract me from my real work...

You are right, you do have a bunch of options to sort out. In the end, if you still are using mysql queries you could sort by time descending and limit your results to 1 in order to only get the last row from the database.

I noticed that the torque app allows you to send the data anywhere for processing (looks like just an http POST to the web server url). This is ideal for node-red if you have it running on a server available on the internet somewhere. I would set up an http-in node to provide the url to use in the torque app, then just handle each new set of readings as they arrive. Any idea how often the app sends new data? Is that configurable? As I mentioned, I'd send some of the metrics to a dashboard so you can see what's happening in real time, and also send all the data to a database or file for storage. Also set up some trigger nodes to look for problem conditions (high temp/pressure and such) and send alerts before something fails.

Best of luck, Mark -- maybe I can find a way to be in South Haven that weekend...

--

Steve

[mark hubrich]

Thanks Steve. I'm being distracted from my real work right now. :D I turned on an old debian server I had laying around last night and fired up Node-Red on that. What you say is pretty much what I'm thinking. That torque app is very customizable. It is specific about response though. It wants to see an 'OK' I think after each line sent. I like your idea about just getting torque to log directly to node-red. My internet at home is good and my IP is static so looks like I am going to use this server. 

I think I'll start a new thread here called Torque Pro Node-Red Live Dashboard. There's probably a lot of other peeps here who would like to help create this. A $21 bluetooth OBD2 device and an Android app called Torque Pro $5.  I'm still struggling to figure out sorting this data. The switch  node is what I'm trying to do.

So I'm getting the data like shown in the debug. What is the proper way to enter the values I want to switch?

Thanks

Mark
​

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/LerT4C281VM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/e3fde373-0d50-4dbc-ac7e-1c445453d497%40googlegroups.com.

[Dave C-J]

If you notice in the debug - it is actually an object inside an array (of size 1) - so you want something like  msg.payload[0].time   (as the array index starts from 0)

[mark hubrich]

OK now I get it. 

On Feb 17, 2017 11:16 AM, "Dave C-J" <dce...@gmail.com> wrote:

If you notice in the debug - it is actually an object inside an array (of size 1) - so you want something like  msg.payload[0].time   (as the array index starts from 0)

--

http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/LerT4C281VM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/CACXWFwLyxxCFa4UTWZ41%3DOjLtN7YwPGu%3DsqPwx76HTk2PZoCNQ%40mail.gmail.com.

[mark hubrich]

Thanks again for your help. I finally managed to produce something that worked :)  It starts at 2:47  or so. I started a recording on my browser and went for a coffee. I can see by the timestamp it's showing reading every second. I have lots of work to do on it but I was pleased to see it working at all. https://youtu.be/pgTBho6VDjk 

[Biswarup Banerjee]

Hi Mark,

I have been reading up this thread today. I have a torque pro 2 feeding from the OBD2 on my car. Can you please explain to me from scratch how can I possibly push the data from the torque pro mobile gateway to a cloud-based or on-prem target? I'm a very non-technical person and don't follow the jargons well. So a detailed explanation will help.

Thanks in advance

Biswarup

On Friday, February 17, 2017 at 8:39:43 PM UTC+5:30, mark hubrich wrote:

Thanks Steve. I'm being distracted from my real work right now. :D I turned on an old debian server I had laying around last night and fired up Node-Red on that. What you say is pretty much what I'm thinking. That torque app is very customizable. It is specific about response though. It wants to see an 'OK' I think after each line sent. I like your idea about just getting torque to log directly to node-red. My internet at home is good and my IP is static so looks like I am going to use this server. 

I think I'll start a new thread here called Torque Pro Node-Red Live Dashboard. There's probably a lot of other peeps here who would like to help create this. A $21 bluetooth OBD2 device and an Android app called Torque Pro $5.  I'm still struggling to figure out sorting this data. The switch  node is what I'm trying to do.

So I'm getting the data like shown in the debug. What is the proper way to enter the values I want to switch?

Thanks

Mark
​

On Fri, Feb 17, 2017 at 8:14 AM, steve rickus <sri...@gmail.com> wrote:

What a blast! I can see this is going to distract me from my real work...

You are right, you do have a bunch of options to sort out. In the end, if you still are using mysql queries you could sort by time descending and limit your results to 1 in order to only get the last row from the database.

I noticed that the torque app allows you to send the data anywhere for processing (looks like just an http POST to the web server url). This is ideal for node-red if you have it running on a server available on the internet somewhere. I would set up an http-in node to provide the url to use in the torque app, then just handle each new set of readings as they arrive. Any idea how often the app sends new data? Is that configurable? As I mentioned, I'd send some of the metrics to a dashboard so you can see what's happening in real time, and also send all the data to a database or file for storage. Also set up some trigger nodes to look for problem conditions (high temp/pressure and such) and send alerts before something fails.

Best of luck, Mark -- maybe I can find a way to be in South Haven that weekend...

--

Steve

On Thursday, February 16, 2017 at 9:45:46 PM UTC-5, mark hubrich wrote:

Here's my script live.. http://unitthirty2.com/lastline.php

For testing purposes I have it at 100 lines. My Nodered doesn't care much for receiving too much at once but in the end I will only want the last line. 

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/LerT4C281VM/unsubscribe.

To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.