Sort messages (rather than array within each message)

Using Node-RED v1.2.7. I have 3 XML files I am processing. I want to batch the 3 streams back into a single, ordered, stream but am unable to (and perhaps I am thinking data tables and not "flows")

There can be over 100 settings in the three files (and resulting collection/array). Something akin to (in each file)...

<settings>
  <setting prop1="1" />
  <setting prop1="2" />
  <setting prop1="3" />
</settings>

I see that the sort node sorts elements within an array per "msg". How can I append/join the streams back into one, and sort them? I thought about using a flow data context and a function to append to a single variable... but surely there is a better way within the palette?

If I understand correctly then the key word is Join. Have a look at the Join node which can join multiple messages together in various ways. I imagine you want to combine them into an array of three objects and then you can concatenate then together in the right order.

Edit: Welcome to the forum, I missed the fact that you are a new member.

Thank you, I am glad I found this community!

I understand that join can merge messages, but I am hoping to append the latter messages to former in a single array object... rather than merging the messages into a single message.

If you happen to be familiar with SQL, it would be something akin to...

select w1.a, w2.b
from wire1 w1 join wire2 w2 on w1.id = w2.id

...would merge the two streams as you described. This assumes the same id is in each, and some properties of each message will be used to construct a new single message per input message.

What I am trying to achieve is something like...

select a
from (
  select w1.a
  from wire1 w1

  union
  select w2.a
  from wire2 w2
) both
order by both.id

Which (in my case) will simply append the messages from the latter input, unchanged, to the original list.

Does that make more sense?

Once you have combined them into one message then you can merge the individual results into one set.
Get them into one message first and then you can work out how to combine them.

Ugh.... there is clearly something I am missing here.

[{"id":"50218f9a.469df8","type":"tab","label":"Flow 9","disabled":false,"info":""},{"id":"9a14acb6.6dd72","type":"xml","z":"50218f9a.469df8","name":"","property":"payload","attr":"","chr":"","x":530,"y":220,"wires":[["31f0acef.5b83ac"]]},{"id":"31f0acef.5b83ac","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"msg.payload.Channels.Channel","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":710,"y":220,"wires":[["3d03ec25.73ecc4"]]},{"id":"3d03ec25.73ecc4","type":"split","z":"50218f9a.469df8","name":"","splt":",","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":880,"y":220,"wires":[["7010b7f2.8fb6f8","885f5d0f.3427b"]]},{"id":"42fedac5.7f6b24","type":"join","z":"50218f9a.469df8","name":"","mode":"custom","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":750,"y":640,"wires":[["58eaa860.be847"]]},{"id":"43cf6866.589f78","type":"function","z":"50218f9a.469df8","name":"","func":"msg.payload = $append(array1, array2);\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":760,"y":600,"wires":[[]]},{"id":"1048f1aa.8deaee","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$append(array1, array2)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":780,"y":560,"wires":[[]]},{"id":"61750c95.26b3ac","type":"comment","z":"50218f9a.469df8","name":"multiple input wires then $append(array1, array2), then sort","info":"multiple input wires\nthen $append(array1, array2), then sort","x":750,"y":680,"wires":[]},{"id":"58eaa860.be847","type":"batch","z":"50218f9a.469df8","name":"","mode":"concat","count":"2","overlap":0,"interval":10,"allowEmptySequence":false,"topics":[],"x":880,"y":640,"wires":[[]]},{"id":"aa4156dd.4c607","type":"xml","z":"50218f9a.469df8","name":"","property":"payload","attr":"","chr":"","x":540,"y":280,"wires":[["266a040a.4dd9b4"]]},{"id":"266a040a.4dd9b4","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"msg.payload.Channels.Channel","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":280,"wires":[["f0fb987e.e33ce8"]]},{"id":"f0fb987e.e33ce8","type":"split","z":"50218f9a.469df8","name":"","splt":",","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":880,"y":280,"wires":[["7010b7f2.8fb6f8","9e5ffff.f8e16"]]},{"id":"ce196d52.ae4e1","type":"xml","z":"50218f9a.469df8","name":"","property":"payload","attr":"","chr":"","x":540,"y":340,"wires":[["8e2ff621.0dd85"]]},{"id":"8e2ff621.0dd85","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"msg.payload.Channels.Channel","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":720,"y":340,"wires":[["71b0d019.8e421"]]},{"id":"71b0d019.8e421","type":"split","z":"50218f9a.469df8","name":"","splt":",","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":880,"y":340,"wires":[["b1a4010b.4738"]]},{"id":"b1a4010b.4738","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.$","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1060,"y":340,"wires":[[]]},{"id":"bb642219.5e79d","type":"debug","z":"50218f9a.469df8","name":"joined output","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1210,"y":240,"wires":[]},{"id":"7010b7f2.8fb6f8","type":"join","z":"50218f9a.469df8","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"payload.$.ChannelID","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":1040,"y":240,"wires":[["bb642219.5e79d"]]},{"id":"885f5d0f.3427b","type":"debug","z":"50218f9a.469df8","d":true,"name":"system-a pre-join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1080,"y":200,"wires":[]},{"id":"9e5ffff.f8e16","type":"debug","z":"50218f9a.469df8","d":true,"name":"system-b pre-join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1080,"y":280,"wires":[]},{"id":"f4ded11b.7534d8","type":"comment","z":"50218f9a.469df8","name":"these are various options for merging the msgs.  to use in successive steps","info":"","x":810,"y":520,"wires":[]},{"id":"9feb7e83.4c7478","type":"inject","z":"50218f9a.469df8","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":280,"wires":[["f8db3eb3.f533b","361f002a.4274","dcaddb08.6225d"]]},{"id":"f8db3eb3.f533b","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"<Channels>     <Channel ChannelID=\"1\" PropertyA=\"A\" />     <Channel ChannelID=\"2\" PropertyA=\"A\" />     <Channel ChannelID=\"3\" PropertyA=\"A\" /> </Channels>","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":220,"wires":[["9a14acb6.6dd72"]]},{"id":"361f002a.4274","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"<Channels>     <Channel ChannelID=\"1\" PropertyB=\"B\" />     <Channel ChannelID=\"2\" PropertyB=\"B\" />     <Channel ChannelID=\"3\" PropertyB=\"B\" /> </Channels>","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":280,"wires":[["aa4156dd.4c607"]]},{"id":"dcaddb08.6225d","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"<Channels>     <Channel ChannelID=\"4\" PropertyA=\"A\" PropertyB=\"B\" />     <Channel ChannelID=\"5\" PropertyA=\"A\" PropertyB=\"B\" />     <Channel ChannelID=\"6\" PropertyA=\"A\" PropertyB=\"B\" /> </Channels>","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":340,"wires":[["ce196d52.ae4e1"]]}]

This flow is somewhat illustrative of what I am trying to accomplish. Two files ("system-a" and "system-b") have the same child items. "system-a" has one property, and "system-b" has the other, for each child item. I need to merge these two with all the properties of each. Then, I need to append to that array, the user collection (which has the same merged formatting, but is a continuation of the master list).

I've tried a myriad of variations for the join (and nodes leading up to it) but given the parts objects are essentially the same (except for their individual part.ids, understandably) I thought "automatic" should work great. I stand corrected.

The documentation demonstrates using a key/value Object join, which I've tried in my flow. It seems a little closer, but the two streams are being merged in a strange fashion. It seems so strange that selecting merged Object for the join, I get no debug output.

I am trying to get the msg =

{
    "ChannelID": 1,
    "PropertyA": "A",
    "PropertyB": "B"
},

To do some further manipulation. Then finally (once the rest of the manipulations are done), the msg =

[
    {
        "ChannelID": 1,
        "PropertyA": "A",
        "PropertyB": "B"
    },
    {
        "ChannelID": 2,
        "PropertyA": "A",
        "PropertyB": "B"
    },
    {
        "ChannelID": 3,
        "PropertyA": "A",
        "PropertyB": "B"
    },
    {
        "ChannelID": 4,
        "PropertyA": "A",
        "PropertyB": "B"
    },
    {
        "ChannelID": 5,
        "PropertyA": "A",
        "PropertyB": "B"
    },
    {
        "ChannelID": 6,
        "PropertyA": "A",
        "PropertyB": "B"
    },
]

Perhaps I should create a separate web service which will do all this parsing, and leave nodered to just message passing?

Try the Join as an array.

That creates a strange effect (similar to the key/value setting results). The first message output...

[
    {
        "$": {
            "ChannelID": "1",
            "PropertyA": "A"
        }
    },
    {
        "$": {
            "ChannelID": "2",
            "PropertyA": "A"
        }
    }
]

...as opposed to what I am after, which would be...

[
    {
        "$": {
            "ChannelID": "1",
            "PropertyA": "A"
        }
    },
    {
        "$": {
            "ChannelID": "1",
            "PropertyA": "B"
        }
    }
]

...which then allow me to join the properties into a single message.

Try something like this

[{"id":"f8db3eb3.f533b","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"<Channels>     <Channel ChannelID=\"1\" PropertyA=\"A\" />     <Channel ChannelID=\"2\" PropertyA=\"A\" />     <Channel ChannelID=\"3\" PropertyA=\"A\" /> </Channels>","tot":"str"},{"t":"set","p":"topic","pt":"msg","to":"1","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":220,"wires":[["aa4156dd.4c607"]]},{"id":"9feb7e83.4c7478","type":"inject","z":"50218f9a.469df8","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payloadType":"str","x":650,"y":140,"wires":[["f8db3eb3.f533b","361f002a.4274","dcaddb08.6225d"]]},{"id":"aa4156dd.4c607","type":"xml","z":"50218f9a.469df8","name":"","property":"payload","attr":"","chr":"","x":550,"y":280,"wires":[["7010b7f2.8fb6f8"]]},{"id":"361f002a.4274","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"<Channels>     <Channel ChannelID=\"1\" PropertyB=\"B\" />     <Channel ChannelID=\"2\" PropertyB=\"B\" />     <Channel ChannelID=\"3\" PropertyB=\"B\" /> </Channels>","tot":"str"},{"t":"set","p":"topic","pt":"msg","to":"2","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":280,"wires":[["aa4156dd.4c607"]]},{"id":"dcaddb08.6225d","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"<Channels>     <Channel ChannelID=\"4\" PropertyA=\"A\" PropertyB=\"B\" />     <Channel ChannelID=\"5\" PropertyA=\"A\" PropertyB=\"B\" />     <Channel ChannelID=\"6\" PropertyA=\"A\" PropertyB=\"B\" /> </Channels>","tot":"str"},{"t":"set","p":"topic","pt":"msg","to":"3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":340,"wires":[["aa4156dd.4c607"]]},{"id":"7010b7f2.8fb6f8","type":"join","z":"50218f9a.469df8","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"3","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"num","reduceFixup":"","x":670,"y":280,"wires":[["5d8f0498.d44e0c"]]},{"id":"5d8f0498.d44e0c","type":"change","z":"50218f9a.469df8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$map(\t   $distinct(payload[*].Channels.Channel[*].*.ChannelID),\t   function($i){\t       $merge(payload[*].Channels.Channel[*].*[ChannelID = $i])\t\t\t}\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":840,"y":280,"wires":[["64a5f7be.39dd5"]]},{"id":"64a5f7be.39dd5","type":"debug","z":"50218f9a.469df8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1010,"y":140,"wires":[]}]
$map(
   $distinct(payload[*].Channels.Channel[*].*.ChannelID),
   function($i){
       $merge(payload[*].Channels.Channel[*].*[ChannelID = $i])
})

and join via array

1 Like

Is all the information that you need in the first structure you show? If not then what were the input messages that gave rise to that output?

With a little variation for the example I gave (3rd source a little different than the prior two)... that's precisely it! I am digesting your sample now and will provide more feedback. Thank you!

My word, I had no idea how powerful JSONata is!

I'm not clear on what you are referring to with, "first structure"?

So, it works fantastically and will be a great resource for me to study further. I am hoping the "why" will make more sense as my mind learns to think in node-red. Thanks again!

2 Likes

For any coming across this later... I stand corrected, this also beautifully solves the problem of merging the disparate 3rd source.

1 Like