# Merging two data sets with JOIN node

**URL:** <https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419>\
**Category:** General\
**Created:** [15 October 2024 21:08 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419 "2024-10-15T21:08:22Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [15 October 2024 21:08 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/1 "2024-10-15T21:08:22Z")

</div>

Hi there! I've been trying (unsuccessfully) to merge two data sets using JOIN node using a common key that exists in both sets (msg.name) ... here is what the first data set looks like:

```auto
{"ticker":"NASDAQ:WAVE","name":"WAVE","close":10.5,"gap":5.5,"change":20.3,"volume":583738,"relative_volume":5,"relative_volume_10d_calc":8.1,"relative_volume_intraday|5":7.8,"relative_volume_10d_calc|5":2.4,"premarket_change":8.1,"float_shares_outstanding":5326643,"relVol10d|5":241}

```

here is the second:

```auto
{"settlementDate":"2024-09-30","issueName":"Eco Wave Power Global AB (publ","symbolCode":"WAVE","marketClassCode":"SC","currentShortPositionQuantity":"28000","previousShortPositionQuantity":"28653","changePreviousNumber":"-653","changePercent":"-2.28","averageDailyVolumeQuantity":"23647","daysToCoverQuantity":"1.18","revisionFlag":null,"name":"WAVE"}

```

I've tried every setting in the JOIN mode, the data is not getting merged (in the output I am seeing values from just one of the sets. Thanks in advance!

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [15 October 2024 21:42 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/2 "2024-10-15T21:42:18Z")

</div>

> [@sonificator](#):
>
> I've tried every setting in the JOIN mode

Each data set needs a different msg.topic.  
Set the join node to manual mode, join after 2 messages.

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

```auto
{
    "dataset1":{
        "ticker":"NASDAQ:WAVE","name":"WAVE","close":10.5,"gap":5.5,"change":20.3,"volume":583738,"relative_volume":5,"relative_volume_10d_calc":8.1,"relative_volume_intraday|5":7.8,"relative_volume_10d_calc|5":2.4,"premarket_change":8.1,"float_shares_outstanding":5326643,"relVol10d|5":241
    },
    "dataset2":{
        "settlementDate":"2024-09-30","issueName":"Eco Wave Power Global AB (publ","symbolCode":"WAVE","marketClassCode":"SC","currentShortPositionQuantity":"28000","previousShortPositionQuantity":"28653","changePreviousNumber":"-653","changePercent":"-2.28","averageDailyVolumeQuantity":"23647","daysToCoverQuantity":"1.18","revisionFlag":null,"name":"WAVE"
    }
}

```

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [15 October 2024 21:51 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/3 "2024-10-15T21:51:11Z")

</div>

If you give each message the correct msg.parts you can then use a reduced sequence to merge the two messages into one object  
e.g.

```auto
[{"id":"2fef465e61206fbf","type":"inject","z":"d1395164b4eec73e","name":"","props":[{"p":"payload"},{"p":"parts","v":"{\"id\":\"my_unique_id\",\"type\":\"array\",\"count\":2,\"index\":0}","vt":"json"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"ticker\":\"NASDAQ:WAVE\",\"name\":\"WAVE\",\"close\":10.5,\"gap\":5.5,\"change\":20.3,\"volume\":583738,\"relative_volume\":5,\"relative_volume_10d_calc\":8.1,\"relative_volume_intraday|5\":7.8,\"relative_volume_10d_calc|5\":2.4,\"premarket_change\":8.1,\"float_shares_outstanding\":5326643,\"relVol10d|5\":241}","payloadType":"json","x":90,"y":7740,"wires":[["f735ce51b75bca2b"]]},{"id":"f735ce51b75bca2b","type":"join","z":"d1395164b4eec73e","name":"","mode":"reduce","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"$merge([$A, $$.payload])","reduceInit":"{}","reduceInitType":"json","reduceFixup":"$A","x":290,"y":7760,"wires":[["6c29de9b0257ebe5"]]},{"id":"ea572cdc5ff7ec59","type":"inject","z":"d1395164b4eec73e","name":"","props":[{"p":"payload"},{"p":"parts","v":"{\"id\":\"my_unique_id\",\"type\":\"array\",\"count\":2,\"index\":1}","vt":"json"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"settlementDate\":\"2024-09-30\",\"issueName\":\"Eco Wave Power Global AB (publ\",\"symbolCode\":\"WAVE\",\"marketClassCode\":\"SC\",\"currentShortPositionQuantity\":\"28000\",\"previousShortPositionQuantity\":\"28653\",\"changePreviousNumber\":\"-653\",\"changePercent\":\"-2.28\",\"averageDailyVolumeQuantity\":\"23647\",\"daysToCoverQuantity\":\"1.18\",\"revisionFlag\":null,\"name\":\"WAVE\"}","payloadType":"json","x":90,"y":7780,"wires":[["f735ce51b75bca2b"]]},{"id":"6c29de9b0257ebe5","type":"debug","z":"d1395164b4eec73e","name":"debug 2578","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":7760,"wires":[]}]

```

[edit]  
Or use the manual merge with a timeout, or send msg complete with second message

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [15 October 2024 22:31 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/4 "2024-10-15T22:31:48Z")

</div>

Thanks so much! Spent half a day with ChatGPT trying to solve. ChatGPT did help with a couple of challenges, but couldn't figure out this one 🙂

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [15 October 2024 22:39 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/5 "2024-10-15T22:39:43Z")

</div>

Actually still a bit of a problem, This worked when I had one record coming from each source ( I filtered "WAVE" out to help with debugging); however when I have a set (sequence of 10 from each) they don't get joined ... should I put some kind of delay/timeout ?

P.S.

Placing a delay node (with rate limit of 1 msg/sec) seemed to work (help). But successful joining seems intermittent (observed even with one record) ... sometimes the two sets get joined, sometimes I see two date sets coming out separately on the output of join node.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [16 October 2024 06:31 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/6 "2024-10-16T06:31:14Z")

</div>

Not sure what you are saying as it is a bit vague, example data and flows help here.

Try setting the parts.id to the name property of the incoming objects. failing that provide clear examples.

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [17 October 2024 18:37 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/7 "2024-10-17T18:37:08Z")

</div>

Turns out there were data mismatches in that field from one of the sources. Ended up doing it a different way (passing one set of data in payload the other in topic). Thanks for your help.

---

<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:** [31 October 2024 18:37 UTC](https://discourse.nodered.org/t/merging-two-data-sets-with-join-node/92419/8 "2024-10-31T18:37:37Z")

</div>

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