Object Sqlite Query, Add Some Data & Merge

[aa0z]

I have this object payload.

I have a sqlite database with call signs and US states those call signs are associated with.

I'd like to search the database with "call" and have it return a state. If there isn't a state associated with the call, it returns a zero. I'd like to merge the state into the object as "state" and then eventually pass it on to a table which displays freq, call, state, comment and time.

I'm looking as the split and join nodes, but when I split on \n, It's not parsing out "freq". I'm kinda lost on what my next step would be.

[Colin]

Do it a step at a time.
You have shown us the input data, which is good. The next step is to build the SQL query, so what query so you need?

Edit: read the node red docs page Working with Messages to see how to access properties in a JavaScript Object.

[aa0z]

I have the sql syntax created and working...I'm not sure it the most efficient way of calling a sql command, but it works.

A change node starts it...

then that flows into template node.

which then the debug spits out the state.

Here is the flow.

[Colin]

So what do you need to do now?

[aa0z]

So back to my original question.

When I get the state abbreviation back, how do I add it to the original object payload?

I'm assuming I've destroyed my original object payload in the change node by setting msg.payload.arguments[0].call to msg.payload and then passing that payload to the template for the sql query.

How do I merge the 2 state abbreviation I got back from the database query with my original object I passed into the change topic.

I've tried to pass msg.payload.arguments[0].call into the sql topic, it gives me a "undefined" string out of the SQL node. That's something I don't understand, so I'm assuming if that would work, it would append the state abbreviation into msg.payload.arguments[0].state as a string and I'd be good to go.

[zenofmud]

In the change node before you set msg.payload to msg.payload.arguments[0].call why not set msg.origincal_message to msg.payload therefore creating a copy of the original payload which you should be able to access after the SQL query

[aa0z]

Ok, I understand that, but how does the flow know which payload to join the state abbreviation up with on the original message as I'm going to be sending msg after msg into the sql query?

Instead of using a copy, I'd much rather just attach the state to the original object payload, right?

[Colin]

The original payload will flow down the wires in the new property in the message so will be there when you want it. The next message sent in will get its original payload added to the property in that message. So as each message gets to the end it will have its own original payload there. There won't be any crossover between messages.

[zenofmud]

@aa0z

Instead of using a copy, I'd much rather just attach the state to the original object payload, right?

You can't because - as you saw, the set msg.payload to msg.payload.arguments[0].call destroys the original content of msg.payload. Remember each msg is an entity by itself. What happens to it and what happens to the mext msg could be different.

Let me go back to the beginning, Are you trying to create a table to display on the dashboard?
If not, what are you going to do with it?
Where are is the input data (call signs) coming from?
You said:

@aa0z

I'm looking as the split and join nodes, but when I split on \n, It's not parsing out "freq".

can you put a debug node (set to display the complete msg object) on the output of the sqlite and the split nodes and name the two debug nodes

Run a query and limit it to 3 rows and then expand the debug displays and then copy and paste it to a reply

OR

export your flow and attach it to a reply and run the SQL request to return 3 rows and tehn copy the msg.payload from that run and past the data in a reply. That way I could feed the data into the flow starting after the sqlite node and see what is happening.

[aa0z]

@zenofmud

Let me go back to the beginning, Are you trying to create a table to display on the dashboard?
If not, what are you going to do with it?

Yes, the final output is to a table node on a dashboard.

The input data is coming from a telnet session (TCP node) that I'm reading data from and that data is coming from another flow via a link so importing the flow here would probably not be the complete picture for data coming into the flow, but here is the code.

This is also why you see the table "addData" and returnpromise objects in the payload. It's coming from another flow and it's being added to another table in another flow.

[{"id":"9f469e68.6e13f","type":"sqlite","z":"f55cb29c.3fc26","mydb":"b30a749d.aef0d8","sqlquery":"msg.topic","sql":" select state from fcc where callsign=\"msg.payload\"","name":"FCC Database","x":1460,"y":180,"wires":[["f3445173.eae3d","f6a138a8.22f968"]]},{"id":"690324a0.e6630c","type":"template","z":"f55cb29c.3fc26","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"select state from fcc where callsign='{{payload}}'","output":"str","x":1240,"y":180,"wires":[["9f469e68.6e13f"]]},{"id":"864a2180.57d3","type":"link in","z":"f55cb29c.3fc26","name":"Database Lookup In","links":["4fc5bc17.1961c4","869cd822.2e3438","aac64a7d.080918","b338b8f3.498b48","b4e80940.9cb2f8","b5b51e9a.e30e5"],"x":135,"y":240,"wires":[["b98c6b84.83c8a8"]]},{"id":"916294b3.2b9b08","type":"switch","z":"f55cb29c.3fc26","name":"Only MO 1x1, K & VE/VA Calls","property":"payload.arguments[0].call","propertyType":"msg","rules":[{"t":"regex","v":"^[KNW]{1}[0]{1}[A-Z]{1}","vt":"str","case":true},{"t":"regex","v":"^[A]{1}[A-L]{1}","vt":"str","case":true},{"t":"regex","v":"^[KNW]{1}[A-Z0-9]{4,}","vt":"str","case":true},{"t":"regex","v":"^[V,C]{1}[E,Y,O]{1}","vt":"str","case":true}],"checkall":"false","repair":true,"outputs":4,"x":510,"y":240,"wires":[["4f3bdcab.5b90c4"],["4f3bdcab.5b90c4","a2378cc0.25cdd"],["4f3bdcab.5b90c4","a2378cc0.25cdd","c551df74.7cd24"],["4f3bdcab.5b90c4"]]},{"id":"b98c6b84.83c8a8","type":"string","z":"f55cb29c.3fc26","name":"","methods":[{"name":"collapseWhitespace","params":[]}],"prop":"payload.arguments[0].call","propout":"payload.arguments[0].call","object":"msg","objectout":"msg","x":250,"y":240,"wires":[["916294b3.2b9b08"]]},{"id":"cae9698a.eeec78","type":"debug","z":"f55cb29c.3fc26","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1910,"y":180,"wires":[]},{"id":"8ea451e.cfd4cb","type":"change","z":"f55cb29c.3fc26","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].call","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1040,"y":180,"wires":[["690324a0.e6630c"]]},{"id":"f3445173.eae3d","type":"join","z":"f55cb29c.3fc26","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1750,"y":180,"wires":[["cae9698a.eeec78"]]},{"id":"a2378cc0.25cdd","type":"split","z":"f55cb29c.3fc26","name":"","splt":"state","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"payload.arguments[0].call","x":830,"y":180,"wires":[["8ea451e.cfd4cb","1939227d.50beee"]]},{"id":"f6a138a8.22f968","type":"debug","z":"f55cb29c.3fc26","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1680,"y":140,"wires":[]},{"id":"1939227d.50beee","type":"debug","z":"f55cb29c.3fc26","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1000,"y":140,"wires":[]},{"id":"c551df74.7cd24","type":"debug","z":"f55cb29c.3fc26","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1150,"y":240,"wires":[]},{"id":"b30a749d.aef0d8","type":"sqlitedb","db":"fcc.db","mode":"RWC"}]

Here is the whole debug from the following flow.

I've been playing around with the split node and I don't understand this in the split as this might be hanging me up.

I'm still kinda lost....I obviously have limited programming experience and I'm getting into more advanced features to obtain my goal here.

[zenofmud]

Ahhh from the way you were discribing things, I thought you were trying to split the output of the sqlite node. But with the flow I see you are

• getting a msg in from somewhere else (link-in)
• collapsing spaces part of the msg.payload (string)
• Testing for a condition (switch)
• splitting the msg (split)
• setting msg.payload to a piece of msg.payload (change)
• building the query in msg.topic (template)
• running the query (sqlite)
• and doing a join (join)

1. Please set your debug nodes to display the complete msg object
2. in each debug node, enter a unique name so I can associate the debug output to a debug node in the flow
3. put a debug node on the output of the link-in node

I'd like to see what comes out of the link-in node, the split node, the sqlite node and the join node.

[aa0z]

You are 100% correct in every one of your assumptions.

Getting screen shots was tough, so hopefully I got everything.

So it seems to me, after setting msg.payload to msg.payload.arguments[0].call I'm also passing other crap to payload also, which the database is not liking? Not sure.

Hope this debug is helpful.

[zenofmud]

That helped a lot. First change I would make:
in the string node, change it from 'collapseWhitespace' to 'trimRight' - CollapseWhitespace will take all the wajacent whtespace and make it a single white space. So if you have "KB1RZ " it will change it to "KB1RZ " notice the space at the end? TrimRight removes all the trailing whitespace so "KB1RZ " becomes "KB1RZ". Since you are using that value in the SQL statement, unless the database has it stored with the trailing blank, you could get a 'not found' using the 'colapseWhitespace' since
"KB1RZ " does not equal "KB1RZ".

Second change, there is no need to split the payload. After the switch go directly to the change node and and set it up like this:

this will setup msg.callsign for the template node and save the original msg.payload so you can access the information after the sqlite node.

next in the template node set it up like this:

and try it out.

With the split node, you ended up doing four calls to SQLITE. If you look at the output from the sqlite node you will see that in the debugs.

At this point you should have the results of the SQLITE call in msg.payload and the original msg.payload in msg.original.payload to use as you see fit.

[aa0z]

Yep, that was the trick. Thanks for your help. It would of taken me hours and hours of frustration to figure that out.

Now I just need to figure out how to merge msg.payload and msg.original.payload into msg.payload so I can insert it into a dashboard table.

I'm assuming I'll need to do a manual join to merge these two objects?

[zenofmud]

@aa0z

Now I just need to figure out how to merge msg.payload and msg.original.payload into msg.payload

Use a change node and set msg.original.payload.dbresults (or what ever you want it called) to msg.payload then move msg.original.payload to msg.payload

I recommend watching this playlist: Node-RED Essentials. The videos are done by the developers of node-red. They're nice & short and to the point. You will understand a whole lot more in about 1 hour. A small investment for a lot of gain.

[Colin]

@zenofmud

then move msg.original.payload.dbresults to msg.payload

A typo there I think. Should it be move msg.original.payload to msg.payload?

[zenofmud]

Good catch @Colin !!! I’ll fix it now