Reading data from influx with an index on mean

General
1

I am struggling with the way I must retrieve a variable amount of data from influx database.
Data from database looks like this:

afbeelding

So I get some 'mean' - values. But the amount of of (mean) values can differ, in my case from 1 to 10.
Normally I can retrieve the data with say let test = msg.payload[0].mean_4;, but if the number can vary I need to have an index on mean_<index>. So how can I do this?

2

What do you mean by an index?

What exactly are you trying to achieve?

Perhaps you could explain what the data relates to in the real world, that might help us to understand.

3

Well, I don't know how to explain it much better, but I will try.

I want to have a variable number of signals that are stored in the influx database, in my case between 1 and 10 .These data come out of the influx request node as you can see, in the example picture that I posted before. If it is one signal that I ask, then only msg.payload[0].mean, will come out of the node. But if I request 10 signals, then the objects will contain (besides the msg.payload[0].time attribute of course), msg.payload[0].mean and msg.payload[0].mean_1 and msg.payload[0].mean_2 and etc etc.... until a maximum of 9, in my case. I need this data to be redirected to a chart node.

I know how many signals that I asked for so I can make the data object that fits the need of the chart node. But I want to prepare this 'chart-object' in a loop so I must put an index variable on this number following 'mean', in order to make this object for the chart node.

I can make a rather heavy case statement on this, if nothing else works, but I like to make this more short in code in a loop. I hope it is more clear to you now, what I am trying to achieve, @Colin.

4

Rather than a case statement which would be rather fragile. Try using Object.values(msg.payload[0]) to return the data as an array and then walk the array elements which you can do in various ways - I tend to use a forEach function and would then check for an element that contains a string and either capture that for use or simply ignore depending on need. Then all of the other elements can be processed.

Incidentally, you could also capture the keys as an array if you needed to be able to include those in your processing.

Converting to/from objects/arrays is a common use-case in JavaScript and is well worth taking some time to understand.

5

@TotallyInformation , Well that looks promising. I will study that possibility. Most of it sounds like Chinese to me now :smile:, but maybe later it will get clear to me what you mean. I will let you know the result.

6

An alternative (which I think is the way I would do it) is the use Object.keys() which will give you the key names.

7

Chinese is easier since you can get your browser to translate it for you!! :smiley: For JavaScript, try Object.values() - JavaScript | MDN (mozilla.org)

That means that you still need to reference the object via its keys, with Object.values, you can process them directly. I would use keys if I had a lot of an object I needed to ignore but values in a case like this where only the first element needs to be skipped.

1 Like
8

That is interesting. I will have to code the two options up and see how they come out.

9

@henkkas are you trying to build a structure like this from a set of data read from the db, for sending the chart?

[{
"series": ["mean", "mean_1", "mean_2"],
"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": [""]
}]
10

@Colin @TotallyInformation I must say keys has no value for me, mean and mean_1 etc..... is just the standard output of the influx request node. I am interested in the values, and I know where they are in the attributes, regardless of the number of attributes. So I am well on my way now. But maybe in another case I might be interested in the keys.

11

What Colin meant was:

const mydata = msg.payload[0]
Object.keys(mydata).forEach( key => {
  console.log( `data for key ${key}: `, mydata[key] )
  if ( key.startsWith('mean') ) {
    // ....
  }
})

With values:

Object.values(msg.payload[0]).forEach( (el, i) => {
  if ( i === 0 ) return // ignore the date

  // ... el contains the "current" mean value to process

})
12

@Colin @TotallyInformation I think, I almost got it, but not quit right, yet

With a fixed number of signals (example = 2) my loop looks like this:

for (let i = 0; i < msg.payload.length; i++) {
    let thetime = Number(msg.payload[i].time);
    let room_pv = (msg.payload[i].mean || 0).toFixed(1);
    let ac01_sp = msg.payload[i].mean_1 || 0;
    data0.push({ x: thetime, y: room_pv });
    data1.push({ x: thetime, y: ac01_sp });
}

The new loop looks now like this:

let data = [];
for (i=0; i < msg.payload.length; i++) {
    let thetime = Number(msg.payload[i].time);
    let arr = Object.values(msg.payload[i]); 
    for (nr = 0; nr < numberOfPlugs; nr++) {
        data[nr] = [];
        data[nr][i] = { x: thetime, y: arr[nr+1] }; 
    }
}

The array structure appears, but the objects don't get copied there. I don't understand why

afbeelding
afbeelding401Ă—569 4.83 KB

13

See my examples above.

14

@Colin @TotallyInformation I got it !!! :grinning: :grinning:

let data = [];
let arr = [];
for (i = 0; i < msg.payload.length; i++) {
    arr =[];
    let thetime = Number(msg.payload[i].time);
    arr.push(thetime);
    Object.values(msg.payload[i]).forEach((element, j) => {
        if (j === 0) return; // ignore the date
        arr.push(element)
        })
    data.push(arr);
}    

node.warn(data);

Thanks very much, guys!!!

15

Are you sure that the date will always be the first one? Also don't you need to know which of the elements is mean, which is mean_1 and so on?

16

@Colin Yes, I am trying to get that structure. Labels and series is no problem, I am struggling with the data part. When I put in a query like this

"SELECT MEAN(S3),MEAN(S1),MEAN(S2) FROM myMeasurement WHERE time >= '2022-12-18T04:06:37.000Z' AND time <= '2022-12-18T08:06:37.000Z' GROUP BY time(40s) fill(none)"

into the influxDB request node. I get an output with 'mean' (=S3). mean_1 (=S1) and mean_2 (=s2).
So always in the same sequential order as I put it in the query.

The output structure of the request node then looks like this:

[   {"time": 1504029632890,
     "mean": 2,
     "mean_1": 6,
     "mean_2": 3
    }
   {"time": 1504022335690,
     "mean": 2,
     "mean_1": 6,
     "mean_2": 3
    }
   {"time": 1504022335690,
     "mean": 2,
     "mean_1": 6,
     "mean_2": 3
    }

etc.....
]

So I have to convert this data, to the 'data-part' of the structure you mentioned before.

Yesterday I was in a hurry and I thought the output was ok, because I saw the objects in the array. But the array structure it self is not ok.

Now I am this far with my routine, but I am trying something that turns out error for some reason.
I am trying to appoint the object to a 2-dimensional array, with two types of instructions that both fail. I commented these instructions.

let data = [[]];
let arr = [];
let obj = {};
for (i = 0; i < msg.payload.length; i++) {
    let thetime = Number(msg.payload[i].time);
    Object.values(msg.payload[i]).forEach((element, j) => {
        if (j === 0) return; // ignore the date
        obj.x = thetime;
        obj.y = element;
//        data[i][j-1].push(obj);
//        data[i][j-1] = obj;
    })
}    

node.warn(data);

I get the following errors:

"TypeError: Cannot read properties of undefined (reading 'push')"
or
"TypeError: Cannot set properties of undefined (setting '0')"

Basically I have the same problem, like I had before, with my earlier routine. No errors but just didn't work.

let data = [];
for (i=0; i < msg.payload.length; i++) {
    let thetime = Number(msg.payload[i].time);
    let arr = Object.values(msg.payload[i]); 
    for (nr = 0; nr < numberOfPlugs; nr++) {
        data[nr] = [];
        data[nr][i] = { x: thetime, y: arr[nr+1] }; 
    }
}

Is there a way to do this right?

17

i would find the keys for means, then use them to loop through to create the data arrays
e.g.

[{"id":"6c1ddc2ef2fdc716","type":"inject","z":"da8a6ef0b3c9a5c8","name":"data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"time\":1504029632890,\"mean\":2,\"mean_1\":6,\"mean_2\":3},{\"time\":1504022335690,\"mean\":2,\"mean_1\":6,\"mean_2\":3},{\"time\":1504022335690,\"mean\":2,\"mean_1\":6,\"mean_2\":3}]","payloadType":"json","x":170,"y":880,"wires":[["535b6ab94712f30a"]]},{"id":"535b6ab94712f30a","type":"function","z":"da8a6ef0b3c9a5c8","name":"function 20","func":"const series = Object.keys(msg.payload[0]) ?? [];\nseries.splice(series.indexOf(\"time\"),1);\nconst data = [];\nseries.forEach((prop, index) =>{\n    data[index] = [];\n    msg.payload.forEach(obj => {\n        data[index].push({x: obj.time, y: obj[prop]})\n    })\n})\nmsg.payload = [\n    {\n        series: series,\n        data: data,\n        labels: series\n    }\n]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":310,"y":880,"wires":[["7462b8b0fcd05535"]]},{"id":"7462b8b0fcd05535","type":"debug","z":"da8a6ef0b3c9a5c8","name":"debug 115","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":490,"y":880,"wires":[]}]

function

const series = Object.keys(msg.payload[0]) ?? [];
series.splice(series.indexOf("time"),1);
const data = [];
series.forEach((prop, index) =>{
    data[index] = [];
    msg.payload.forEach(obj => {
        data[index].push({x: obj.time, y: obj[prop]})
    })
})
msg.payload = [
    {
        series: series,
        data: data,
        labels: series
    }
]
return msg;

[edit] fixed typo's

18

@E1cid Thanks for your reaction. Can you explain what this line means?
I think I can understand the rest of it then also.

19

Set series to the keys of msg.payload[0] if the result is nullish set it to [ ].

20

Ok, thanks very much. As I understand it, it is an even better way then logical OR (||).
Maybe I should better use ?? instead of || everywhere I use this?

1 Like