# Object Sqlite Query, Add Some Data & Merge

**URL:** <https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331>\
**Category:** General\
**Created:** [29 March 2021 00:59 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331 "2021-03-29T00:59:28Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![aa0z](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aa0z](https://discourse.nodered.org/u/aa0z)\
**Post date:** [29 March 2021 00:59 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/1 "2021-03-29T00:59:28Z")

</div>

I have this object payload.

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

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.

---

<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:** [29 March 2021 06:12 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/2 "2021-03-29T06:12:54Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![aa0z](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aa0z](https://discourse.nodered.org/u/aa0z)\
**Post date:** [29 March 2021 12:13 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/3 "2021-03-29T12:13:18Z")

</div>

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

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

then that flows into template node.

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

which then the debug spits out the state.

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

Here is the flow.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/0/30a9d7c8fdc79f87f55b46851d4b766915a79ec7.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:** [29 March 2021 12:25 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/4 "2021-03-29T12:25:11Z")

</div>

So what do you need to do now?

---

<div class="post-metadata">

**Author:** ![aa0z](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aa0z](https://discourse.nodered.org/u/aa0z)\
**Post date:** [29 March 2021 13:02 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/5 "2021-03-29T13:02:07Z")

</div>

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.

---

<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:** [29 March 2021 13:14 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/6 "2021-03-29T13:14:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![aa0z](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aa0z](https://discourse.nodered.org/u/aa0z)\
**Post date:** [29 March 2021 13:30 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/7 "2021-03-29T13:30:38Z")

</div>

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?

---

<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:** [29 March 2021 13:52 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/8 "2021-03-29T13:52:40Z")

</div>

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.

---

<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:** [29 March 2021 13:55 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/9 "2021-03-29T13:55:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![aa0z](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aa0z](https://discourse.nodered.org/u/aa0z)\
**Post date:** [29 March 2021 15:19 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/10 "2021-03-29T15:19:53Z")

</div>

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

```auto
[{"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.

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

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

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.

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

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

---

<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:** [29 March 2021 15:49 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/11 "2021-03-29T15:49:12Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![aa0z](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aa0z](https://discourse.nodered.org/u/aa0z)\
**Post date:** [29 March 2021 16:43 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/12 "2021-03-29T16:43:53Z")

</div>

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

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

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

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

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/864f0aec6a6b0cc87cafd92066f6e34b85bcb216.png)

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

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

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.

---

<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:** [29 March 2021 18:19 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/13 "2021-03-29T18:19:45Z")

</div>

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:  
 ![Screen Shot 2021-03-29 at 2.15.54 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/c/1cf9bfaeccab95c3e40de54fd8e5d9b37fd1f755.png)  
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:  
 ![Screen Shot 2021-03-29 at 2.11.12 PM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/8/c8bb9bb1a47fcca32737ff77434fe91a04f387aa.png)  
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.

---

<div class="post-metadata">

**Author:** ![aa0z](https://avatars.discourse-cdn.com/v4/letter/a/278dde/32.png) [@aa0z](https://discourse.nodered.org/u/aa0z)\
**Post date:** [29 March 2021 19:58 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/14 "2021-03-29T19:58:03Z")

</div>

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?

---

<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:** [29 March 2021 20:42 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/15 "2021-03-29T20:42:13Z")

</div>

> [@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](https://www.youtube.com/playlist?list=PLyNBB9VCLmo1hyO-4fIZ08gqFcXBkHy-6). 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.

---

<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:** [29 March 2021 20:56 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/16 "2021-03-29T20:56:26Z")

</div>

> [@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?

---

<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:** [29 March 2021 21:41 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/17 "2021-03-29T21:41:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [12 April 2021 21:42 UTC](https://discourse.nodered.org/t/object-sqlite-query-add-some-data-merge/43331/18 "2021-04-12T21:42:23Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.