JOIN multiple SPLIT to combined object

[H3lpd3sk]

Hi all,

Running 4.0.1

I have the folloing flow, and trying for few hours now to make it do what I want, but getting frustrated now. Maybe someone can enlighten me how to proceed.
I looked around the web and forum ofcourse, without finding a suitable solution?

[{"id":"1d86e5b3.84f24a","type":"inject","z":"54efb553244c241f","name":"msg1","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{ \"Value_A\" : \"A\" , \"Value_B\" : \"B\" }","payloadType":"json","x":330,"y":940,"wires":[["d01fbcf6.026bf"]]},{"id":"d45c77e3.791cc8","type":"inject","z":"54efb553244c241f","name":"msg2","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{ \"Value_C\" : \"C\" }","payloadType":"json","x":330,"y":1020,"wires":[["d01fbcf6.026bf"]]},{"id":"d01fbcf6.026bf","type":"join","z":"54efb553244c241f","name":"","mode":"custom","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"3","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":510,"y":980,"wires":[["89d6da40b9d3e64d"]]},{"id":"89d6da40b9d3e64d","type":"debug","z":"54efb553244c241f","name":"1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":980,"wires":[]}]

Ideally the msg.parts should be configured so that messages are combined into date, x,y by date, string comparison. Shouldn't be so hard?

[jbudd]

Hi @H3lpd3sk, welcome to the forum.

The code you posted is not usable, the forum software mangles it.

Please edit your post, putting lines before and after your code with three backticks in, like this

[E1cid]

You delete msg.parts.index and count so that will not work. Also msg._msgid is different for each message coming out of the splits, so you create 4 new msg.parts.id's
Still i am confused as to what the output should be.

If this is not it

[{"id":"91dc074a0491e77e","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"y\":1.234},{\"date\":\"2022-01-01T02:00:00.000Z\",\"y\":2.456}]","tot":"json"},{"t":"set","p":"topic","pt":"msg","to":"y","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":180,"y":900,"wires":[["7d11862c79e51848"]]},{"id":"3faa320b53878b27","type":"inject","z":"613df62afc8a16bf","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"x","payload":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"x\":1},{\"date\":\"2022-01-01T02:00:00.000Z\",\"x\":2}]","payloadType":"json","x":190,"y":960,"wires":[["91dc074a0491e77e","7d11862c79e51848"]]},{"id":"7d11862c79e51848","type":"join","z":"613df62afc8a16bf","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":350,"y":920,"wires":[["562a6e9c39693d48"]]},{"id":"562a6e9c39693d48","type":"function","z":"613df62afc8a16bf","name":"function 2","func":"let x = msg.payload.x.sort((a,b) =>  a.date<b.date);\nlet y = msg.payload.y.sort((a,b) =>  a.date<b.date)\nmsg.payload = [];\nx.forEach((obj, index) => {\n    msg.payload.push({\n        date: obj.date,\n        x: obj.x,\n        y: y[index].y\n    })\n})\n\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":480,"y":920,"wires":[["cbd7adedd7be02e8"]]},{"id":"cbd7adedd7be02e8","type":"debug","z":"613df62afc8a16bf","name":"debug 34","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":700,"y":880,"wires":[]}]

Can you supply the output you want at the two debugs.

Or low code way

[{"id":"3faa320b53878b27","type":"inject","z":"613df62afc8a16bf","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"x\":1},{\"date\":\"2022-01-01T02:00:00.000Z\",\"x\":2}]","payloadType":"json","x":150,"y":880,"wires":[["91dc074a0491e77e"]]},{"id":"91dc074a0491e77e","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"set","p":"y","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"y\":1.234},{\"date\":\"2022-01-01T02:00:00.000Z\",\"y\":2.456}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":880,"wires":[["d71b1af2e8b0a6e5"]]},{"id":"d71b1af2e8b0a6e5","type":"split","z":"613df62afc8a16bf","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":410,"y":880,"wires":[["3b86a059dfe90375"]]},{"id":"3b86a059dfe90375","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"set","p":"payload.y","pt":"msg","to":"y[msg.parts.index].y","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":920,"wires":[["9acf99b03f145784"]]},{"id":"9acf99b03f145784","type":"join","z":"613df62afc8a16bf","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":490,"y":920,"wires":[["cbd7adedd7be02e8"]]},{"id":"cbd7adedd7be02e8","type":"debug","z":"613df62afc8a16bf","name":"debug 34","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":660,"y":920,"wires":[]}]

[H3lpd3sk]

thanks, changed accordingly

[H3lpd3sk]

I would like to group the data by "date"

Ideally the result should be similiar to:

{ 
"x": 1, "y":1.234, "date": "2022-01-01T01:00:00.000Z",
}

The main challenge is perhaps, that flows are running in series not parallel?

[E1cid]

If you want that at each debug, then something like this

[{"id":"d71b1af2e8b0a6e5","type":"split","z":"613df62afc8a16bf","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":310,"y":900,"wires":[["3b86a059dfe90375"]]},{"id":"91dc074a0491e77e","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"set","p":"y","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"y\":1.234},{\"date\":\"2022-01-01T02:00:00.000Z\",\"y\":2.456}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":180,"y":900,"wires":[["d71b1af2e8b0a6e5"]]},{"id":"3b86a059dfe90375","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"set","p":"payload.y","pt":"msg","to":"y[msg.parts.index].y","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":210,"y":940,"wires":[["00644489eca8bb27"]]},{"id":"3faa320b53878b27","type":"inject","z":"613df62afc8a16bf","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"x\":1},{\"date\":\"2022-01-01T02:00:00.000Z\",\"x\":2}]","payloadType":"json","x":170,"y":840,"wires":[["91dc074a0491e77e"]]},{"id":"00644489eca8bb27","type":"switch","z":"613df62afc8a16bf","name":"","property":"parts.index","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"eq","v":"1","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":370,"y":940,"wires":[["7b97559c856954ab"],["6b33ebc5ed3b86b1"]]},{"id":"6b33ebc5ed3b86b1","type":"debug","z":"613df62afc8a16bf","name":"debug 2575","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":530,"y":960,"wires":[]},{"id":"7b97559c856954ab","type":"debug","z":"613df62afc8a16bf","name":"debug 2576","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":530,"y":900,"wires":[]}]

have x and y arrays in different message properties, so you can work with them.

[H3lpd3sk]

Thanks for your reply, however this is not really what I'm trying to achieve.

The use case is there are several data sources (db, csv, api) with common "date" field (hourly).
So how would one merge this data into one message to process it (in a subflow) ?
I hope a little more context helps?

That is what I tried to reflect in my basic model as well.

[jbudd]

Thanks for fixing your initial post.

I am having a hard time understanding how your input data

{ 
"Value_A" : "A" , 
"Value_B" : "B" 
}

and

{ 
"Value_C" : "C" 
}

can conceivably be transformed into the desired format

{ 
"x": 1, "y":1.234, "date": "2022-01-01T01:00:00.000Z",
}

But I think that you need to untick "Use existing msg.parts property", not least because your input messages don't have msg.parts.

It makes me uneasy to see one input payload with 2 properties and one with just 1. Not sure if it's a problem though.

Can you perhaps show us some real input data and the combined object you want to transform it to?

[E1cid]

@H3lpd3sk

this is not really what I'm trying to achieve.

Then supply the output you want and the inputs that would make this example output. Your examples so far have not shown what you want.

@E1cid

have x and y arrays in different message properties, so you can work with them.

As stated you would call csv and move it to another message property,
then call db and move that to another property, then api etc, then you merge or join them.
Will need clear input and output examples to show you a clear example.

[H3lpd3sk]

Hope this makes it more clear?

[{"id":"3faa320b53878b27","type":"inject","z":"6ed3ed9d6f2d5957","name":"Trigger","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"null","payloadType":"json","x":130,"y":120,"wires":[["91dc074a0491e77e","c37d5112e83db7bc"]]},{"id":"91dc074a0491e77e","type":"change","z":"6ed3ed9d6f2d5957","name":"Datasource B","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"y\":1.234},{\"date\":\"2022-01-01T02:00:00.000Z\",\"y\":2.456}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":140,"wires":[["76ccbb914c4c4063"]]},{"id":"042b575a4673d2d0","type":"split","z":"6ed3ed9d6f2d5957","name":"Split Record","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":490,"y":100,"wires":[["562a6e9c39693d48"]]},{"id":"76ccbb914c4c4063","type":"split","z":"6ed3ed9d6f2d5957","name":"Split Record","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":490,"y":140,"wires":[["562a6e9c39693d48"]]},{"id":"562a6e9c39693d48","type":"function","z":"6ed3ed9d6f2d5957","name":"Set Grouping params","func":"msg.parts = {};\nmsg.parts.id = msg._msgid;\nmsg.parts.type = \"object\";\nmsg.parts.key = \"date\";\n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":700,"y":120,"wires":[["7d11862c79e51848"]]},{"id":"7d11862c79e51848","type":"join","z":"6ed3ed9d6f2d5957","name":"JOIN By Date? ","mode":"custom","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":true,"accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":920,"y":120,"wires":[["779056206a294578"]]},{"id":"779056206a294578","type":"debug","z":"6ed3ed9d6f2d5957","name":"debug 35","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1100,"y":120,"wires":[]},{"id":"c37d5112e83db7bc","type":"change","z":"6ed3ed9d6f2d5957","name":"DataSource A","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"x\":1},{\"date\":\"2022-01-01T02:00:00.000Z\",\"x\":2}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":100,"wires":[["042b575a4673d2d0"]]},{"id":"1bc3e80bcf4b76f0","type":"comment","z":"6ed3ed9d6f2d5957","name":"Expected Result","info":"[\n    {\n        \"date\": \"2022-01-01T01:00:00.000Z\",\n        \"x\": 1,\n        \"y\": 1.234\n    },\n    {\n        \"date\": \"2022-01-01T02:00:00.000Z\",\n        \"x\": 2,\n        \"y\": 2.456\n    }\n]","x":1120,"y":80,"wires":[]}]

I will play around with the suggested datasources in series not parallel as well

[E1cid]

Yes my first post, both examples do exactly that JOIN multiple SPLIT to combined object - #3 by E1cid

as does this

[{"id":"3faa320b53878b27","type":"inject","z":"613df62afc8a16bf","name":"Trigger","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"null","payloadType":"json","x":70,"y":920,"wires":[["c37d5112e83db7bc"]]},{"id":"c37d5112e83db7bc","type":"change","z":"613df62afc8a16bf","name":"DataSource A","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"x\":1},{\"date\":\"2022-01-01T02:00:00.000Z\",\"x\":2}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":220,"y":920,"wires":[["aada9533076c0c1b"]]},{"id":"aada9533076c0c1b","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"a","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":880,"wires":[["91dc074a0491e77e"]]},{"id":"91dc074a0491e77e","type":"change","z":"613df62afc8a16bf","name":"Datasource B","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"y\":1.234},{\"date\":\"2022-01-01T02:00:00.000Z\",\"y\":2.456}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":920,"wires":[["ca7dde831c6c3c73"]]},{"id":"ca7dde831c6c3c73","type":"split","z":"613df62afc8a16bf","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":670,"y":920,"wires":[["ccb2fc45bb27acd0"]]},{"id":"ccb2fc45bb27acd0","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"set","p":"payload.x","pt":"msg","to":"a[msg.parts.index].x","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":210,"y":980,"wires":[["7f77ad6401d051a3"]]},{"id":"7f77ad6401d051a3","type":"join","z":"613df62afc8a16bf","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":true,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":410,"y":980,"wires":[["779056206a294578"]]},{"id":"779056206a294578","type":"debug","z":"613df62afc8a16bf","name":"debug 35","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":600,"y":980,"wires":[]}]

and this , but this looks up the date specifically and does not rely on parts.index.

[{"id":"3faa320b53878b27","type":"inject","z":"613df62afc8a16bf","name":"Trigger","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"null","payloadType":"json","x":70,"y":920,"wires":[["c37d5112e83db7bc"]]},{"id":"c37d5112e83db7bc","type":"change","z":"613df62afc8a16bf","name":"DataSource A","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"x\":1},{\"date\":\"2022-01-01T02:00:00.000Z\",\"x\":2}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":220,"y":920,"wires":[["aada9533076c0c1b"]]},{"id":"aada9533076c0c1b","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"a","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":880,"wires":[["91dc074a0491e77e"]]},{"id":"91dc074a0491e77e","type":"change","z":"613df62afc8a16bf","name":"Datasource B","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"y\":1.234},{\"date\":\"2022-01-01T02:00:00.000Z\",\"y\":2.456}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":920,"wires":[["ca7dde831c6c3c73"]]},{"id":"ca7dde831c6c3c73","type":"split","z":"613df62afc8a16bf","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":670,"y":920,"wires":[["ccb2fc45bb27acd0"]]},{"id":"ccb2fc45bb27acd0","type":"change","z":"613df62afc8a16bf","name":"","rules":[{"t":"set","p":"payload.x","pt":"msg","to":"$$.a[$.date = $$.payload.date].x","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":210,"y":980,"wires":[["7f77ad6401d051a3"]]},{"id":"7f77ad6401d051a3","type":"join","z":"613df62afc8a16bf","name":"","mode":"auto","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","useparts":false,"accumulate":true,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":410,"y":980,"wires":[["779056206a294578"]]},{"id":"779056206a294578","type":"debug","z":"613df62afc8a16bf","name":"debug 35","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":600,"y":980,"wires":[]}]

[H3lpd3sk]

The JSONATA variant is probably the better one indeed!

I see now, it can be done with flow context as well with lookup.

Note to self work in series not parallels.

Thanks for looking everyone, concider this resolved

[{"id":"4945983363531c10","type":"inject","z":"6ed3ed9d6f2d5957","name":"Trigger","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"null","payloadType":"json","x":110,"y":480,"wires":[["21751211b66aa21d"]]},{"id":"550f0c42ddaaab8e","type":"split","z":"6ed3ed9d6f2d5957","name":"Split Record","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","property":"payload","x":470,"y":480,"wires":[["7490ddfb4bfe7cd6"]]},{"id":"7490ddfb4bfe7cd6","type":"function","z":"6ed3ed9d6f2d5957","name":"Set Grouping params","func":"const b = flow.get(\"b\").find(x => x.date === msg.payload.date);\n\nmsg.payload = {\n    ...msg.payload,\n    ...b,\n}; \n\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":480,"wires":[["be554fe8d6b422b9"]]},{"id":"be554fe8d6b422b9","type":"debug","z":"6ed3ed9d6f2d5957","name":"debug 37","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":880,"y":480,"wires":[]},{"id":"0810ad30ad33eb83","type":"change","z":"6ed3ed9d6f2d5957","name":"DataSource A","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"x\":1},{\"date\":\"2022-01-01T02:00:00.000Z\",\"x\":2}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":480,"wires":[["550f0c42ddaaab8e"]]},{"id":"21751211b66aa21d","type":"change","z":"6ed3ed9d6f2d5957","name":"Datasource B","rules":[{"t":"set","p":"payload","pt":"msg","to":"[{\"date\":\"2022-01-01T01:00:00.000Z\",\"y\":1.234},{\"date\":\"2022-01-01T02:00:00.000Z\",\"y\":2.456}]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":260,"y":540,"wires":[["4f7a8e922b6ca9d8"]]},{"id":"4f7a8e922b6ca9d8","type":"change","z":"6ed3ed9d6f2d5957","name":"Store in context","rules":[{"t":"set","p":"b","pt":"flow","to":"payload","tot":"msg","dc":true}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":540,"wires":[["0810ad30ad33eb83"]]}]

[E1cid]

There is no need to use flow context, and you should avoid using it unless there is no other way.
You should do it all in series and move the payloads to other properties before loading next payload.

[H3lpd3sk]

For a lookup context is required I suppose?

[E1cid]

No, you can look up any array in any other message property, that is why I said to move the payloads for later use. using context can introduce race issues, and you should avoid it if you can pass the info in the message.

[H3lpd3sk]

Perfect explanation, thanks