Display 2 extracts from influxdb in an UI-Chart

[JungeG]

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

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

With the Chart node I can display each object separately - without any problem

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

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

Thanks Georg

[E1cid]

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

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

[zenofmud]

which means @JungeG will have to join the two msgs

[JungeG]

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

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

the new chart looks as expected:

now you can switch between the data:

Hope this is a clear explanation.
Thanks again
Georg

[Colin]

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

[JungeG]

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

[Colin]

What are the fields and tags in those two measurements?

[Colin]

I was going to suggest rearranging your measurments, and this may still be worthwile, but looking at the docs for the select statement I see you can do

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

[JungeG]

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:

and the select on EN01_Power:

and the select on EN01_Power, EN01_PowerShift:

Only the EN01_Power values are selected.
Georg

[Colin]

You have missed the _ from the measurement name

[JungeG]

You are right - my fault:

but the extract in node-red ist still only the EN01_Power values.
Georg

[Colin]

@JungeG

the extract in node-red ist still only the EN01_Power values

How are you doing the extract in node-red?

[Colin]

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.

[JungeG]

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

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:

the chart looks like:

the lines are not drawn - if I use only one serie, var jsondata = JSON.parse(data0); it is displayed correctly:

Any idea?
Thanks Georg

[Colin]

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

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

[JungeG]

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:

which is one array less.
Thanks for you help
Georg