How to forward data from MSSQL to Node RED Chart

[spamcle...@gmail.com]

Hi,

I want to read data from my MSSQL Server and display it in a Node RED chart.

I created an MSSQL node with a single query "select top 10 * from weblog"

The output is an array with objects looking like that:

ayload : array[10]

array[10]

0: object

ID: 2794563

Timestamp: "2017-08-11T05:57:06.356Z"

Parameter: "Alarmanlage_Status"

Username: "10.0.0.21"

Value: "0"

P1: "Homematic"

P2: "LAN"

P3: "HM"

P4: "x"

P5: "x"

Next I forward this array to a json-function to convert it to JSON format.

string[2882]

[
    {
        "ID": 2794563,
        "Timestamp": "2017-08-11T05:57:06.356Z",
        "Parameter": "Alarmanlage_Status",
        "Username": "10.0.0.21",
        "Value": "0",
        "P1": "Homematic",
        "P2": "LAN",
        "P3": "HM",
        "P4": "x",
        "P5": "x"
    },
    {
        "ID": 2854028,
        "Timestamp": "2017-08-19T17:19:55.776Z",
        "Parameter": "Alarmanlage_Status",
        "Username": "10.0.0.21",
        "Value": "1",
        "P1": "Homematic",
        "P2": "LAN",
        "P3": "HM",
        "P4": "x",
        "P5": "x"
    },

Next I send this json to a function, that prepares the "value"-field for the chart node.

msg.payload = msg.payload.Value;

return msg;

Unfortunately the output of this function is "undefined".

26.2.2018, 19:00:22node: cba963a5.8b118msg.payload : undefined

undefined

Can anybody help me to prepare this data for my chart object ?

BR

[steve rickus]

Yes, get rid of the JSON node -- as you can see in the first debug output, the msg payload is already an array of 10 objects!

Passing it through  the json node just converts back into a plain string, which you can't access easily in your flow...

Then, you will need to reformat your array of query results into a format that the ui_chart node expects: You have two choices --

split the array into 10 msgs and change each one to have your [x, y] coordinates, or build an array of series, with labels, x, and y.

The ui_chart node's info shows the expected data format, if you want to pass all the chart data in one msg

Either way, the best node for restructuring your query results into chart series data, is to use the change node with a Jsonata expression.

Although the jsonata syntax is hard to read, it's very powerful. In the past, I've use this expression (choose the "J:" pulldown):

[(
  $groups := payload.{
    Parameter: [
      $.{"x": Timestamp, "y": Value}
    ]
  }~>$spread();
  $series := $count($groups);

  $series = 0 ? undefined : $groups {
    "series": $keys(),
    "data": $series = 1 ? [[*]] : $.[*]
  }
)]

It's probably overkill for your data, but it handles multiple series and out of order data... Hope it works for you.

--

Steve

[Rewe Node]

Am Montag, 26. Februar 2018 19:01:21 UTC+1 schrieb spamcle...@gmail.com:

Unfortunately the output of this function is "undefined".

26.2.2018, 19:00:22node: cba963a5.8b118msg.payload : undefined

undefined

Can anybody help me to prepare this data for my chart object ?

You can use a simple JSONata expression in the change-node. Like this:

Or 

Value[].$number()

if you want

[spamcle...@gmail.com]

I did as proposed - unfortunately I still get an "undefined" error in the debug windows

 

[Rewe Node]

Can you export your flow? 

[spamcle...@gmail.com]

sure:

 I hope thats what you expect :)

[{"id":"703dc06e.d8032","type":"inject","z":"322ca28f.0bf11e","name":"","topic":"","payload":"5","payloadType":"num","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":235,"y":101,"wires":[["e7e359bb.f04148"]]},{"id":"e7e359bb.f04148","type":"MSSQL","z":"322ca28f.0bf11e","mssqlCN":"c0008e37.2f5d2","name":"Test_One","query":"select top 10 * from weblog","outField":"payload","x":196,"y":368,"wires":[["e07e0a69.e2cd58"]]},{"id":"fbae2502.ac9878","type":"debug","z":"322ca28f.0bf11e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":606.5,"y":353,"wires":[]},{"id":"e07e0a69.e2cd58","type":"change","z":"322ca28f.0bf11e","name":"Test01","rules":[{"t":"set","p":"payload","pt":"msg","to":"Value[].$number()","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":383,"y":364,"wires":[["fbae2502.ac9878"]]},{"id":"c0008e37.2f5d2","type":"MSSQL-CN","z":"","name":"IOT","server":"10.0.0.19","encyption":false,"database":"WeblogDB"}]

[Rewe Node]

Am Dienstag, 27. Februar 2018 10:09:50 UTC+1 schrieb spamcle...@gmail.com:

sure:

 I hope thats what you expect :)

...

Ok, my mistake. Try this:

payload.Value[].$number()

 

[spamcle...@gmail.com]

Ok, I think i reached the next level now :)

27.2.2018, 10:39:38node: fbae2502.ac9878msg.payload : array[10]

array[10]

0: 0

1: 1

2: 0

3: 1

4: 0

5: 1

6: 0

7: 1

8: 0

9: 1

The Change Node output looks fine now, but when I connect a chart node it says 

27.2.2018, 10:40:20node: affa00b.13a8msg : string[15]

"Bad data inject"

[Rewe Node]

Am Dienstag, 27. Februar 2018 10:46:11 UTC+1 schrieb spamcle...@gmail.com:

Ok, I think i reached the next level now :)

...

msg : string[15]

"Bad data inject"

You need other data structure for chart node.

Like so.

[
   {    
     "key": "myData",
      "values":payload.Value.$number()
   }
]

 

[Rewe Node]

For further information about data structure, see https://github.com/node-red/node-red-dashboard/blob/master/Charts.md

[spamcle...@gmail.com]

Ok, I see

Is there a "smart" way to do such operations in NodeRED or do I have to parse the required input-string "sign by sign" ?