Weird behaviour when processing an array of arrays having null values

[janvda]

[quote="janvda, post:18, topic:28615, full:true"]

@Colin

@janvda

the JSONata implementation appears to be consistent with that
[/quote]

True, but that does not mean that the implementation is performing according to the spec. An alternative implementation might convert into valid JSON first, so the result would be as you originally expected. Presumably there is a jsonata spec, but again it would need someone who knows jsonata well to understand it and see whether the implementation here is according to the spec.

I have raised following issue for that:

• https://github.com/jsonata-js/jsonata/issues/458

I have got feedback for the issue.

In summary: JSONata expects JSON as input and as a sparse array is not proper JSON so its behaviour is undefined - which all makes sense to me.

@TotallyInformation

I would like to suggest that is a failing of the underlying library. Of course, also compounded by the other inconsistencies you've found on the JSONata/Node-RED side.

I am pretty happy with the excel node ( I was actually amazed that it worked so well). Note that returning an array of objects instead of an array of arrays would also complicate my processing (jsonata is very powerful when dealing with arrays).

For me I am happy to live with the work around =

• filling the gaps in sparse arrays by running it through 2 json nodes before feeding it into a change node with jsonata expressions.

[TotallyInformation]

@janvda

For me I am happy to live with the work around

OK, all of this is predicated on not having too large a workbook anyway I guess. Some of the workbooks I get to handle can be hard to process in Excel itself let alone trying to manhandle (sorry, personhandle) it into node or running the data back and forth into and out of JSON

That is where PowerShell is actually quite useful because of its ability to stream and pipe data more directly.