Influx + JSONata + Multiline Chart

Dashboard
1

Hello,

Im trying to show in dashboard a multiline chart....im getting data from influx (in raw mode), and use Jsonata to show in chart....LINK

I dont know if my method is the best for that...

My difficulty is how to modify this JSONata, create something more dynamic, reading the size of the array what Im receiving, (creating a cycle For in JSONata), because I would like to have graphics with 4, 6, 13 lines/var... depending on what user want to see.

Thanks for the time ppl.

2

Personally I would not use JSONata in that case. I am not saying it cannot be done but I usually avoid JSONata for more complex situations. Instead I would probably use a Function node.

1 Like
3

Hello

Thanks for the reply.

So what example i should use

[{
"series": ["A", "B", "C"],
"data": [
    [{ "x": 1504029632890, "y": 5 },
     { "x": 1504029636001, "y": 4 },
     { "x": 1504029638656, "y": 2 }
    ],
    [{ "x": 1504029633514, "y": 6 },
     { "x": 1504029636622, "y": 7 },
     { "x": 1504029639539, "y": 6 }
    ],
    [{ "x": 1504029634400, "y": 7 },
     { "x": 1504029637959, "y": 7 },
     { "x": 1504029640317, "y": 7 }
    ]
],
"labels": [""]
}]

or Non-Series

[{
    "series": ["X", "Y", "Z" ],
    "data": [ [5,6,9], [3,8,5], [6,7,2] ],
    "labels": [ "Jan", "Feb", "Mar" ]
}]

Btw... is there any example code or demonstration for this dynamic case?

Thanks.

4

One is for time series data (which means the data are a sequence of values with timestamps, the other is for non time series data. Presumably you know whether you have data with timestamps or not.

Since I understood you already have something going, using JSONata, don't you already know what format you want the data?

5

yes i have something.... im using non-series but i would like to change for series(normal), because im getting all the data with timestamp.

Like this:

"payload": {
    "results": [
      {
        "series": [
          {
            "columns": [
              "time",
              "mean_HTF_L3_INLET_Temp_BoilerSd",
              "mean_HTF_L3_INLET_Pressure_BoilerSd",
              "mean_HTF_L3_OUTLET_Pressure_BoilerSd",
              "mean_HTF_L3_OUTLET_Temp_BoilerSd"
            ],
            "name": "Boiler3",
            "values": [
              [
                1598048400000,
                254.45076923077,
                0,
                0,
                228.81846153846
              ],
              [
                1598049000000,
                253.50483333333,
                0,
                0,
                227.83666666667
              ],
6

I would expect such things to be possible in jsonata. Sometimes it is a bit searching to get the jsonata query right.

... but I admit that it is not fully clear what outcome you would expect. Do you want to be able to get only the first 4 or 6 or 13 values of the input to be considered ?

E.g. adding the query ~>$filter(function($v, $i, $a) { $i <= 3 }) will only return the first 4 elements of the array.

An example of using this filter.

[series.values.($boolean($[1])?$round($[1],2):$[1])]~>$filter(function($v, $i, $a) { $i <= 3 })

Instead of using 3, you can use a jsonata variable to make it dynamic.

7

Hello

ty, for the answer.

I want a for loop for JSONata .... I tried to use some functions (like $count to get the size of the array), but to no sucess.

What I want for my application is that the user can select as many variables as he wants for the chart (between 1 and 15 variables). So I am looking for something dynamic, read the size of the array / quantity of variables and format the information.

I started to do something in a simple function of NR.

//
// Format the InfluxDB results to match the charts JSON format
//

var series = ["1","2","3","4","5"];
var labels = ["Data Values"];
var data = "[[";
var thetime;

var arrSize = msg.payload.results[0].series[0].values.length;
node.warn("arrSize="+arrSize);
var arrValueSize = msg.payload.results[0].series[0].columns.length;

node.warn("arrValueSize="+arrValueSize);

for (var j = 1; j < arrValueSize; j++) {
    for (var i = 0; i < arrSize; i++) {
        thetime = Number(msg.payload.results[0].series[0].values[i][0]); 
        data += '{ "x":' + thetime + ', "y":' + msg.payload.results[0].series[0].values[i][j] + '}';
        if (i < (arrSize - 1)) {
            data += ","
        }
    }
    
    if (j < (arrSize - 1)) {
        data += "],["
    } else {
        data += "]]"
    }
}

node.warn("Data="+data);

var jsondata = JSON.parse(data);
msg.payload = [{ "series": series, "data": jsondata, "labels": labels }];
msg.playload = data;
return msg;

That function works well if the size of the array is under 15, higher than 15 i got error "function : (error)
"SyntaxError: Unexpected end of JSON input""

The Data i get from influx its this:

{"_msgid":"6b3701fb.36058","payload":{"results":[{"statement_id":0,"series":[{"name":"Boiler3","columns":["time","mean_HTF_L3_INLET_Temp_BoilerSd","mean_HTF_L3_INLET_Pressure_BoilerSd","mean_HTF_L3_OUTLET_Pressure_BoilerSd","mean_HTF_L3_OUTLET_Temp_BoilerSd"],"values":[[1598192400,208.272,0,0,208.077],[1598193000,205.98083333333327,0,0,205.74450000000004],[1598193600,203.217,0,0,202.96050000000002],[1598194200,200.98450000000008,0,0,200.1555],[1598194800,207.1098333333333,0,0,197.33816666666672],[1598195400,216.7206666666667,0,0,194.43133333333344],[1598196000,219.60200000000006,0,0,194.80716666666666],[1598196600,226.62233333333333,0,0,200.04766666666666],[1598197200,239.0766666666667,0,0,205.7966666666667],[1598197800,253.5746666666666,0,0,211.71133333333333],[1598198400,265.5541666666668,0,0,220.43750000000003],[1598199000,275.61216666666655,0,0,232.17100000000002],[1598199600,272.9371666666667,0,0,244.71483333333327],[1598200200,270.986,0,0,253.78683333333328],[1598200800,269.4945,0,0,248.0783333333333],[1598201400,267.7300000000001,0,0,232.50483333333332],[1598202000,271.39616666666666,0,0,239.0506666666666],[1598202600,271.03650000000005,0,0,243.46950000000004],[1598203200,269.36116666666663,0,0,249.65983333333324],[1598203800,270.8678333333334,0,0,248.15099999999993],[1598204400,269.06666666666666,0,0,247.2495],[1598205000,270.2483333333334,0,0,247.95749999999995],[1598205600,269.67183333333327,0,0,243.35633333333334],[1598206200,269.93149999999997,0,0,244.4910000000001],[1598206800,269.84736842105264,0,0,244.53473684210525]]}]}]},"topic":"","query":"SELECT mean(HTF_L3_INLET_Temp_BoilerSd) AS mean_HTF_L3_INLET_Temp_BoilerSd, mean(HTF_L3_INLET_Pressure_BoilerSd) AS mean_HTF_L3_INLET_Pressure_BoilerSd, mean(HTF_L3_OUTLET_Pressure_BoilerSd) AS mean_HTF_L3_OUTLET_Pressure_BoilerSd, mean(HTF_L3_OUTLET_Temp_BoilerSd) AS mean_HTF_L3_OUTLET_Temp_BoilerSd FROM Boiler3 WHERE time > now() - 4h AND time < now() AND Grp='HTF_L3' AND Side='BOILER3' GROUP BY time(10m) FILL(null)"}

anyone have any idea ?

thanks

8

I have created a jsonata expression:

Here a copy paste of same jsonata expression:

[{
   "series": interested_series,
   "data" :  interested_series.(
    /* determine columns_index = array index of interested series in payload.results.series.columns */
    $series := $;
    $columns_index := $$.payload.results.series.columns#$index[$=$series].$index; 
    /* only consider the first number_of_values */
    $$.payload.results.series.values#$val_index[$val_index<$$.number_of_values].{
        "x" : $[0],
        "y" : $[$columns_index]
    }
),
"labels": [""]
}]

Besides the influxdb payload it is expecting the following 2 input parameters must be set:

  "interested_series" : [ "mean_HTF_L3_INLET_Pressure_BoilerSd" , "mean_HTF_L3_OUTLET_Temp_BoilerSd" ],
  "number_of_values" : 2,

where

  • interested_series is an array listing the series you are interested in
  • number_of_values specifies the number of values you want for each series.

So you can put this jsonata expression in a change node.
When retrieving the influxdb payload you must extend it also with the above 2 input parameters before feeding it into in the change node with the jsonata expression.

9

Hello

Thanks for the help :slight_smile: i will try the code.

10

Hello

JSONata its working like i want i did few adjust and its working. thanks

Now i want solve the problem in that function....

1 Like
11

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