Sort messages (rather than array within each message)

SewShulMeedeeUh · 9 February 2021 07:05

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?

Colin · 9 February 2021 07:22

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.

SewShulMeedeeUh · 9 February 2021 22:18

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?

Colin · 10 February 2021 07:36

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.

SewShulMeedeeUh · 11 February 2021 10:10

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?

Colin · 11 February 2021 12:13

Try the Join as an array.

SewShulMeedeeUh · 11 February 2021 12:22

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.

E1cid · 11 February 2021 13:29

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

Colin · 11 February 2021 13:51

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?

SewShulMeedeeUh · 11 February 2021 22:13

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!

SewShulMeedeeUh · 11 February 2021 22:16

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

SewShulMeedeeUh · 12 February 2021 06:40

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!

SewShulMeedeeUh · 13 February 2021 05:19

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

system · 27 February 2021 05:19

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

Topic		Replies	Views
Combining Messages General	11	1070	16 March 2020
How to sort a subset of data within an XML structure General	4	1185	26 November 2019
Combination of filtered/sorted messages General	8	1290	24 February 2019
Combine several messages from MQTT into one request to DataBase General	15	3624	22 June 2020
[Solved] Join several object messages to one object message? General	4	1269	10 November 2019

Sort messages (rather than array within each message)

Related topics