Merging two data sets with JOIN node

General
1

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:

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

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

2

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

image
image708×122 10.5 KB

{
    "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"
    }
}
3

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.

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

4

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 :slight_smile:

5

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.

6

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.

7

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.

8

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