Merging instead of appending JSON in JOIN node after SPLIT

General
1

I pull some data out of MySQL, split this and then format the results to create the JSON I need from each row and am trying to get this to merge correctly but think I am missing something here. The example flow below results in a very similar experience, where the data in is an array which is split. The split data then needs to be merged and not just added to back in if that makes sense:

The names "Main 1" and "Main 2" are not predictable, so I cant pre create anything here.

I am pretty sure I am missing something with the JOIN node that is obvious but after 2 hours I thought I would ask.

Thanks in advance

Data in

[
    {
        "Main 1": {
            "Value 1": ["a","b","c"]
        }
    },
    {
        "Main 1": {
            "Value 2": ["d","e","f"]
        }
    },
    {
        "Main 2": {
            "Value 3": ["g","h","i"]
        }
    },
    {
        "Main 2": {
            "Value 4": ["j","k","l"]
        }
    }
]

Desired Data Out

[
    {
        "Main 1": {
            "Value 1": ["a","b","c"],
            "Value 2": ["d","e","f"]
        }
    },
    {
        "Main 2": {
            "Value 3": ["g", "h", "i"],
            "Value 4": ["j", "k", "l"] }
    }
]

Very basic example flow showing the issue:

[
    {
        "id": "1c4ee0dd73fb2d38",
        "type": "inject",
        "z": "60f9277b58cf0755",
        "name": "Dummy Data",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "[{\"Main 1\":{\"Value 1\":[\"a\",\"b\",\"c\"]}},{\"Main 1\":{\"Value 2\":[\"d\",\"e\",\"f\"]}},{\"Main 2\":{\"Value 1\":[\"a\",\"b\",\"c\"]}},{\"Main 2\":{\"Value 2\":[\"d\",\"e\",\"f\"]}}]",
        "payloadType": "json",
        "x": 140,
        "y": 1100,
        "wires": [
            [
                "8e6587393cbfff25"
            ]
        ]
    },
    {
        "id": "8e6587393cbfff25",
        "type": "split",
        "z": "60f9277b58cf0755",
        "name": "",
        "splt": "\\n",
        "spltType": "str",
        "arraySplt": 1,
        "arraySpltType": "len",
        "stream": false,
        "addname": "",
        "x": 290,
        "y": 1100,
        "wires": [
            [
                "12ef3b2fda25129a"
            ]
        ]
    },
    {
        "id": "ce9f79b852656bad",
        "type": "debug",
        "z": "60f9277b58cf0755",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 690,
        "y": 1100,
        "wires": []
    },
    {
        "id": "12ef3b2fda25129a",
        "type": "join",
        "z": "60f9277b58cf0755",
        "name": "",
        "mode": "auto",
        "build": "object",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\\n",
        "joinerType": "str",
        "accumulate": true,
        "timeout": "",
        "count": "",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "",
        "reduceFixup": "",
        "x": 530,
        "y": 1100,
        "wires": [
            [
                "ce9f79b852656bad"
            ]
        ]
    }
]
2

Try this using a change node

[{"id":"1c4ee0dd73fb2d38","type":"inject","z":"c791cbc0.84f648","name":"Dummy Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"Main 1\":{\"Value 1\":[\"a\",\"b\",\"c\"]}},{\"Main 1\":{\"Value 2\":[\"d\",\"e\",\"f\"]}},{\"Main 2\":{\"Value 1\":[\"a\",\"b\",\"c\"]}},{\"Main 2\":{\"Value 2\":[\"d\",\"e\",\"f\"]}}]","payloadType":"json","x":120,"y":2440,"wires":[["c4dbab1b.b0ea48"]]},{"id":"c4dbab1b.b0ea48","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"\t$keys($$.payload).{ $: $merge($$.payload[$].*)}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":2440,"wires":[["ce9f79b852656bad"]]},{"id":"ce9f79b852656bad","type":"debug","z":"c791cbc0.84f648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":2440,"wires":[]}]

To do similar in join node you would need to use the reduce sequence option. At which point it is simplier to do it in a JSONata expression

3

That works perfect, I was not married to the idea of using the join to complete it.
For my actual use, where the data is coming from MySQL, getting split, a function formatting it to match, then joining it again followed by the Change you posted has worked perfect.

Much appreciated

4

You can use the JOIN Node as well to reduce the array. So it is not necessary to use JSONATA to reduce an array.

5

Please provide a example so I can learn. Using the OP's data

6

This would help actually if you could share an example as above. Fed in my live data and even though the formatting is identical its not playing ball. I even ensured every value throughout is unique in case that was the issue.

The results I am seeing with my live data is along the lines of this:

[
    {
        "Main 1": {
            "Value 1": ["a","b","c","d","e","f","g","h","i","j","k","l"],
            "Value 2": ["a","b","c","d","e","f","g","h","i","j","k","l"]
        }
    },
    {
        "Main 2": {
            "Value 3": ["a","b","c","d","e","f","g","h","i","j","k","l"],
            "Value 4": ["a","b","c","d","e","f","g","h","i","j","k","l"] }
    }
]

Edited the source data in the first post to match to make things make sense

7

@E1cid , @hobojoe720

Ok - I see - and I think your solution is at the moment the best one, as I still have problems to understand the JSONATA. Reducing the splitted array with a followed join node in reducing the sequence of messages works with JSONATA as well. I am sure that this possible - but in this case I think your (@E1cid ) change node is the most efficient solution.

8

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