# Reading data from influx with an index on mean

**URL:** <https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351>\
**Category:** General\
**Created:** [17 December 2022 12:40 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351 "2022-12-17T12:40:10Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [17 December 2022 12:40 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/1 "2022-12-17T12:40:10Z")

</div>

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

![afbeelding](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/8/a87b4ab88c8bd1841a9c30a6d13726b1e7bd99d8.png)

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?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 December 2022 13:21 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/2 "2022-12-17T13:21:48Z")

</div>

> [@henkkas](#):
>
> I need to have an index on `mean_<index>`. So how can I do this

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.

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [17 December 2022 13:41 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/3 "2022-12-17T13:41:20Z")

</div>

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.

---

<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:** [17 December 2022 13:49 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/4 "2022-12-17T13:49:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [17 December 2022 13:54 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/5 "2022-12-17T13:54:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 December 2022 13:57 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/6 "2022-12-17T13:57:43Z")

</div>

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

---

<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:** [17 December 2022 14:01 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/7 "2022-12-17T14:01:21Z")

</div>

> [@henkkas](#):
>
> Most of it sounds like Chinese to me now 😄, but maybe later it will get clear to me what you mean. I will let you know the result.

Chinese is easier since you can get your browser to translate it for you!! 😃 For JavaScript, try [Object.values() - JavaScript | MDN (mozilla.org)](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_objects/Object/values)

> [@Colin](#):
>
> An alternative (which I think is the way I would do it) is the use `Object.keys()` which will give you the key names.

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 December 2022 14:03 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/8 "2022-12-17T14:03:17Z")

</div>

> [@TotallyInformation](#):
>
> 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.

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 December 2022 14:09 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/9 "2022-12-17T14:09:55Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [17 December 2022 14:30 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/10 "2022-12-17T14:30:34Z")

</div>

@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.

---

<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:** [17 December 2022 15:11 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/11 "2022-12-17T15:11:42Z")

</div>

> [@henkkas](#):
>
> I must say `keys` has no value for me

What Colin meant was:

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

```

With values:

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

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

})

```

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [17 December 2022 15:15 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/12 "2022-12-17T15:15:10Z")

</div>

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

```auto
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:

```auto
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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/f/df1f562b93e58f97c4efc8ad94c7a585fe8f42fc.png)

---

<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:** [17 December 2022 15:15 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/13 "2022-12-17T15:15:32Z")

</div>

See my examples above.

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [17 December 2022 15:50 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/14 "2022-12-17T15:50:31Z")

</div>

@Colin @TotallyInformation I got it !!! 😀 😀

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [17 December 2022 16:08 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/15 "2022-12-17T16:08:22Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [18 December 2022 08:44 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/16 "2022-12-18T08:44:37Z")

</div>

> [@Colin](#):
>
> @henkkas are you trying to build a structure like this from a set of data read from the db, for sending the chart?
> 
> ```auto
> [{
> "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": [""]
> }]
> 
> ```

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

```auto
[ {"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.

```auto
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.

```auto
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?

---

<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:** [18 December 2022 09:28 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/17 "2022-12-18T09:28:34Z")

</div>

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

```auto
[{"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

```auto
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

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [18 December 2022 09:41 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/18 "2022-12-18T09:41:52Z")

</div>

> [@E1cid](#):
>
> `const series = Object.keys(msg.payload[0]) ?? [];`

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

---

<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:** [18 December 2022 09:47 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/19 "2022-12-18T09:47:33Z")

</div>

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

> **[Nullish coalescing operator (??) - JavaScript | MDN](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Nullish_coalescing)**
>
> The nullish coalescing (??) operator is a logical
> operator that returns its right-hand side operand when its left-hand side operand is
> null or undefined, and otherwise returns its left-hand side
> operand.

---

<div class="post-metadata">

**Author:** ![henkkas](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/henkkas/32/39844_2.png) [@henkkas](https://discourse.nodered.org/u/henkkas)\
**Post date:** [18 December 2022 09:51 UTC](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351/20 "2022-12-18T09:51:47Z")

</div>

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?

[Next page](https://discourse.nodered.org/t/reading-data-from-influx-with-an-index-on-mean/72351.md?page=2)