# Display 2 extracts from influxdb in an UI-Chart

**URL:** <https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665>\
**Category:** Dashboard\
**Created:** [7 July 2020 14:24 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665 "2020-07-07T14:24:21Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![JungeG](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jungeg/32/13642_2.png) [@JungeG](https://discourse.nodered.org/u/JungeG)\
**Post date:** [7 July 2020 14:24 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/1 "2020-07-07T14:24:21Z")

</div>

Hi!  
I am extracting the power consumption and the power consumption within the last hour from an influxdb.  
the flow looks like:

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

After the extract is formated i get 2 arrays of the object:

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

With the Chart node I can display each object separately - without any problem  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/f/6f51aca33fe65194a1b3b359e6f87b82f997ffbd.png)

I want to combine both charts to be displayed in one chart - the flow should look like:

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

But it shows only the power consumption, since this is the last extract.  
Any idea how to do this?

Thanks Georg

---

<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:** [7 July 2020 14:39 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/2 "2020-07-07T14:39:58Z")

</div>

you need to set the data in this format, this will give a 2 lines on same chart

```auto
[{"series":["A","B"],"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}]],"labels":["a","b"]}]

```

edit remove character form end of array

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 July 2020 15:18 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/3 "2020-07-07T15:18:58Z")

</div>

which means @JungeG will have to `join` the two msgs

---

<div class="post-metadata">

**Author:** ![JungeG](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jungeg/32/13642_2.png) [@JungeG](https://discourse.nodered.org/u/JungeG)\
**Post date:** [7 July 2020 18:26 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/4 "2020-07-07T18:26:03Z")

</div>

Thanks!  
This was the right hint.  
Add a join node and a new function:  
The flow looks like now:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/8/f8870950cd5193021f6dbd260a4853a751b6998a.png)

Results after Join InfluxDB Extract - 2 arrays in one node.  
Reformate the payload using following function:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/5/e514655fc13bd0f2598aab904a5d92eff61a95f0.png)

the new chart looks as expected:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/4/b49cc87fa354ff8417d97d005a66e6d5aaf45020.png)

now you can switch between the data:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/8/18345c4db004dcac61e893ddbe9574d8c2b9b93a.png)

Hope this is a clear explanation.  
Thanks again  
Georg

---

<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:** [7 July 2020 19:17 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/5 "2020-07-07T19:17:10Z")

</div>

Are you sure you can't get both sets of data in one query? What are the queries?

---

<div class="post-metadata">

**Author:** ![JungeG](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jungeg/32/13642_2.png) [@JungeG](https://discourse.nodered.org/u/JungeG)\
**Post date:** [8 July 2020 13:31 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/6 "2020-07-08T13:31:35Z")

</div>

Hi Collin!

The query is just a select \* from the measurement:  
Select 1:

> select \* from EN01\_Power where time \>= now() - 14h

Select 2:

> select \* from EN01\_PowerShift where time \>= now() - 14h

How can I combine them to have only one query?  
Thanks for guidance.  
Georg

---

<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 July 2020 14:22 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/7 "2020-07-08T14:22:27Z")

</div>

What are the fields and tags in those two measurements?

---

<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 July 2020 14:35 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/8 "2020-07-08T14:35:54Z")

</div>

I was going to suggest rearranging your measurments, and this may still be worthwile, but looking at the [docs for the select statement](https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/) I see you can do

`select * from EN01_Power, EN01_PowerShift where time >= now() - 14h`

---

<div class="post-metadata">

**Author:** ![JungeG](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jungeg/32/13642_2.png) [@JungeG](https://discourse.nodered.org/u/JungeG)\
**Post date:** [8 July 2020 15:11 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/9 "2020-07-08T15:11:18Z")

</div>

Thanks Colin,  
but if I re-arrange the select statement - I get one array with just the EN01\_Power data.  
Do I need an into clause?  
The only fields are time and value in both measurements.  
show field keys:

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

and the select on EN01\_Power:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/8/a8a9997e175bf94b3648bc4d21f04f54af13cb60.png)  
and the select on EN01\_Power, EN01\_PowerShift:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/2/32b490af395b783a382dc0d5d7257a0b05c02ea7.png)

Only the EN01\_Power values are selected.  
Georg

---

<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 July 2020 15:30 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/10 "2020-07-08T15:30:00Z")

</div>

You have missed the \_ from the measurement name

---

<div class="post-metadata">

**Author:** ![JungeG](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jungeg/32/13642_2.png) [@JungeG](https://discourse.nodered.org/u/JungeG)\
**Post date:** [8 July 2020 15:50 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/11 "2020-07-08T15:50:09Z")

</div>

You are right - my fault:

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

but the extract in node-red ist still only the EN01\_Power values.  
Georg

---

<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 July 2020 15:51 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/12 "2020-07-08T15:51:16Z")

</div>

> [@JungeG](#):
>
> the extract in node-red ist still only the EN01\_Power values

How are you doing the extract in node-red?

---

<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 July 2020 16:06 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/13 "2020-07-08T16:06:38Z")

</div>

I have just tried with the Influxdb In node with a query  
`select * from m1,m2 limit 10`  
and I get 20 rows with the combined data fields. If the timestamps were the same for some of the samples it might combine them I don't know.

---

<div class="post-metadata">

**Author:** ![JungeG](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jungeg/32/13642_2.png) [@JungeG](https://discourse.nodered.org/u/JungeG)\
**Post date:** [9 July 2020 08:08 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/14 "2020-07-09T08:08:39Z")

</div>

Thanks Colin,  
I have now extracted the data as `Raw Data`:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/e/fee0b4a03102744c8cc696021f30a37379e59089.png)

and formatted the data as follows:

> var series = [msg.payload.results[0].series[0].name, msg.payload.results[0].series[1].name];  
> var labels = [msg.payload.results[0].series[0].name, msg.payload.results[0].series[1].name];
> 
> var data0 = "[[";  
> var thetime0;
> 
> for (var i=0; i \< msg.payload.results[0].series[0].values.length; i++) {  
> thetime0 = (msg.payload.results[0].series[0].values[i][0]); // Some manipulation of the time may be required  
> thetime0 = Date.parse(thetime0);  
> data0 += '{ "x":' + thetime0 + ', "y":' + msg.payload.results[0].series[0].values[i][1] + '}';  
> if (i \< (msg.payload.results[0].series[0].values.length - 1)) {  
> data0 += ","  
> } else {  
> data0 += "]]"  
> }  
> }
> 
> var data1 = "[[";  
> var thetime1;
> 
> for (var j=0; j \< msg.payload.results[0].series[1].values.length; j++) {  
> thetime1 = (msg.payload.results[0].series[1].values[j][0]); // Some manipulation of the time may be required  
> thetime1 = Date.parse(thetime1);  
> data1 += '{ "x":' + thetime1 + ', "y":' + msg.payload.results[0].series[1].values[j][1] + '}';  
> if (j \< (msg.payload.results[0].series[1].values.length - 1)) {  
> data1 += ","  
> } else {  
> data1 += "]]"  
> }  
> }  
> var data = [data0, data1];  
> var jsondata = [JSON.parse(data0), JSON.parse(data1)];  
> //var jsondata = JSON.parse(data1);  
> msg.payload = [{"series": series, "data": jsondata, "labels": labels}];  
> msg.playload = data;  
> return msg;

Get the data in same format as with the earlier solution, but in less steps:

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

the chart looks like:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/9/b92064ed7e66051b5c22eb4587fc976de9b870b6.png)  
the lines are not drawn - if I use only one serie, `var jsondata = JSON.parse(data0);` it is displayed correctly:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/7/8772dfb9f1c6d5cb095b9cb03e7c3eeb347f6e68.png)

Any idea?  
Thanks Georg

---

<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 July 2020 10:27 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/15 "2020-07-09T10:27:11Z")

</div>

That "data" array is not correct. From the docs it should be

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

```

So "data" should be an array, with one element per series and each of those elements should be a simple array of points. You have an array of two elements, which is ok, but you have each of those as an array of 1 element, containing the array of points.

---

<div class="post-metadata">

**Author:** ![JungeG](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jungeg/32/13642_2.png) [@JungeG](https://discourse.nodered.org/u/JungeG)\
**Post date:** [9 July 2020 11:29 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/16 "2020-07-09T11:29:30Z")

</div>

Hi Colin,  
thanks for the hint:  
the problem lies with the double [] during the conversion, if I use single than it is okay.  
So I repost the conversion:

> var series = [msg.payload.results[0].series[0].name, msg.payload.results[0].series[1].name];  
> var labels = [msg.payload.results[0].series[0].name, msg.payload.results[0].series[1].name];
> 
> var data0 = "[";  
> var thetime0;
> 
> for (var i=0; i \< msg.payload.results[0].series[0].values.length; i++) {  
> thetime0 = (msg.payload.results[0].series[0].values[i][0]); // Some manipulation of the time may be required  
> thetime0 = Date.parse(thetime0);  
> data0 += '{ "x":' + thetime0 + ', "y":' + msg.payload.results[0].series[0].values[i][1] + '}';  
> if (i \< (msg.payload.results[0].series[0].values.length - 1)) {  
> data0 += ","  
> } else {  
> data0 += "]"  
> }  
> }
> 
> var data1 = "[";  
> var thetime1;
> 
> for (var j=0; j \< msg.payload.results[0].series[1].values.length; j++) {  
> thetime1 = (msg.payload.results[0].series[1].values[j][0]); // Some manipulation of the time may be required  
> thetime1 = Date.parse(thetime1);  
> data1 += '{ "x":' + thetime1 + ', "y":' + msg.payload.results[0].series[1].values[j][1] + '}';  
> if (j \< (msg.payload.results[0].series[1].values.length - 1)) {  
> data1 += ","  
> } else {  
> data1 += "]"  
> }  
> }  
> var data = [data0, data1];  
> var jsondata = [JSON.parse(data0), JSON.parse(data1)];  
> //var jsondata = JSON.parse(data1);  
> msg.payload = [{"series": series, "data": jsondata, "labels": labels}];  
> msg.playload = data;  
> return msg;

The data section now looks:

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

which is one array less.  
Thanks for you help  
Georg

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [23 July 2020 11:29 UTC](https://discourse.nodered.org/t/display-2-extracts-from-influxdb-in-an-ui-chart/29665/17 "2020-07-23T11:29:38Z")

</div>

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