# Weird behaviour when processing an array of arrays having null values

**URL:** <https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615>\
**Category:** Core Development\
**Created:** [19 June 2020 14:26 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615 "2020-06-19T14:26:06Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [19 June 2020 14:26 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/1 "2020-06-19T14:26:06Z")

</div>

I am using `node-red-contrib-spreadsheet-in` to read an excel file into node-red.  
This produces a json array of arrays having null values for those cells that are not populated.

E.g. here below the debug output of the `sheet to json` node for a simple excel sheet  
So you can see that array[1][1] = null.

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/b/ab27928e7bdceca21e5c512a25f918e8de051fce.png)

FYI the simple excel sheet used as input :  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/0/b034b6b12d057c7a7c8c69b93500db8156a4353c.png)

So far so good, but when I pass this through a change node with following JSONata expression:

```auto
payload.{
   "row" : $
}

```

then the "null" cell gets removed as you can see in below output:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/2/92cee584ac8cb7c5a58d19b630108d52cd988299.png)

... but when I test this with same payload (copy pasted from debug window) in the JSONata expression editor then I get the expected result with the null value:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/1/6102b0ddce209a4b6c6728808e952f9c9e7ade44.png)

As workaround - i have put 2 `json` nodes in between (to convert it to a string and back).

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/1/b10ad6c6757c34cc7e0ace2fd990e61328c695c5.png)

with this workaround I am getting the expected output:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/d/7d0b9e190bba5c07db74470d35ad31525c299aea.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 June 2020 15:09 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/2 "2020-06-19T15:09:25Z")

</div>

I believe javascript uses sparse arrays where an element can be completely missing. I wonder whether your input array has the null element completely missing and after converting to JSON and back the element is present with a null value. Or vice versa.  
I believe you can test that using `for in` to iterate the array. If the element is completely missing then it will not be selected in the loop, if it is present then it will.  
If my guess is correct then whether jsonata should give different answers I don't know. Probably not. Are you using the latest node-red? I believe there was a jsonata upgrade a little time ago that fixed a number of bugs.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [19 June 2020 15:53 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/3 "2020-06-19T15:53:09Z")

</div>

Thanks for the response.

I am using node-red version v1.0.4

based on your input I have tested it with below simple java script program:

```auto
row0_for_of_count = 0;
row0_for_in_count = 0;
row1_for_of_count = 0;
row1_for_in_count = 0;

for (var i of msg.payload[0]){
  row0_for_of_count++;
}

for (i of msg.payload[1]){
  row1_for_of_count++;
}

for (i in msg.payload[0]){
  row0_for_in_count++;
}

for (i in msg.payload[1]){
  row1_for_in_count++;
}

msg.row0_for_of_count = row0_for_of_count;
msg.row0_for_in_count = row0_for_in_count;
msg.row1_for_of_count = row1_for_of_count;
msg.row1_for_in_count = row1_for_in_count;

return msg;

```

... and indeed the "for in" loop for the 2nd row only sees 2 properties.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/e/3e2355187fbfbb4729a5fe8c9c21a1052493bd97.png)

... I have also applied the same script after passing the payload through 2 `json` nodes (to serialize it to a string and back) and the "for in" loop for the 2nd row sees now 3 properties:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/9/a9d4f5e03489cedcb5478d79721587308c388ebd.png)

So that most likely explains the root cause of the issue.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 June 2020 16:27 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/4 "2020-06-19T16:27:13Z")

</div>

I am feeling rather pleased with myself now for that inspired guesswork (the weather is miserable here today so thanks for cheering me up 🙂 )  
I don't know whether that is a bug in the jsonata code or not. It is certainly confusing. Are you using a recent version of node-red?

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [19 June 2020 16:58 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/5 "2020-06-19T16:58:41Z")

</div>

node-red version 1.04:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/8/b88ba3f6ca1c72bdb2b1c51063cdc1a317b45162.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 June 2020 17:24 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/6 "2020-06-19T17:24:15Z")

</div>

I think this needs a jsonata expert.

---

<div class="post-metadata">

**Author:** ![Paul-Reed](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/paul-reed/32/66906_2.png) [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)\
**Post date:** [19 June 2020 20:19 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/7 "2020-06-19T20:19:42Z")

</div>

I thought that person was you Colin 😉

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [19 June 2020 20:37 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/8 "2020-06-19T20:37:56Z")

</div>

Absolutely not. Once it gets above `payload=0 ? true : false` then I am lost.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [19 June 2020 22:33 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/9 "2020-06-19T22:33:27Z")

</div>

> [@Colin](#):
>
> I think this needs a jsonata expert.

Not sure if it is a JSONata issue.

It seems that the debug tab represents "sparse" arrays in exactly the same way as arrays with null values.  
I also would expect that serialization and deserialisation with `json` node would return exactly the same payload but this is not the case when the array is a "sparse" array.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 June 2020 06:10 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/10 "2020-06-20T06:10:06Z")

</div>

There are TWO questions then:

1. Should jsonata behave differently for sparse and full arrays?
2. Is the JSON node correctly converting a sparse array to JSON? With the corollary that if it _is_ correctly converted then is it converting it back correctly?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 June 2020 07:54 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/11 "2020-06-20T07:54:18Z")

</div>

I have done some experiments with interesting results. The function below creates a sparse array with element index 1 set to null and index 3 set to 7. Using forEach() with node.warn finds only those elements and displays the values null and 7 as expected. Looping through using indexes 0 to length-1 then node.warn shows the empty elements as undefined and the null element is null. However, returning the array in the payload and displaying via a debug node shows the undefined ones as null, which is arguably a bug in the debug display. Perhaps @dceejay would like to comment on this.  
The function node contains

```auto
array = []
array[1] = null
array[3] = 7
array.forEach( function(val, i) {node.warn(`each index: ${i} value: ${val}`)})
for (i=0; i<array.length; i++) {
    node.warn(`for length index: ${i} value: ${array[i]}`)
}
msg.payload = array
return msg;

```

```auto
[{"id":"ad3cb23a.59648","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":480,"wires":[["168ec116.5dd1cf"]]},{"id":"168ec116.5dd1cf","type":"function","z":"bdd7be38.d3b55","name":"sparse array tests","func":"array = []\narray[1] = null\narray[3] = 7\narray.forEach( function(val, i) {node.warn(`each index: ${i} value: ${val}`)})\nfor (i=0; i<array.length; i++) {\n node.warn(`for length index: ${i} value: ${array[i]}`)\n}\nmsg.payload = array\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":480,"wires":[["261e7cc8.e77e8c"]]},{"id":"261e7cc8.e77e8c","type":"debug","z":"bdd7be38.d3b55","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":530,"y":480,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [20 June 2020 09:58 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/12 "2020-06-20T09:58:34Z")

</div>

at first glance yes it looks like the debug display is not doing the right thing... we do handle a simple `msg.payload = undefined` so it should be possible to fix - I guess somewhere in the encodeObject function in util.js.

However more generally - JSON does not have a representation for `undefined` so JSON.stringify replaces undefined by null.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [20 June 2020 10:01 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/13 "2020-06-20T10:01:28Z")

</div>

I think the underlying issue is that a JSON array requires all elements to be specified (at least as `null`) while javascript supports sparse arrays where not all elements are specified.

So not every javascript array can be unambiguously mapped to a JSON array.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 June 2020 10:04 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/14 "2020-06-20T10:04:37Z")

</div>

Agreed, as @dceejay says, looking at the [json spec](https://www.json.org/json-en.html) it seems that a sparse array cannot be represented exactly in JSON. Null is allowed as the value for an array element but undefined or missing are not allowed. That means that converting a sparse array to json and back must fill in the missing elements with nulls.

That still leaves the question as to whether the effect that @janvda is seeing when he transforms the sparse array with jsonata is expected behaviour or not. Googling for `jsonata sparse array` seems to yield nothing that is obviously relevant, which is rather surprising to me.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [20 June 2020 10:15 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/15 "2020-06-20T10:15:22Z")

</div>

I think JSONata is designed to work only with JSON objects.

As I am using a sparse array (which is actually not a proper JSON object) you may say that this is not valid input for a JSONata expression.

... nevertheless the JSONata implementation is using javascript map and filter functions under the hood which also in javascript would skip the elements in a sparse array that are not specified. So the JSONata implementation appears to be consistent with that.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [20 June 2020 10:19 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/16 "2020-06-20T10:19:03Z")

</div>

the other open question from your OP - is whether the spreadsheet node should represent blank cells as undefined or null or should use an empty string as it would do if it was csv

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [20 June 2020 10:20 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/17 "2020-06-20T10:20:26Z")

</div>

> [@janvda](#):
>
> the JSONata implementation appears to be consistent with that

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.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [20 June 2020 10:44 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/18 "2020-06-20T10:44:22Z")

</div>

> [@Colin](#):
>
> 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:

- [strange behaviour when processing sparse arrays · Issue #458 · jsonata-js/jsonata · GitHub](https://github.com/jsonata-js/jsonata/issues/458)

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [20 June 2020 10:51 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/19 "2020-06-20T10:51:38Z")

</div>

> [@dceejay](#):
>
> the other open question from your OP - is whether the spreadsheet node should represent blank cells as undefined or null or should use an empty string as it would do if it was csv

I guess that node [GitHub - sakai-to/node-red-contrib-spreadsheet-in: Node-RED nodes to read data from spreadsheet (Excel, ODS, etc.) file](https://github.com/sakai-to/node-red-contrib-spreadsheet-in) is using a node.js module that is doing the conversion to a sparse array.

Using sparse arrays instead of undefined or null might not be a bad choice to safe memory space for those excel sheets having a lot of empty cells.

> [@dceejay](#):
>
> at first glance yes it looks like the debug display is not doing the right thing... we do handle a simple `msg.payload = undefined` so it should be possible to fix - I guess somewhere in the encodeObject function in util.js.

It would indeed make sense that debug window is not showing `null` for missing array elements or `undefined` array elements.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [21 June 2020 20:45 UTC](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615/20 "2020-06-21T20:45:26Z")

</div>

I don't believe that the use of an array of arrays is a good model for a spreadsheet anyway. If you need a proper full model for a spreadsheet, you need to keep the data in the spreadsheet. Or maybe read it into a SQL DB.

The standard model in JavaScript for such data is an array of objects which better models the spreadsheet schema. While sparse arrays certainly save memory, as you've seen, it doesn't allow you to retain a consistent model of the original data. You have to know the content of your data in order to ensure you don't use the wrong JavaScript function.

If using an array of objects, you no longer need to worry about sparseness since you can safely miss out any column and still process the data in a consistent way without needing to know the contents.

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.

[Next page](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615.md?page=2)