# Possible issue with mySQL date column and JSONATA $spread() function

**URL:** <https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257>\
**Category:** General\
**Tags:** database\
**Created:** [26 January 2022 13:38 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257 "2022-01-26T13:38:26Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [26 January 2022 13:38 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/1 "2022-01-26T13:38:26Z")

</div>

Apologies in advance for a the long post.

My data is stored in a mySQL database with one of the fields being a date field (TIMESTAMP). My flow queries the database and returns the selected values that look like this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c9111017af732b7caa2045f6088e8b78f365e75c.jpeg)

The output then goes through a change node to split up the individual data fields into separate arrays using the JSONATA $spread() function.

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

This works perfectly on all data EXCEPT the date field (payload.day)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/0/f07b588acb690ffc86da8646e64bc12c3e28b1ae.jpeg)

If I copy the data returned from the SQL query (copy the payload without changing the date field) and send it via the inject node, it works correctly.

**Inject Node:**

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

**Array data:**  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/2/42490d3b9061a8f98510de4040bdd320b407ddc0.png)

**After $spread() function:**

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

If I use the DATE\_FORMAT function (convert to string) in my query it works correctly (but then I cannot use the date/time in a time series chart).

Query:

```auto
select round((load_home_imp_per - load_home_exp_per)/1000,2) as 'totalHomeuse', round((site_grid_imp_per)/1000,2) as 'gridToHome', (round((solar_exp_per - solar_imp_per)/1000,2)-round((site_grid_exp_per)/1000,2)-round(battery_pwl_imp_per/1000,2)) as 'solarToHome', round(battery_pwl_exp_per/1000,2) as 'batteryToHome', round((solar_exp_per - solar_imp_per)/1000,2) as 'totalSolar', round((site_grid_exp_per)/1000,2) as 'solarToGrid', round(battery_pwl_imp_per/1000,2) as 'solarToBattery', date_format(created,'%Y-%m-%d')as 'day' from powerwall_cumm where date (created) >= '2022-01-17' and date (created) <= '2022-01-25' : 

```

**Array data:**  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/d/cdb8a0b5843f6324bc8969ba7ba38249f0483f9b.png)

**After $spread() function:**  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/2/d27e2bc3faddbc829f4f360490f37ba70fc9598a.png)

I would be happy to post sample data and/or flow, but this ONLY happens when the data is returned via a query and is in TIMESTAMP format, so it is somewhat specific. In order to replicate this, you need to have a mySQL database with a TIMESTAMP column and return multiple columns in your query (including the TIMESTAMP column).

Node-RED v 1.3.5  
mySQL node v 1.0.0  
Node.js v 14.15.5

Thanks for your help.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [26 January 2022 14:22 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/2 "2022-01-26T14:22:49Z")

</div>

Strange indeed, did you try to rename `dateTime` to something else as well ? (maybe hitting some internal functions somewhere, who knows)

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [26 January 2022 14:27 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/3 "2022-01-26T14:27:20Z")

</div>

> [@bakman2](#):
>
> Strange indeed, did you try to rename `dateTime` to something else as well

Yes - tried day, date and a couple of different ones. What is really strange is that if I take the same data and send it via an inject node (JSON format), it works perfectly.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [26 January 2022 14:36 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/4 "2022-01-26T14:36:17Z")

</div>

'Day' and 'date' are reserved keywords in javascript (and in sql for that matter), to be safe always prefix/suffix generic words with an underscore or something.

> What is really strange is that if I take the same data and send it via an inject node (JSON format), it works perfectly.

So both the inject node and sql node produce exactly the same output ? Can you show and example of this ?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [26 January 2022 14:52 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/5 "2022-01-26T14:52:54Z")

</div>

To avoid that unmaintainable (IMO) switch node and speed this up (and make it re-usable) - a simple function might be better...

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

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

```auto
[
    {
        "id": "337250983f7905d8",
        "type": "inject",
        "z": "4cad319b1add5007",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payloadType": "date",
        "x": 812,
        "y": 640,
        "wires": [
            [
                "da751414a159fccd"
            ]
        ]
    },
    {
        "id": "da751414a159fccd",
        "type": "function",
        "z": "4cad319b1add5007",
        "name": "generate dummy data (ignore me)",
        "func": "\nconst templateRow = {\n totalHomeuse: 67.74,\n gridToHome: 34.62,\n solarToHome: 21.11,\n batteryToHome: 12.01,\n totalSolar: 34.88,\n solarToGrid: 0.05,\n solarToBattery: 13.72,\n day: \"2022-01-17T06:00:00.0002\"\n}\n\nconst data = []\nfor (let index = 0; index < 10; index++) {\n const element = RED.util.cloneMessage(templateRow);\n element.totalHomeuse = randomFloat(60,70)\n element.gridToHome = randomFloat(30,40)\n element.solarToHome = randomFloat(20,30)\n element.batteryToHome = randomFloat(10,20)\n element.totalSolar = randomFloat(30,40)\n element.solarToGrid = randomFloat(0.01,0.99)\n element.solarToBattery = randomFloat(11,14)\n element.day = new Date(Date.now() + ((index+1)*1000*60)).toISOString()\n data.push(element)\n}\nmsg.payload = data;\n\n\nreturn msg;\n\n\n/**\n* @param {number} min\n* @param {number} max\n*/\nfunction randomFloat(min, max) {\n return Math.random() * (max - min) + min;\n};",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 1050,
        "y": 640,
        "wires": [
            [
                "942662580d53e493",
                "e3e67cb9207e5e23"
            ]
        ]
    },
    {
        "id": "942662580d53e493",
        "type": "debug",
        "z": "4cad319b1add5007",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1302,
        "y": 640,
        "wires": []
    },
    {
        "id": "e3e67cb9207e5e23",
        "type": "function",
        "z": "4cad319b1add5007",
        "name": "toArrays",
        "func": "\n//Props can be dynamically sent in via the msg object if required (to make it generic)\nconst props = [\n \"totalHomeuse\",\n \"gridToHome\",\n \"solarToHome\",\n \"batteryToHome\",\n \"totalSolar\",\n \"solarToGrid\",\n \"solarToBattery\",\n \"day\"\n];\n\nmsg.result = toArrays(msg.payload, props)\nreturn msg;\n\n\n\n\n\n/**\n * Convert object properties in `data` to array of values\n * @param {any[]} data - Data to scan\n * @param {string[]} propNames - property names to collect\n*/\nfunction toArrays(data, propNames) {\n const result = {};\n propNames.forEach(p => {\n result[p] = [];\n data.forEach(e => {\n result[p].push(e[p]);\n })\n })\n return result;\n}\n\n\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 972,
        "y": 720,
        "wires": [
            [
                "66081cdb3d263701"
            ]
        ]
    },
    {
        "id": "66081cdb3d263701",
        "type": "debug",
        "z": "4cad319b1add5007",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "result",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1302,
        "y": 720,
        "wires": []
    }
]

```

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [26 January 2022 14:56 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/6 "2022-01-26T14:56:02Z")

</div>

> [@bakman2](#):
>
> So both the inject node and sql node produce exactly the same output ? Can you show and example of this ?

What I did was copy the data returned by the query from the debug panel and paste it into the inject node. However, when using the inject node, the $spread() works but not when the data comes from the sql node.

```auto
[{"id":"f3698f61.2c587","type":"inject","z":"3fad0f28.757898","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"totalHomeuse\":67.74,\"gridToHome\":34.62,\"solarToHome\":21.11,\"batteryToHome\":12.01,\"totalSolar\":34.88,\"solarToGrid\":0.05,\"solarToBattery\":13.72,\"day\":\"2022-01-17T06:00:00.000Z\"},{\"totalHomeuse\":40.11,\"gridToHome\":9.44,\"solarToHome\":20.73,\"batteryToHome\":9.94,\"totalSolar\":32.45,\"solarToGrid\":0.02,\"solarToBattery\":11.7,\"day\":\"2022-01-18T06:00:00.000Z\"},{\"totalHomeuse\":42.33,\"gridToHome\":27.9,\"solarToHome\":12.81,\"batteryToHome\":1.62,\"totalSolar\":15.22,\"solarToGrid\":0.01,\"solarToBattery\":2.4,\"day\":\"2022-01-19T06:00:00.000Z\"},{\"totalHomeuse\":95.92,\"gridToHome\":74.44,\"solarToHome\":20.01,\"batteryToHome\":1.47,\"totalSolar\":22.48,\"solarToGrid\":0,\"solarToBattery\":2.47,\"day\":\"2022-01-20T06:00:00.000Z\"},{\"totalHomeuse\":79.26,\"gridToHome\":43.65,\"solarToHome\":25.27,\"batteryToHome\":10.34,\"totalSolar\":37.45,\"solarToGrid\":0.02,\"solarToBattery\":12.16,\"day\":\"2022-01-21T06:00:00.000Z\"},{\"totalHomeuse\":69.16,\"gridToHome\":33.93,\"solarToHome\":27.18,\"batteryToHome\":8.05,\"totalSolar\":36.76,\"solarToGrid\":0.05,\"solarToBattery\":9.53,\"day\":\"2022-01-22T06:00:00.000Z\"},{\"totalHomeuse\":59.2,\"gridToHome\":27.87,\"solarToHome\":21.15,\"batteryToHome\":10.17,\"totalSolar\":36.01,\"solarToGrid\":0.03,\"solarToBattery\":14.83,\"day\":\"2022-01-23T06:00:00.000Z\"},{\"totalHomeuse\":48.79,\"gridToHome\":29.5,\"solarToHome\":16.26,\"batteryToHome\":3.03,\"totalSolar\":17.43,\"solarToGrid\":0.01,\"solarToBattery\":1.16,\"day\":\"2022-01-24T06:00:00.000Z\"},{\"totalHomeuse\":41.5,\"gridToHome\":9.42,\"solarToHome\":21.18,\"batteryToHome\":10.9,\"totalSolar\":37.29,\"solarToGrid\":0.8,\"solarToBattery\":15.31,\"day\":\"2022-01-25T06:00:00.000Z\"}]","payloadType":"json","x":1404.3334350585938,"y":2122.0001220703125,"wires":[["ae24158f.996dc","eb0b8104.52ae7"]]},{"id":"ae24158f.996dc","type":"change","z":"3fad0f28.757898","name":"","rules":[{"t":"set","p":"created","pt":"msg","to":"$spread(payload.day)","tot":"jsonata"},{"t":"set","p":"totalHomeuse","pt":"msg","to":"$spread(payload.totalHomeuse)","tot":"jsonata"},{"t":"set","p":"gridToHome","pt":"msg","to":"$spread(payload.gridToHome)","tot":"jsonata"},{"t":"set","p":"solarToHome","pt":"msg","to":"$spread(payload.solarToHome)","tot":"jsonata"},{"t":"set","p":"batteryToHome","pt":"msg","to":"$spread(payload.batteryToHome)","tot":"jsonata"},{"t":"set","p":"totalSolar","pt":"msg","to":"$spread(payload.totalSolar)","tot":"jsonata"},{"t":"set","p":"solarToGrid","pt":"msg","to":"$spread(payload.solarToGrid)","tot":"jsonata"},{"t":"set","p":"solarToBattery","pt":"msg","to":"$spread(payload.solarToBattery)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1654.3333740234375,"y":2111,"wires":[["8b34dfb8.3b0758"]]},{"id":"8b34dfb8.3b0758","type":"debug","z":"3fad0f28.757898","name":"spread2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1841.3333740234375,"y":2111,"wires":[]}]

```

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [26 January 2022 15:02 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/7 "2022-01-26T15:02:29Z")

</div>

> [@Steve-Mcl](#):
>
> To avoid that unmaintainable (IMO) switch node and speed this up (and make it re-usable) - a simple function might be better...

Thanks! I was doing something similar (without the custom function) in a function node (looping through the payload array and creating separate arrays for the values) but thought that the change node would be simpler - I guess not!!

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [26 January 2022 15:06 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/8 "2022-01-26T15:06:07Z")

</div>

> [@rakgupta](#):
>
> but thought that the change node would be simpler - I guess not!!

IMO - JSONata is not simpler, not faster to write (as in entering all those items in the switch node), not faster to run, not worth it.

Just my opinion (sorry JSONata fans)

It [JSONata] definitely has value and its place, but I suspect, not this time.

---

<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:** [26 January 2022 19:58 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/9 "2022-01-26T19:58:28Z")

</div>

It does have its moments, especially in being able to reshape complex JSON. But as has been mentioned many times before, if the logic isn't fairly obvious in JSONata you will probably end up spending (wasting) a lot of time trying to figure it out when some simple JavaScript would have been easier and faster.

Of course, that is easy to say if, like us, you know JavaScript 😀

Really, the same is true of all transform languages, JSONata, XSLT, RegEx, etc. And indeed even SQL sometimes.

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [26 January 2022 20:07 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/10 "2022-01-26T20:07:52Z")

</div>

> [@TotallyInformation](#):
>
> if the logic isn't fairly obvious in JSONata you will probably end up spending (wasting) a lot of time trying to figure it out when some simple JavaScript would have been easier and faster.

I think that was why I was going with JSONATA - I am not versed in JavaScript and the $spread function does exactly what I wanted. If @Steve-Mcl had not given the example, I would not have figured out that particular solution. My original solution was looping through the array in a function node and parsing the data that way - it worked but his was much simpler (though hard for me to understand!).

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [26 January 2022 22:19 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/11 "2022-01-26T22:19:06Z")

</div>

The JSONata to return your arrays would be

```auto
$keys($$.payload).${$: $lookup($$.payload, $)}

```

```auto
[{"id":"337250983f7905d8","type":"inject","z":"c791cbc0.84f648","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"totalHomeuse\":68.90714786310673,\"gridToHome\":31.906674784052676,\"solarToHome\":23.27931943919369,\"batteryToHome\":11.519549218255168,\"totalSolar\":39.60022628689188,\"solarToGrid\":0.050980126017972305,\"solarToBattery\":11.162057619506802,\"day\":\"2022-01-26T20:53:20.861Z\"},{\"totalHomeuse\":68.17193369533685,\"gridToHome\":31.07755396256973,\"solarToHome\":28.569870077875187,\"batteryToHome\":13.031317654171131,\"totalSolar\":30.064744461880043,\"solarToGrid\":0.7958029741777393,\"solarToBattery\":11.271577903661264,\"day\":\"2022-01-26T20:54:20.864Z\"},{\"totalHomeuse\":67.16614654807084,\"gridToHome\":32.257420539732514,\"solarToHome\":29.20887159651446,\"batteryToHome\":17.37455777236321,\"totalSolar\":36.94699633228725,\"solarToGrid\":0.8362179586357198,\"solarToBattery\":12.565728386297618,\"day\":\"2022-01-26T20:55:20.875Z\"},{\"totalHomeuse\":65.8292886962078,\"gridToHome\":35.4808099424924,\"solarToHome\":27.336937946503028,\"batteryToHome\":19.489670719263476,\"totalSolar\":34.55025701435134,\"solarToGrid\":0.29257186572989097,\"solarToBattery\":13.400657871922705,\"day\":\"2022-01-26T20:56:20.878Z\"},{\"totalHomeuse\":69.67262679051375,\"gridToHome\":34.395759458670625,\"solarToHome\":20.7053644447368,\"batteryToHome\":18.800451177161506,\"totalSolar\":34.764609982906805,\"solarToGrid\":0.14803058177420933,\"solarToBattery\":12.078324201449801,\"day\":\"2022-01-26T20:57:20.878Z\"},{\"totalHomeuse\":66.57725908055531,\"gridToHome\":39.59637761956939,\"solarToHome\":20.941086140770008,\"batteryToHome\":19.976242868459227,\"totalSolar\":33.20923889688261,\"solarToGrid\":0.9271043152780161,\"solarToBattery\":12.622650343656929,\"day\":\"2022-01-26T20:58:20.878Z\"},{\"totalHomeuse\":64.34442644388044,\"gridToHome\":32.11309292980981,\"solarToHome\":26.126783815992567,\"batteryToHome\":14.368642294250611,\"totalSolar\":39.047538137886285,\"solarToGrid\":0.24256521834726666,\"solarToBattery\":11.595420686863388,\"day\":\"2022-01-26T20:59:20.879Z\"},{\"totalHomeuse\":66.09638401361939,\"gridToHome\":34.39870937853556,\"solarToHome\":29.202466044209586,\"batteryToHome\":15.864266218530506,\"totalSolar\":39.304993839474335,\"solarToGrid\":0.7857975106521085,\"solarToBattery\":12.352390292999372,\"day\":\"2022-01-26T21:00:20.879Z\"},{\"totalHomeuse\":67.90086323185135,\"gridToHome\":39.28696910201738,\"solarToHome\":20.421418162110413,\"batteryToHome\":12.219492046406298,\"totalSolar\":36.78781703050602,\"solarToGrid\":0.02574248256924662,\"solarToBattery\":13.16417183930692,\"day\":\"2022-01-26T21:01:20.879Z\"},{\"totalHomeuse\":68.0651217083145,\"gridToHome\":31.146407206373922,\"solarToHome\":26.847013112677203,\"batteryToHome\":13.533197531414565,\"totalSolar\":38.92231949289614,\"solarToGrid\":0.7746580941292076,\"solarToBattery\":13.762551110820679,\"day\":\"2022-01-26T21:02:20.879Z\"}]","payloadType":"json","x":270,"y":2740,"wires":[["89094b74.76e5f8"]]},{"id":"89094b74.76e5f8","type":"change","z":"c791cbc0.84f648","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$keys($$.payload).${$:$lookup($$.payload,$)}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":460,"y":2740,"wires":[["66081cdb3d263701"]]},{"id":"66081cdb3d263701","type":"debug","z":"c791cbc0.84f648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":750,"y":2740,"wires":[]}]

```

> [@rakgupta](#):
>
> $spread function does exactly what I wanted.

You could have used

```auto
$$.payload.totalHomeuse

```

to return an array of totalHomeuse, etc. No need for the spread function.

---

<div class="post-metadata">

**Author:** ![rakgupta](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rakgupta/32/42535_2.png) [@rakgupta](https://discourse.nodered.org/u/rakgupta)\
**Post date:** [26 January 2022 22:43 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/12 "2022-01-26T22:43:19Z")

</div>

> [@E1cid](#):
>
> You could have used
> 
> ```auto
> $.payload.totalHomeuse
> 
> ```
> 
> to return an array of totalHomeuse, etc. No need for the spread function.

Thank you!! So many ways to do this - now to figure out which one I can understand and remember:-)

---

<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:** [26 January 2022 23:58 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/13 "2022-01-26T23:58:44Z")

</div>

> [@rakgupta](#):
>
> it worked but his was much simpler (though hard for me to understand!)

Yes, well, that's JSONata for you 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [27 March 2022 23:59 UTC](https://discourse.nodered.org/t/possible-issue-with-mysql-date-column-and-jsonata-spread-function/57257/14 "2022-03-27T23:59:34Z")

</div>

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