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

General
1

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
image642Ă—606 56.9 KB

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

image
image746Ă—1618 91.1 KB

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

image
image698Ă—520 62 KB

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
image726Ă—546 74.1 KB

Array data:
image

After $spread() function:

image

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:

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

After $spread() function:
image

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.

2

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

3

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.

4

'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 ?

5

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

image

image
image274Ă—980 21.5 KB 

[
    {
        "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": []
    }
]
1 Like
6

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.

[{"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":[]}]
7

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!!

8

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.

2 Likes
9

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 :grinning:

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

1 Like
10

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!).

11

The JSONata to return your arrays would be

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

[{"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":[]}]

You could have used

$$.payload.totalHomeuse

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

1 Like
12

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

13

Yes, well, that's JSONata for you :slight_smile:

2 Likes
14

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