# Convert InfluxDB v2 data to ui\_chart

**URL:** <https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088>\
**Category:** Share Your Nodes\
**Created:** [5 March 2021 12:52 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088 "2021-03-05T12:52:49Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Simon01011](https://avatars.discourse-cdn.com/v4/letter/s/e68b1a/32.png) [@Simon01011](https://discourse.nodered.org/u/Simon01011)\
**Post date:** [5 March 2021 12:52 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/1 "2021-03-05T12:52:49Z")

</div>

Hi everybody!

Since I didn't find anything here I wrote a small function to display Influx data on a chart:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/b/2bc061f672dae804cd038cad2fa2103ca6cb3c5a.png)  
The function for the query prepares the query string:

```auto
msg.query = 'from(bucket: "testbucket")'+
' |> range(start: -1h)' +
' |> filter(fn: (r) => r._measurement == "system" and (r._field == "load1" or r._field == "load5" or r._field == "load15"))';
return msg;

```

This queries the system load for the last hour (collected from a Telegraf agent). The response from the DB is processed in the function node "Conversion":

```auto
// helper
// used to get all distinct values from an array
const distinct = (value, index, self) => {
    return self.indexOf(value) === index;
}

// main code

// first, get all fields of the query result
const fields = msg.payload.map(x => x._field);
// get all distinct fields
const distinctfields = fields.filter(distinct);

// get all timestamps
const timestamps = msg.payload.map(x => Date.parse(x._time));
// get all values
const values = msg.payload.map(x => x._value);

// loop over all distinct fields
var ind;
var data = [];
var arr = []
for (var n = 0; n < distinctfields.length; n++) { 
    // get indices of matching entries for current field
    ind = [];
    for (var m = 0; m < fields.length; m++) {
        if (fields[m] === distinctfields[n]) {
            ind.push(m);
        }
    }

    // get matching timestamps
    var matchingtimestamps = ind.map(x => timestamps[x])
    // get matching values
    var matchingvalues = ind.map(x => values[x])

    // compose array
    arr = [];
    for (m = 0; m < matchingtimestamps.length; m++) { 
        arr.push({'x': matchingtimestamps[m], 'y': matchingvalues[m]});
    }
    
    // save array
    data.push(arr);
}

const payload = [{"series": distinctfields, "data": data, "labels": distinctfields}];

msg.payload = payload;

return msg;

```

The most important thing (for me) is: it doesn't matter how many fields you have in your querry, each field will be extracted from the response and shown as a separate line.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/4/04b7a431135f294515c63cff5f5acd9f7b7fab75.png)

Maybe this piece of code is helpful for someone 😃

---

<div class="post-metadata">

**Author:** ![malaxy](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/malaxy/32/39791_2.png) [@malaxy](https://discourse.nodered.org/u/malaxy)\
**Post date:** [8 April 2021 05:29 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/2 "2021-04-08T05:29:39Z")

</div>

Hello @Simon01011

Thanks for this Function I would like to use it but have some problems 🙂  
I read [this](https://discourse.nodered.org/t/display-datas-from-influx-in-an-ui-chart/38579) Post about data manupilation and also read the [Documentation](https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data) from the Chart Element.

If I try to use your function with CopyPaste Node Red sad that it didn't no the Function Map

```auto
TypeError: msg.payload.map is not a function

```

Here are some Screenshots  
 ![NodeRed DummyData Plot](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/7/779bbd6292111f1ff1c5f7674223cc0568cdcf6b.png)

 ![NodeRed Flow Influx](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/5/05e2d85b5c147a40f6fd0a48ab5895c931219c23.png)

PS. There is also a Timeshift from 2h between the Data and the graph 🙂

Thanks a lot in advance for your help.

---

<div class="post-metadata">

**Author:** ![Simon01011](https://avatars.discourse-cdn.com/v4/letter/s/e68b1a/32.png) [@Simon01011](https://discourse.nodered.org/u/Simon01011)\
**Post date:** [8 April 2021 07:18 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/3 "2021-04-08T07:18:01Z")

</div>

Hi malaxy!

As the title says it is for Influx v2. As I see from your screenshot you are using v1.8. It seems the msg.payload is different from the one the function expects...

THe time shift may come from the difference between local time and UTC, but I don't know if you store the wrong time in the DB or if something goes wrong with your query. You should check that.

---

<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:** [8 April 2021 10:36 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/4 "2021-04-08T10:36:08Z")

</div>

For the timeshift problem, two questions

1. In your debug you show timestamps like  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/e/1eb6e170f5078da9cc1ebf11286523eb5944a1cd.png)  
Is that the correct UTC time (it should not be local time).

2. Is the time zone set correctly on the machine running the browser?

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [9 April 2021 13:30 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/5 "2021-04-09T13:30:26Z")

</div>

Hi Simon

Well done... this made my life a lot easier.  
Managed to retrieve 5 series from InfluxDB cloud

Only 1 issue... everytime I refresh the series swop around and come back in a different order.  
Makes it a bit difficult to keep track of colours, etc.

Is there a way to ensure that the series load in the same order every time?  
I tried looking at your function but not that good with Jave.

Thanks  
Tj

---

<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:** [9 April 2021 14:35 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/6 "2021-04-09T14:35:51Z")

</div>

If you configure a debug node to fire on startup and make that trigger a sequence of messages, one for each topic that there will be, then that will decide the order so it will always be the same. Then you can set the colours up in the chart node.

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [10 April 2021 12:18 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/7 "2021-04-10T12:18:20Z")

</div>

Thanks for that Colin... when i do that I only get the last series on the graph  
I also use the buttons to select different time frames by altering the \_start in the query

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/a/6a3300a676c3c625c219d1029f8ebbc5ef80e5d3.png)

---

<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:** [10 April 2021 12:38 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/8 "2021-04-10T12:38:25Z")

</div>

> [@TjObie](#):
>
> when i do that I only get the last series on the graph

I have no idea what you mean by that, and I don't see an inject node sending the initial values in.  
However, if you are only using this for historical data then all you have to do is make sure that the channels are always ordered the same when you load the data into the chart.

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [10 April 2021 14:05 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/9 "2021-04-10T14:05:29Z")

</div>

I use the buttons to get different timeframes and the same happens. Order is the same in all the queries but graph draws in different order every time. From same button of from diff button...

msg.query = 'from(bucket: "myData")'+  
' |\> range(start: -2m)' +  
' |\> filter(fn: (r) =\> r.\_measurement == "power\_watts" and (r.\_field == "PV\_Watts" or r.\_field =="Battery\_Watts" or r.\_field == "Load\_Watts" or r.\_field == "SOC" or r.\_field == "Utility\_Watts"))';  
return msg;

If I add the injector  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/5/e5f74b77abdb7508879ff9b0e3df639c86caa87d.png)

with three queries in order PV\_watts, Battery\_Watts and Load\_Watts only the  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/c/fc672d35a658c403d1fdacaf43487921ceff8496.png)

only last query appears on the graph  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/4/04d0b7ed91793b4de23b59e0de1bead38203d998.png)

---

<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:** [10 April 2021 14:26 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/10 "2021-04-10T14:26:46Z")

</div>

The important thing is what is being passed to the chart node. You need to put a debug node showing that. Then make sure that each button has the lines in the same order.

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [10 April 2021 14:35 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/11 "2021-04-10T14:35:36Z")

</div>

I have done that...  
send 1st time  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/3/83015076e572f6d40c9bedfed8fb14531823d9a0.png)

send again  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/2/826ec97dbdad0e37c93ddbd82bab65c81b97a8f6.png)

I think it is the way that the Extract series data interoperate the data... it scrambles it every time  
I can see what goes in and then what 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:** [10 April 2021 14:54 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/12 "2021-04-10T14:54:32Z")

</div>

s you can see they are in different orders. What does the data coming out of the influx node look like?

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [10 April 2021 15:04 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/13 "2021-04-10T15:04:23Z")

</div>

You right... it goes into the InfluxDB Node as  
Load, Battery, PV, Utility, SOC

Then comes back as  
Battery, SOC, Load, PV, Utility

And then out off the Extractor same as input  
Battery, SOC, Load, PV, Utility

It looks like the Influx DB node scrambles it...

---

<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:** [10 April 2021 15:05 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/14 "2021-04-10T15:05:04Z")

</div>

Show us what looks like coming out of influx.

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [10 April 2021 15:14 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/15 "2021-04-10T15:14:11Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/a/bab5f5acf580023ab9238d6d25c4e422a08c19b6.png)

then opening ... I can see the order

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/d/9d6b677f4127b1a61beea236351db694f60d99ea.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/1/91f5bf8b83ef403eb4652494010a0ef3e1584208.png)

---

<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:** [10 April 2021 15:35 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/16 "2021-04-10T15:35:59Z")

</div>

The data coming from influx is a javascript object. You can't rely on the order of properties in an object. In the function that converts that to the chart data you will have to sort them in some way. The best way to do that will depend on how you have structured the function. If you are using `Object.Entries()` (which is good way of doing it) then you could sort the entries as shown in this link before using them to extract the data. [Object.entries() - JavaScript | MDN](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Object/entries)

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [10 April 2021 15:39 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/17 "2021-04-10T15:39:52Z")

</div>

Thanks Colin

Only just started out with Java and Node Red but will give it a bash...  
All part of the learning curve...

---

<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:** [10 April 2021 16:01 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/18 "2021-04-10T16:01:43Z")

</div>

> [@TjObie](#):
>
> Only just started out with Java

It is javascript, not java. I thought I should mention it as if you start looking at java docs you will get very confused. The two languages are not related and whoever decided to give them similar names has got a lot to answer for.

---

<div class="post-metadata">

**Author:** ![TjObie](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/tjobie/32/38776_2.png) [@TjObie](https://discourse.nodered.org/u/TjObie)\
**Post date:** [11 April 2021 07:21 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/19 "2021-04-11T07:21:15Z")

</div>

Thanks for clearing that up.  
I was getting confused with the java and javascript. No wonder nothing worked...  
I read up on the javascript programming on w3school website last night and just added a  
sort in the extract node.  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/0/a0a38b7f069254b86b8b2fbcd36a5c16c38795e4.png)

Problem solved.  
I don't mind in what order the series came back as long as it was consistent.  
I can now set legend colours and it consistent over the time frames.

Many thanks for your help.  
Tj

---

<div class="post-metadata">

**Author:** ![chrisn-au](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chrisn-au/32/25504_2.png) [@chrisn-au](https://discourse.nodered.org/u/chrisn-au)\
**Post date:** [11 April 2021 07:40 UTC](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088/20 "2021-04-11T07:40:51Z")

</div>

TJ,

great to see you have it sorted and using the node-red dashboard. I understand using the dashboard to visualise the data but have you looked at using Grafana as well. It is awesome for visualising time series data

[Next page](https://discourse.nodered.org/t/convert-influxdb-v2-data-to-ui-chart/42088.md?page=2)