Unable to filter arrays using JSON path syntax in function node

Core Development Feature Requests
1

Similar to this use case, I need to filter an array for a specific property value in the array.

msg.payload.store.book[?(@.price<10)]
msg.payload.$.phoneNumbers[?(@.type=='iPhone')].number

Where 10 or iPhone comes from a flow variable.

The function node in Javascript is not allowing.

Perhaps a GitHub issue is warranted to include a library?

2

JSONpath is not something that is built-in to node-red.
You can use JSONata in a change node.

Function nodes are for JavaScript.
i.e. you can use regular JavaScript array .filter

1 Like
3

Why do you want to use jsonata in a function node? It will surely be more efficient to use javascript directly.

4

I am already able to use subsets of JSON path.
msg.payload.store.book[0].title is a filtered path.
We need support for more expressive filters as shown, and stuff the results into flow variables.
I have at least ten to filter.
Are you suggesting ten JSONata or template nodes?
KISS principle applies, and that recommendation is not.

5

msg.payload.store.book[0].title is valid JavaScript
Just because it looks like JSONpath doesnt mean the function node is supporting a subset of JSONpath.

So use JavaScript in a function.

If you must (though I don't understand why anyone would go to the efforts of learning JSONpath or JSONata over JavaScript) you can probably import JSONata or JSONpath inside the function node "setup" tab

KISS in low code is visual flow.
JSONata and JSONpath are anything but KISS.

6

Maybe this would make more sense if you show us some sample data sets & what you would like them to look like & we can propose a "flow" way that is acceptable to you?

For example, if you have a real world example (perhaps via a public API where we can access the data) and explain your overall goal?

7

Well everyone has an opinion and clearly we disagree.
If it was truly low-code there would be nodes for visual data transformations like DataWeave generating code like JSONata or SQL.
Data transformation in Javascript is archaic.

8

Sure, but your question was about using JSONpath inside a function & suggested you wanted to keep it KISS (not me)

I only pointed out in a low code environment, using the built in nodes is (generally) the KISS method.

So again, if you have a real world example, we can point you in the right direction. for example, it may be a case of "get data" (request node) --> move to accessible msg prop (change node) --> split array (split node) --> do JSONata in change node --> rejoin data (join node) --> etc

But without a real world example, we are speculating.

9

I am on my phone at the moment at jury duty, so the example pasted is the closest I could find. But envision a MariaDB SQL response with multiple parties with array properties; id, partyType, name, accountIdentifier, etc.
If not found, then I have to prepare the SQL from the received JSON purchaseOrder payload.

The approach in your reply feels like the large multi node concern I raised where each node replaces a line of Javascript. Even with proper naming, one may need to open each node in an unwieldy flow to understand what is going on.

So there is a balance...aka how 'low' should you go?

Some integration tools have found that balance, and from someone that has been using these tools for over 30 years (easily as many tools as years), Node-RED has not found that balance and I feel my question is trying to get it there.

The import library approach is my next step.

Oh...while I am at it, other tools have provided multi-axis in memory views, say off msg object. Much more robust than get/set on flow parameters etc. Just saying.

10

I appreciate you are on your phone, so not necessarily in a position to respond fully, but I would be interested to hear more about this. I'm not sure what you mean by "multi-axis in memory view". I would be interested to learn more so we can take this feedback on board properly.

In terms of transforming data, as has been suggested, you can use JSONata in a Change node to transform data in a single node. If that is not sufficient for your needs, it would be good to understand better why not.

It is certainly possible to add additional modules to the Function already - enabling the functionExternalModules option in your settings file and you'll be able to add whatever additional modules you need.

11

All it takes if for someone to write it :grinning:

12

@knolleary
Perhaps the multi-axis topic is 'off-topic', but let me try to explain and a graphic is probably in order at some point.
I will pick one open source tool XAware, that I believe is no longer in existence, that supported this. And there is a very good chance that you may find this useful or you may find the current approach is just fine. And I will use the Node-RED msg object as the root.
The use case is typically around many-to-many transformations (source-to-target) transformations where data 'enrichment' is required, and there are inter-relationships between multiple targets, especially when a relational database is the target which multiple inter-related tables via foreign key relationships are part of the transaction. Enrichment example could include an address validation service, or a cross-reference table for UN REC 20 units of measure, item identifiers, UN REC 21 package codes, and so forth.

The intent is that a purchaseOrder JSON is received over HTTP as msg.payload. I would be able to configure the receipt of that to, instead of managing msg.payload all the time, it is configured to be set as an 'axis' of msg.purchaseOrder. From that point on, I can forever reference that axis based on its path of msg.purchaseOrder without managing via set and get operations. Next, based on msg.purchaseOrder.purchaseOrderLine[i].item.identifier I can call the XREF REST API and the results are stored in msg.xref.itemId array object. It too is auto-set to that respective axis WITHOUT impacting msg.purchaseOrder axis. And so forth with the respective target table objects (I am thinking ORM here); e.g., msg.target.table.PurchaseOrderHeader or what ever axis I define. Again, no set and get. Every axis is referenceable at any point in time.

I DO appreciate the local, flow and global scoping aspects, but that is only a property of the axis. I hope that helps. Again that was XAware (an XML centric tool using XPATH), but other tools like the old Mercator tool (90s) employed similar object references but often in proprietary approaches.

Back to JSONPath, the title of this subject should state Unable not Usable. Phone issues. I do recommend a hybrid Javascript/ JSONPath approach, to find that balance. I am just learning JSONata, and I am not sure I can create dynamic SQL Statements and pass them to MariaDB like I want with JSONata. I will experiment further with the functionsExternalModules approach. Thank you .

13

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