Rearranging an Array and then adding more labels to a JSON expression

mudwalker · 27 March 2023 17:16

Hi,

I have JSON expression of a list of devices connected to my Network. I have searched the Forum for similar requirements, with no success. Consequently I have spent quite some time trying to re-arrange the array to a format I can use to inject into a routine, but have so far been unsuccessful.

Each device has an entry with the following format

{
    "url": "XXX.XXX.XXX.1",
    "name": "NAME1",
    "type": "service",
    "link": "http://NAME1",
    "category": "Service"
 }

I inject this list into a Change Node to create a sorted list by 'category' and then by 'url' to create groups of Devices. That part works fine, but then I want to add a couple more labels to the array to create a section of the same 'category' and then a label to make sure checks are done to each group in a later routine.

{  
        "group": "Service",
        "checks": [
            {
                "url":"XXX.XXX.XXX.1",
                "name":"NAME1",
                "type":"service",
                "link":"http://NAME1",
                "category":"Service"
            },
            {
                "url":"XXX.XXX.XXX.10",
                "name":"NAME10",
                "type":"remote",
                "link":"",
                "category":"Service"
            }
        ]
    },
    {  
        "group": "Website",
        "checks": [
            {
                "url":"XXX.XXX.XXX.2",
                "name":"NAME2",
                "type":"web server",
                "link":"https://NAME2",
                "category":"Website"
            }
        ]
    }

Here is the flow that sorts the data and then inject/debug nodes just to display the desired output.

[{"id":"2abf0555b0cf5827","type":"change","z":"0e2cba8a481c3fb9","name":"srt category,IP","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload^(category, url)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1090,"y":1260,"wires":[["ea37488b99070538"]]},{"id":"ea37488b99070538","type":"debug","z":"0e2cba8a481c3fb9","name":"Sorted array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1270,"y":1260,"wires":[]},{"id":"3919b82fdc5103db","type":"inject","z":"0e2cba8a481c3fb9","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[     {         \"url\": \"XXX.XXX.XXX.1\",         \"name\": \"NAME1\",         \"type\": \"service\",         \"link\": \"http://NAME1\",         \"category\": \"Service\"     },     {         \"url\": \"XXX.XXX.XXX.2\",         \"name\": \"NAME2\",         \"type\": \"web server\",         \"link\": \"https://NAME2\",         \"category\": \"Website\"     },     {         \"url\": \"XXX.XXX.XXX.3\",         \"name\": \"NAME3\",         \"type\": \"internet\",         \"link\": \"http://XXX.XXX.XXX.3\",         \"category\": \"Internet\"     },     {         \"url\": \"XXX.XXX.XXX.4\",         \"name\": \"NAME4\",         \"type\": \"extender\",         \"link\": \"http://XXX.XXX.XXX.4\",         \"category\": \"Internet\"     },     {         \"url\": \"XXX.XXX.XXX.5\",         \"name\": \"NAME5\",         \"type\": \"web server\",         \"link\": \"https://XXXXXXXXXXXXX\",         \"category\": \"Server\"     },     {         \"url\": \"XXX.XXX.XXX.6\",         \"name\": \"NAME6\",         \"type\": \"printer\",         \"link\": \"http://NAME6\",         \"category\": \"Network\"     },     {         \"url\": \"XXX.XXX.XXX.10\",         \"name\": \"NAME10\",         \"type\": \"remote\",         \"link\": \"\",         \"category\": \"Service\"     },     {         \"url\": \"XXX.XXX.XXX.13\",         \"name\": \"NAME13\",         \"type\": \"server\",         \"link\": \"http://XXX.XXX.XXX.13\",         \"category\": \"Server\"     },     {         \"url\": \"XXX.XXX.XXX.14\",         \"name\": \"NAME14\",         \"type\": \"heating\",         \"link\": \"\",         \"category\": \"IOT\"     },     {         \"url\": \"XXX.XXX.XXX.15\",         \"name\": \"NAME15\",         \"type\": \"heating\",         \"link\": \"\",         \"category\": \"IOT\"     },     {         \"url\": \"http://NAME18\",         \"name\": \"NAME18\",         \"type\": \"website\",         \"link\": \"http://18\",         \"category\": \"Server\"     } ]","payloadType":"jsonata","x":930,"y":1260,"wires":[["2abf0555b0cf5827"]]},{"id":"ebc1c44c1a8b7914","type":"inject","z":"0e2cba8a481c3fb9","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[\t    {  \t        \"group\": \"IOT\",\t        \"checks\": [\t            {\t                \"url\":\"XXX.XXX.XXX.14\",\t                \"name\":\"NAME14\",\t                \"type\":\"heating\",\t                \"link\":\"\",\t                \"category\":\"IOT\"\t            },\t            {\t                \"url\":\"XXX.XXX.XXX.15\",\t                \"name\":\"NAME15\",\t                \"type\":\"heating\",\t                \"link\":\"\",\t                \"category\":\"IOT\"\t            }\t        ]\t    },\t    {  \t        \"group\": \"Internet\",\t        \"checks\": [\t            {\t                \"url\":\"XXX.XXX.XXX.3\",\t                \"name\":\"NAME3\",\t                \"type\":\"internet\",\t                \"link\":\"http://XXX.XXX.XXX.3\",\t                \"category\":\"Internet\"\t            },\t            {\t                \"url\":\"XXX.XXX.XXX.4\",\t                \"name\":\"NAME4\",\t                \"type\":\"extender\",\t                \"link\":\"http://XXX.XXX.XXX.4\",\t                \"category\":\"Internet\"\t            }\t        ]\t    },\t    {  \t        \"group\": \"Network\",\t        \"checks\": [\t            {\t                \"url\":\"XXX.XXX.XXX.6\",\t                \"name\":\"NAME6\",\t                \"type\":\"printer\",\t                \"link\":\"http://NAME6\",\t                \"category\":\"Network\"\t            }\t        ]\t    },\t    {  \t        \"group\": \"Server\",\t        \"checks\": [\t            {\t                \"url\":\"XXX.XXX.XXX.13\",\t                \"name\":\"NAME13\",\t                \"type\":\"server\",\t                \"link\":\"http://XXX.XXX.XXX.13\",\t                \"category\":\"Server\"\t            },\t            {\t                \"url\":\"XXX.XXX.XXX.5\",\t                \"name\":\"NAME5\",\t                \"type\":\"web server\",\t                \"link\":\"https://XXXXXXXXXXXXX\",\t                \"category\":\"Server\"\t            },\t            {\t                \"url\":\"http://NAME18\",\t                \"name\":\"NAME18\",\t                \"type\":\"website\",\t                \"link\":\"http://18\",\t                \"category\":\"Server\"\t            }\t        ]\t    },\t    {  \t        \"group\": \"Service\",\t        \"checks\": [\t            {\t                \"url\":\"XXX.XXX.XXX.1\",\t                \"name\":\"NAME1\",\t                \"type\":\"service\",\t                \"link\":\"http://NAME1\",\t                \"category\":\"Service\"\t            },\t            {\t                \"url\":\"XXX.XXX.XXX.10\",\t                \"name\":\"NAME10\",\t                \"type\":\"remote\",\t                \"link\":\"\",\t                \"category\":\"Service\"\t            }\t        ]\t    },\t    {  \t        \"group\": \"Website\",\t        \"checks\": [\t            {\t                \"url\":\"XXX.XXX.XXX.2\",\t                \"name\":\"NAME2\",\t                \"type\":\"web server\",\t                \"link\":\"https://NAME2\",\t                \"category\":\"Website\"\t            }\t        ]\t    }\t]","payloadType":"jsonata","x":930,"y":1320,"wires":[["4ecea792f621c045"]]},{"id":"4ecea792f621c045","type":"debug","z":"0e2cba8a481c3fb9","name":"Added labels","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1090,"y":1320,"wires":[]}]

If anyone can help with this, it would be much appreciated.

E1cid · 27 March 2023 18:06

Your required output example is impossible as an object can not have two properties called group, or is it supposed to be an array of objects ( [edit] see last example below, as i see the flow inject has correct format)

$$.payload^(category, url){category: {"checks":$}}

Will produce

{
    "IOT": {
        "checks": [
            {
                "url": "XXX.XXX.XXX.14",
                "name": "NAME14",
                "type": "heating",
                "link": "",
                "category": "IOT"
            },
            {
                "url": "XXX.XXX.XXX.15",
                "name": "NAME15",
                "type": "heating",
                "link": "",
                "category": "IOT"
            }
        ]
    },
    "Internet": {
        "checks": [
            {
                "url": "XXX.XXX.XXX.3",
                "name": "NAME3",
                "type": "internet",
                "link": "http://XXX.XXX.XXX.3",
                "category": "Internet"
            },
            {
                "url": "XXX.XXX.XXX.4",
                "name": "NAME4",
                "type": "extender",
                "link": "http://XXX.XXX.XXX.4",
                "category": "Internet"
            }
        ]
    },
    "Network": {
        "checks": {
            "url": "XXX.XXX.XXX.6",
            "name": "NAME6",
            "type": "printer",
            "link": "http://NAME6",
            "category": "Network"
        }
    },
    "Server": {
        "checks": [
            {
                "url": "XXX.XXX.XXX.13",
                "name": "NAME13",
                "type": "server",
                "link": "http://XXX.XXX.XXX.13",
                "category": "Server"
            },
            {
                "url": "XXX.XXX.XXX.5",
                "name": "NAME5",
                "type": "web server",
                "link": "https://XXXXXXXXXXXXX",
                "category": "Server"
            },
            {
                "url": "http://NAME18",
                "name": "NAME18",
                "type": "website",
                "link": "http://18",
                "category": "Server"
            }
        ]
    },
    "Service": {
        "checks": [
            {
                "url": "XXX.XXX.XXX.1",
                "name": "NAME1",
                "type": "service",
                "link": "http://NAME1",
                "category": "Service"
            },
            {
                "url": "XXX.XXX.XXX.10",
                "name": "NAME10",
                "type": "remote",
                "link": "",
                "category": "Service"
            }
        ]
    },
    "Website": {
        "checks": {
            "url": "XXX.XXX.XXX.2",
            "name": "NAME2",
            "type": "web server",
            "link": "https://NAME2",
            "category": "Website"
        }
    }
}

[edit] As url is a string and can be an IP or domain, you will need to convert url ip to a full length ip padding extra zeros to sort correctly in lexicographical order.
e.g

 $$.payload^(
   category,
   $join($split(url, ".").$pad($, -3, "0"), ".")
){category: {"checks": $}}

Or as an array of objects

($$.payload^(
   category,
   $join($split(url, ".").$pad($,-3,"0"), ".")
){category: {"group": $distinct(category), "checks":[$]}}).*

mudwalker · 28 March 2023 08:51

@E1cid Thank you!

Spent some time with this and came up with nothing. I can now move forward!!

I understand the lexicographic order, my data was edited for 'public consumption', but it was the addition of the labels that I was interested in (the original data sorts exactly as desired). I could add a single label, but couldn't see how to add two.

I will have a play and see what I can use to get an 'operational' array. One of these will work I am sure, but I will report back.

mudwalker · 28 March 2023 13:16

@E1cid Thank you, indeed a slightly modified 3rd option gave me what I needed. As in my data the lexicography was OK, I shortened it to this...
($$.payload^(category,url){category: {"group": $distinct(category), "checks":[$]}}).*

What is more, I think I might almost understand it. Just need to work out the (...).*, but I will get there! If not, I will be back!

E1cid · 28 March 2023 13:26

The (...).* returns the values of the object. remove it to see the difference

You haven't really modified it, all you have done is remove the lexi ordering, so server.checks is ordered 13, 5, name18, padding the numbers corrects the order, but does not convert the url property. Your choice though.

system · 11 April 2023 13:27

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

Topic		Replies	Views
Sort node - I'm stuck! General	4	2390	29 December 2020
Extracting data from arrays and concatanating General	6	57	10 October 2024
Modify an array General	3	7711	5 November 2019
Mapping a specific data of an array of properties in JSON General	10	1337	24 April 2021
Change Payload include Array General	2	422	31 July 2018

Rearranging an Array and then adding more labels to a JSON expression

Related topics