More Efficient Way to Parse JSON Results

Hello all. I have a JSON String that can have up to 100 results ('spots'), sourced from an HTTP request. I only really want three fields from each 'spot'. They are 'Mode', 'Frequency', and 'LocationDesc'. I want to do the following:

  1. Parse the JSON file to return results for each 'spot'
  2. Sort all results by Mode and LocationDesc
  3. Output results to simple text table

Currently, I am doing this with multiple functions, perhaps there is a way to do this more efficiently? Below is my current flow.

[
    {
        "id": "d2f47fbaea626814",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "47821693ff173466",
        "type": "inject",
        "z": "d2f47fbaea626814",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 360,
        "y": 120,
        "wires": [
            [
                "c7e824482ad27587"
            ]
        ]
    },
    {
        "id": "c7e824482ad27587",
        "type": "http request",
        "z": "d2f47fbaea626814",
        "name": "",
        "method": "GET",
        "ret": "txt",
        "paytoqs": "ignore",
        "url": "https://api.pota.app/spot/activator",
        "tls": "",
        "persist": false,
        "proxy": "",
        "authType": "",
        "senderr": false,
        "x": 530,
        "y": 120,
        "wires": [
            [
                "793ce172cd2b36e6"
            ]
        ]
    },
    {
        "id": "793ce172cd2b36e6",
        "type": "json",
        "z": "d2f47fbaea626814",
        "name": "",
        "property": "payload",
        "action": "obj",
        "pretty": false,
        "x": 750,
        "y": 120,
        "wires": [
            [
                "be646be526df49b9",
                "fc15ce3272e9c64e"
            ]
        ]
    },
    {
        "id": "552a73b1bea7189e",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[0].locationDesc + ' - '+ msg.payload[0].mode + ' - '+ msg.payload[0].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 120,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "a0b7b6ef93ab1f6d",
        "type": "debug",
        "z": "d2f47fbaea626814",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1330,
        "y": 300,
        "wires": []
    },
    {
        "id": "e2bd2a226ab6107a",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload1.locationDesc + ' - '+ msg.payload1.mode + ' - '+ msg.payload1.frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 180,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "664cd814331e3d5b",
        "type": "join",
        "z": "d2f47fbaea626814",
        "name": "",
        "mode": "custom",
        "build": "string",
        "property": "payload",
        "propertyType": "msg",
        "key": "topic",
        "joiner": "\n",
        "joinerType": "str",
        "accumulate": false,
        "timeout": "",
        "count": "10",
        "reduceRight": false,
        "reduceExp": "",
        "reduceInit": "",
        "reduceInitType": "",
        "reduceFixup": "",
        "x": 1170,
        "y": 300,
        "wires": [
            [
                "a0b7b6ef93ab1f6d"
            ]
        ]
    },
    {
        "id": "be646be526df49b9",
        "type": "sort",
        "z": "d2f47fbaea626814",
        "name": "",
        "order": "ascending",
        "as_num": false,
        "target": "payload",
        "targetType": "msg",
        "msgKey": "locationDesc",
        "msgKeyType": "jsonata",
        "seqKey": "payload",
        "seqKeyType": "msg",
        "x": 750,
        "y": 360,
        "wires": [
            [
                "552a73b1bea7189e",
                "e2bd2a226ab6107a",
                "cb0f561178b1978f",
                "2c7ca67d38934d26",
                "0441dd6d7221b639",
                "2dd3946f0b08f807",
                "cbde257849fb18a5",
                "9277b6d395598bba",
                "851804d09a7d8b5a",
                "2d9c768cb444ab9f"
            ]
        ]
    },
    {
        "id": "cb0f561178b1978f",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload2.locationDesc + ' - '+ msg.payload2.mode + ' - '+ msg.payload2.frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 240,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "2c7ca67d38934d26",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[3].locationDesc + ' - '+ msg.payload[3].mode + ' - '+ msg.payload[3].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 300,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "0441dd6d7221b639",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[4].locationDesc + ' - '+ msg.payload[4].mode + ' - '+ msg.payload[4].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 360,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "2dd3946f0b08f807",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[5].locationDesc + ' - '+ msg.payload[5].mode + ' - '+ msg.payload[5].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 420,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "cbde257849fb18a5",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[6].locationDesc + ' - '+ msg.payload[6].mode + ' - '+ msg.payload[6].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 480,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "9277b6d395598bba",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[7].locationDesc + ' - '+ msg.payload[7].mode + ' - '+ msg.payload[7].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 540,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "851804d09a7d8b5a",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[8].locationDesc + ' - '+ msg.payload[8].mode + ' - '+ msg.payload[8].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 600,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "2d9c768cb444ab9f",
        "type": "function",
        "z": "d2f47fbaea626814",
        "name": "",
        "func": "msg.payload = msg.payload[9].locationDesc + ' - '+ msg.payload[9].mode + ' - '+ msg.payload[9].frequency\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 980,
        "y": 660,
        "wires": [
            [
                "664cd814331e3d5b"
            ]
        ]
    },
    {
        "id": "fc15ce3272e9c64e",
        "type": "debug",
        "z": "d2f47fbaea626814",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 510,
        "y": 240,
        "wires": []
    }
]

Try using array.reduce().
eg

[{"id":"47821693ff173466","type":"inject","z":"d2f47fbaea626814","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":360,"y":120,"wires":[["c7e824482ad27587"]]},{"id":"c7e824482ad27587","type":"http request","z":"d2f47fbaea626814","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.pota.app/spot/activator","tls":"","persist":false,"proxy":"","authType":"","x":530,"y":120,"wires":[["be646be526df49b9","fc15ce3272e9c64e"]]},{"id":"be646be526df49b9","type":"sort","z":"d2f47fbaea626814","name":"","order":"ascending","as_num":false,"target":"payload","targetType":"msg","msgKey":"locationDesc","msgKeyType":"jsonata","seqKey":"payload","seqKeyType":"msg","x":770,"y":120,"wires":[["552a73b1bea7189e"]]},{"id":"fc15ce3272e9c64e","type":"debug","z":"d2f47fbaea626814","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":240,"wires":[]},{"id":"552a73b1bea7189e","type":"function","z":"d2f47fbaea626814","name":"","func":"msg.payload = msg.payload.reduce((acc, obj) => {\n    return acc += obj.locationDesc + \n    ' - '+ obj.mode + \n    ' - '+ obj.frequency + \n    '\\n'\n}, \"\")\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":980,"y":120,"wires":[["a0b7b6ef93ab1f6d"]]},{"id":"a0b7b6ef93ab1f6d","type":"debug","z":"d2f47fbaea626814","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1230,"y":120,"wires":[]}]
msg.payload = msg.payload.reduce((acc, obj) => {
    return acc += obj.locationDesc + 
    ' - '+ obj.mode + 
    ' - '+ obj.frequency + 
    '\n'
}, "")
return msg;

Wow, that is exactly what I was looking for. One more question.

How can I filter results to only look for LocationDesc = 'US-AK' for example?

try

msg.payload = msg.payload.reduce((acc, obj) => {
    if (obj.locationDesc === "US-FL"){
        acc += obj.locationDesc + 
        ' - '+ obj.mode + 
        ' - '+ obj.frequency + 
        '\n'
    }
return acc; 
}, "")
return msg;

[edit] remove multiple returns and else, to make more concise.

1 Like

Bless you. Thank you so much. That works exactly as I wanted it to.

I do like this solution using the Array.reduce(...) method for building the list of output strings -- but I can never remember how that syntax works.

So FWIW, and for my own sanity, I tend to use Array.filter(...) to find out which elements needs processing first, then map each of those records to an output string, and finally Array.join(...) them to build the output -- like so:

var records = msg.payload.filter(obj => obj.locationDesc === "US-AK");
var strings = records.map(rec => `${rec.locationDesc} - ${rec.mode} - ${rec.frequency}`);
msg.payload = strings.join("\n");
return msg;

Incidentally, here is another method you can use -- a change node with this JSONata expression:

payload[locationDesc = "US-AK"].(
    locationDesc & ' - ' & mode & ' - ' & frequency
)~>$join("\n")

(although I have not tested that expression)

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