JSONATA Array Filtering

sonificator · 24 October 2024 19:05

Hi there! I am downloading a fairly large array from an API and looking for the most efficient way to filter it (obviously would be preferable to filter it in the API call, but not possible). Seems that a change node with jsonata would be the best approach; jsonata is always so tricky, any help would be appreciated.

The data is coming in the following format; I want to filter out everything that doesn't meet the criteria:

(change > change_flow) AND ( price > price_min_flow) AND ( price < price_max_flow), etc.
where change_flow, etc is stored in flow context

Blockquote

{
    "status": 200,
    "data": {
        "data": {
            "AAPL": {
                "volume": 18457363,
                "price": 230.42,
                "change": -0.15,
                "float": 15179469955,
                "relativeVolume": 36.30859039199602,
                "shortRatio": 2.32,
                "shortFloat": 0.93377,
                "daysGap": -0.34,
                "averageVolume": 39185859.6,
                "rsi": 52.096
            },
            "NVDA": {
                "volume": 130080525,
                "price": 139.7328,
                "change": 0.12,
                "float": 23541535433,
                "relativeVolume": 40.00929295738587,
                "shortRatio": 0.8,
                "shortFloat": 1.06658,
                "daysGap": 0.9,
                "averageVolume": 254269218.6,
                "rsi": 62.832
            }
        }
    }
}

Steve-Mcl · 24 October 2024 19:24

In that case, I would not recommend JSONata...

Look closely at the image in this demo: https://flows.nodered.org/flow/63f29373e826a9fd4943d679d43e725f

Pure JS 4ms, JSONata 1222ms

sonificator · 24 October 2024 19:37

Hmm, interesting - Ok. Could you assist with the optimal JS for this. Thank you.

E1cid · 24 October 2024 19:59

But if you use the native JSONata sort function rather than the orderby operator they are very similar

[{"id":"6dadca5f3f264609","type":"subflow","name":"flow-timer (2)","info":"","category":"","in":[{"x":80,"y":100,"wires":[{"id":"c41c06b1a334d9a0"}]}],"out":[{"x":440,"y":100,"wires":[{"id":"c41c06b1a334d9a0","port":0}]}],"env":[{"name":"name","type":"str","value":"measure","ui":{"icon":"font-awesome/fa-tag","label":{"en-US":"Timer Name"},"type":"input","opts":{"types":["str","env"]}}},{"name":"operation","type":"str","value":"start","ui":{"icon":"font-awesome/fa-cog","label":{"en-US":"Operation"},"type":"select","opts":{"opts":[{"l":{"en-US":"start"},"v":"start"},{"l":{"en-US":"stop"},"v":"stop"},{"l":{"en-US":"msg.topic"},"v":"msg.topic"},{"l":{"en-US":"msg.operation"},"v":"msg.operation"},{"l":{"en-US":"msg.payload"},"v":"msg.payload"}]}}}],"meta":{"module":"node-red-contrib-flow-performance","type":"flow-performance","version":"1.0.1","author":"Steve-Mcl","desc":"Inline flow performance measure node","keywords":"node-red performance","license":"MIT"},"color":"#DAEAAA","icon":"node-red/timer.svg","status":{"x":280,"y":160,"wires":[{"id":"c41c06b1a334d9a0","port":1}]}},{"id":"c41c06b1a334d9a0","type":"function","z":"6dadca5f3f264609","name":"do operation","func":"// @ts-ignore\nvar name = msg.perfName || env.get(\"name\");\n// @ts-ignore\nvar operation = msg.perfOperation || env.get(\"operation\");\nvar measures = global.get(\"flow_timers\") || {};\nvar measure = measures[name] || {};\n\nfunction doOp(measure, op){\n    if(operation === \"start\"){\n        measure.start = Date.now();//change to process.hrtime\n        measure.stop = null;\n        measure.durationMs = null;\n    } else if(operation === \"stop\") {\n        measure.stop = Date.now();//change to process.hrtime\n        measure.durationMs = measure.start ? measure.stop - measure.start : null;\n        msg._performance = measure;\n    }\n}\n\n\nif(operation === \"start\"){\n    doOp(measure, operation);\n} else if(operation === \"stop\") {\n    doOp(measure, operation);\n    node.send([null, { payload: { text: name + \": \" + measure.durationMs + \"ms\" }}]);\n} else if(operation === \"msg.topic\") {\n    operation = msg.topic;\n    doOp(measure, operation);\n} else if(operation === \"msg.operation\") {\n    operation = msg.operation;\n    doOp(measure, operation);\n} else if(operation === \"msg.payload\") {\n    operation = msg.payload;\n    doOp(measure, operation);\n} else {\n    return [msg, null];\n}\nmeasures[name] = measure;\nglobal.set(\"flow_timers\", measures);\n\nreturn [msg, null];","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":250,"y":100,"wires":[[],[]]},{"id":"b14991b018ecaded","type":"inject","z":"d1395164b4eec73e","name":"function","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"function","payload":"function","payloadType":"str","x":220,"y":7460,"wires":[["0c44776cf86dfe80"]]},{"id":"0c44776cf86dfe80","type":"function","z":"d1395164b4eec73e","name":"Generate a dataset","func":"var data = [];\n\nfunction randomFloat(min, max) { \n    return Math.random() * (max - min) + min; \n}\nfunction randomInt(min, max) {\n  return Math.floor(Math.random() * (max - min + 1) + min);\n}\nfunction randomBool() {\n  return Math.random() >= 0.5;\n}\n\nvar makeRow = function() {\n    return {\n        \"name\": \"Item \" + (data.length+1),\n        \"gender\": randomBool() == true ? \"male\" : \"female\",\n        \"age\": randomInt(18,75),\n        \"height\": randomInt(18,75),\n        \"lat\": randomFloat(51.0,54.9),\n        \"lon\": randomFloat(-1.8, 1.8),\n        \"friendCount\": randomInt(30,300),\n        \"employmentDuration\": randomFloat(1.0,35.9),\n        \"accessLevel\": randomInt(1,9),\n        \"canEdit\": randomBool(),\n        \"canWrite\": randomBool(),\n        \"dob\": `${randomInt(1930,1999)}-${randomInt(1,12)}-${randomInt(1,28)}`\n    }\n}\n\nfor(let i = 0; i < 1000; i++) {\n    data.push(makeRow());\n}\n\nmsg.payload = data;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":436,"y":7480,"wires":[["224e9510086972dd","fbf68a0d624a331b"]]},{"id":"481f1a47fcdadc54","type":"inject","z":"d1395164b4eec73e","name":"JSONata","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"JSONata","payload":"JSONata","payloadType":"str","x":210,"y":7500,"wires":[["0c44776cf86dfe80"]]},{"id":"224e9510086972dd","type":"debug","z":"d1395164b4eec73e","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":720,"y":7480,"wires":[]},{"id":"fbf68a0d624a331b","type":"switch","z":"d1395164b4eec73e","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"function","vt":"str"},{"t":"eq","v":"JSONata","vt":"str"}],"checkall":"true","repair":false,"outputs":2,"x":380,"y":7600,"wires":[["242678a8f687d1cb"],["999c06c1577c18fa"]]},{"id":"242678a8f687d1cb","type":"subflow:6dadca5f3f264609","z":"d1395164b4eec73e","name":"","env":[{"name":"name","value":"function","type":"str"}],"x":550,"y":7560,"wires":[["d9e9fe92eb55566d"]]},{"id":"999c06c1577c18fa","type":"subflow:6dadca5f3f264609","z":"d1395164b4eec73e","name":"","env":[{"name":"name","value":"jsonata","type":"str"}],"x":550,"y":7640,"wires":[["a153aa613af7c525"]]},{"id":"d9e9fe92eb55566d","type":"function","z":"d1395164b4eec73e","name":"Native JS Sort","func":"msg.payload.sort((a,b) => {\n    if(a.age < b.age){\n        return -1;\n    }else if(a.age > b.age){\n        return 1;\n    }else{\n        return 0;\n    }\n})\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":730,"y":7560,"wires":[["7bc8f59931d9a0ab"]]},{"id":"a153aa613af7c525","type":"change","z":"d1395164b4eec73e","name":"JSONata sort","rules":[{"t":"set","p":"payload","pt":"msg","to":"$sort($$.payloadt, function($l, $r) {\t  $l.age > $r.age\t})","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":730,"y":7640,"wires":[["535c1684b0a13567"]]},{"id":"7bc8f59931d9a0ab","type":"subflow:6dadca5f3f264609","z":"d1395164b4eec73e","name":"","env":[{"name":"name","value":"function","type":"str"},{"name":"operation","value":"stop","type":"str"}],"x":910,"y":7560,"wires":[["390bafb97e9ba2d3"]]},{"id":"535c1684b0a13567","type":"subflow:6dadca5f3f264609","z":"d1395164b4eec73e","name":"","env":[{"name":"name","value":"jsonata","type":"str"},{"name":"operation","value":"stop","type":"str"}],"x":910,"y":7640,"wires":[["352c6a71cbe6a1a8"]]},{"id":"390bafb97e9ba2d3","type":"debug","z":"d1395164b4eec73e","name":"function","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1070,"y":7560,"wires":[]},{"id":"352c6a71cbe6a1a8","type":"debug","z":"d1395164b4eec73e","name":"JSONata","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1070,"y":7640,"wires":[]}]

Steve-Mcl · 24 October 2024 20:11

Good to know.

Could you help the OP with a query for their conundrum? (I am afk)

E1cid · 24 October 2024 20:23

Why of couse, something like this

[{"id":"29b7a996ff9053cc","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"change","pt":"msg","to":"0","tot":"num"},{"t":"set","p":"price_min","pt":"msg","to":"100","tot":"num"},{"t":"set","p":"price_max","pt":"msg","to":"300","tot":"num"},{"t":"set","p":"payload","pt":"msg","to":"$sift($$.payload.data.data, function($v,$k)\t{$v.change > $$.change and $v.price > $$.price_min and $v.price < $$.price_max}\t)\t\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":380,"y":7840,"wires":[["4e5340ffce155365"]]},{"id":"4ad42215ec3280f3","type":"inject","z":"d1395164b4eec73e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{     \"status\": 200,     \"data\": {         \"data\": {             \"AAPL\": {                 \"volume\": 18457363,                 \"price\": 230.42,                 \"change\": -0.15,                 \"float\": 15179469955,                 \"relativeVolume\": 36.30859039199602,                 \"shortRatio\": 2.32,                 \"shortFloat\": 0.93377,                 \"daysGap\": -0.34,                 \"averageVolume\": 39185859.6,                 \"rsi\": 52.096             },             \"NVDA\": {                 \"volume\": 130080525,                 \"price\": 139.7328,                 \"change\": 0.12,                 \"float\": 23541535433,                 \"relativeVolume\": 40.00929295738587,                 \"shortRatio\": 0.8,                 \"shortFloat\": 1.06658,                 \"daysGap\": 0.9,                 \"averageVolume\": 254269218.6,                 \"rsi\": 62.832             }         }     } }","payloadType":"json","x":190,"y":7840,"wires":[["29b7a996ff9053cc"]]}]

Which will return a object not an array, as the OG example was an object and not an array.

$sift($$.payload.data.data, function($v,$k)
{$v.change > $$.change and $v.price > $$.price_min and $v.price < $$.price_max}
)

You can call the flow var direct with $flowContext("varname") rather than $$.chnage etc.

sonificator · 24 October 2024 20:55

Could show me how to apply your jsonata approach to my example please ?

Colin · 24 October 2024 21:14

I assume that msg.payload is an array of objects of the format you have shown.

Do you mean that has to apply for both data.data.AAPL and data.data.NVDA?

sonificator · 24 October 2024 21:20

Yes, that's how the data is coming in from the API - it's several thousand objects (node-red even freezes slightly before you can view it, which I've never experienced). So I need to cycle through all of them to filter out a handful based on value conditions of certain fields (example I gave).

E1cid · 24 October 2024 21:53

I thought i just did, any property that does not pass the conditional is removed.

Otherwise explain fully with input, output examples, and flow var values.

sonificator · 24 October 2024 22:02

Sorry, I missed the flow - thanks so much!

sonificator · 24 October 2024 22:35

Another quick question please. When storing the parameter values using the change node with an inject node that just has a timestamp payload I get the following error: "Cannot set property of non-object type: payload.change" ... it works fine if the inject node has a proper json payload.

E1cid · 24 October 2024 22:40

As the inject sets the payload to a timestamp (number), the change can not add payload.change to payload , as payload is not an object. set the inject payload to an empty object {}.

Also note that your data is in payload which may cause issues, unless this is a separte flow.

sonificator · 24 October 2024 22:47

Thanks. I am getting stored values from context with jsonata and also sending these values to a different node in the flow in payload.

sonificator · 25 October 2024 04:28

One more challenge came up. After the jsonata filtering I am getting output in the following format:

{"ADT":{"volume":29079654,"price":8.14,"change":17.63,"float":277767777,"relativeVolume":669.3863072501292,"shortRatio":5.15,"shortFloat":8.47932,"daysGap":6.65,"averageVolume":5645560.65,"rsi":73.641},"QS":{"volume":68074619,"price":6.5,"change":25.48,"float":371136076,"relativeVolume":1183.5067008672897,"shortRatio":11.73,"shortFloat":15.03023,"daysGap":18.34,"averageVolume":8565159.55,"rsi":67.165},"PCT":{"volume":14397838,"price":14.3,"change":29.53,"float":89476163,"relativeVolume":597.0198431094809,"shortRatio":12.8,"shortFloat":50.04126,"daysGap":1,"averageVolume":2547167.6,"rsi":82.869},"LC":{"volume":8587131,"price":13.82,"change":10.91,"float":108788732,"relativeVolume":584.1824156088004,"shortRatio":3.04,"shortFloat":3.32353,"daysGap":18.14,"averageVolume":1782398.6,"rsi":70.125},"IONR":{"volume":269585,"price":8.0965,"change":15.5,"float":1385027955,"relativeVolume":1879.6890252405522,"shortRatio":1.43,"shortFloat":0.00205,"daysGap":-1.71,"averageVolume":31875.5,"rsi":73.458},"NVA":{"volume":216922,"price":10.78,"change":21.81,"float":189571070,"relativeVolume":560.5219638242894,"daysGap":10.17,"averageVolume":79654.3,"rsi":85.016}}

I am not sure how to access the stock name (e.g. ADT) ; the path is payload.ADT ... and when I split the array I lose that data point. Is it possible to modify the output so that it's the simplest array, where payload.ADT is set to a variable (at the same level as the others): name: 'ADT'. Thanks again.

E1cid · 25 October 2024 06:57

As said above there are no arrays these are objects.

You would need to loop through each object check if it passes conditional then return a merged object. $each will return an array of object from an object.

[$each(
    $$.payload.data.data, 
    function($v,$k){
        $v.change > $$.change and $v.price > $$.price_min and $v.price < $$.price_max ?
        $merge([{"stock":$k}, $v])
    }
)]

You need to start providing clear input and clear output examples, so we have all the correct information to provide a clear example first time. Your initial example failed to provide a clear output.

sonificator · 25 October 2024 08:00

Not sure what you mean by my initial example failed to provide a clear output. Let me try again, (and there is an additional problem of data being "lost" somewhere in the process). The data is coming in the format that I initially provided, this is what it looks like:

{
    "status": 200,
    "data": {
        "data": {
            "AAPL": {
                "volume": 18457363,
                "price": 230.42,
                "change": -0.15,
                "float": 15179469955,
                "relativeVolume": 36.30859039199602,
                "shortRatio": 2.32,
                "shortFloat": 0.93377,
                "daysGap": -0.34,
                "averageVolume": 39185859.6,
                "rsi": 52.096
            },
            "NVDA": {
                "volume": 130080525,
                "price": 139.7328,
                "change": 0.12,
                "float": 23541535433,
                "relativeVolume": 40.00929295738587,
                "shortRatio": 0.8,
                "shortFloat": 1.06658,
                "daysGap": 0.9,
                "averageVolume": 254269218.6,
                "rsi": 62.832
            }
        }
    }
}

There are 5400+ records. The jsonata filter seemed to work, but I was getting significantly fewer records in the output than expected (records that should have been there were missing). I copied the data from the debug, got it into excel, and confirmed that those records existed.

So I was able to grab the entire data set of 5400 while it this format in the debug

When I tried converted the data (prior to filtering) into a format the I wanted for my output, I noticed it got truncated at 1000th record (copying data from the debug got me 1000 instead of 5400); not sure if this is is a limitation of the debug console or an actual "loss" (that somehow explained why expected records were missing). The only other explanation is that the jsonata filter is not working as expected.

Here is the output format that I need. The missing data is a mystery.

[{"volume":29079654,"price":8.14,"change":17.63,"float":277767777,"relativeVolume":669.3863072501292,"shortRatio":5.15,"shortFloat":8.47932,"daysGap":6.65,"name":"ADT"},{"volume":68074619,"price":6.5,"change":25.48,"float":371136076,"relativeVolume":1183.5067008672897,"shortRatio":11.73,"shortFloat":15.03023,"daysGap":18.34,"name":"QS"},{"volume":14397838,"price":14.3,"change":29.53,"float":89476163,"relativeVolume":597.0198431094809,"shortRatio":12.8,"shortFloat":50.04126,"daysGap":1,"name":"PCT"},{"volume":8587131,"price":13.82,"change":10.91,"float":108788732,"relativeVolume":584.1824156088004,"shortRatio":3.04,"shortFloat":3.32353,"daysGap":18.14,"name":"LC"},{"volume":269585,"price":8.0965,"change":15.5,"float":1385027955,"relativeVolume":1879.6890252405522,"shortRatio":1.43,"shortFloat":0.00205,"daysGap":-1.71,"name":"IONR"},{"volume":216922,"price":10.78,"change":21.81,"float":189571070,"relativeVolume":560.5219638242894,"daysGap":10.17,"name":"NVA"}]

Steve-Mcl · 25 October 2024 08:21

Can you clarify the input data. Is it an array or is it nested objects.
Your topic states "Array filtering". Your first post shows data is nested objects and now you post a screenshot with actual array containing 1000+ items and want an output in array format.

Assumption time

Assuming the input data is actually a large array (1000+ elements) of objects. Also assuming msg.payload contains this array (not nested under a property) & lastly, assuming you simply want the same format output: then a simple function is all it takes:

const min = msg.min ?? 1.0 // use msg.min or default to 1.0
const max= msg.max ?? 100.0 // use msg.max or default to 100.0
msg.payload = msg.payload.filter(el => el.price >= min && el.price <= max)
return msg

Steve-Mcl · 25 October 2024 08:23

The debug output is limited to keep the frontend client responsive.

If you set the debug to send results to console, you will see all of the data

Alternatively, send the result to a file node and inspect the file content.

E1cid · 25 October 2024 10:24

I mean you supplied an input, but no example of what the output should be like and no example of the conditional values that would create that output. So i had to assume you wanted an object as the OG was an object.

The debug is a display and will truncate to save bandwidth so as to not slow the browser. All the values will be there in the server though. You can send the debug to console to see full object if you wish.

Topic		Replies	Views
Puzzling JSONATA problem in Change Node General	2	205	13 January 2023
Jsonata: return value from context array General	2	4597	5 December 2018
Unable to filter arrays using JSON path syntax in function node Feature Requests function-node	12	706	16 May 2022
Data Formatting Dashboard	6	1135	28 January 2022
Store and check for changes in JSON General	4	605	10 January 2022

JSONATA Array Filtering

Assumption time

Related topics