How to store fields of arrays into InfluxDB using batch node? What formating function should I need?

General
1

I have the followiing payload with fields of arrays:

{"timestamp":1686127509414,
"topic":"NodeID;ns=3;s=\"V20Reg\".\"Pos\"",
"payload":[{"measurement":"V20RegPos",
"fields":[
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":8,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":419,"stVisu":2,"batchNbr":3,"key":129,"stPlate":0,"note":0},
{"rcpNbr":419,"stVisu":2,"batchNbr":3,"key":129,"stPlate":0,"note":0},
{"rcpNbr":419,"stVisu":2,"batchNbr":3,"key":129,"stPlate":0,"note":0},
{"rcpNbr":419,"stVisu":6,"batchNbr":3,"key":128,"stPlate":0,"note":0},
{"rcpNbr":419,"stVisu":6,"batchNbr":3,"key":127,"stPlate":0,"note":0},
{"rcpNbr":419,"stVisu":6,"batchNbr":3,"key":127,"stPlate":0,"note":0},
.
.
.
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0},
{"rcpNbr":0,"stVisu":0,"batchNbr":0,"key":0,"stPlate":0,"note":0}],
"timestamp":1686127509414000000}],
"_msgid":"517dfd98f7dc934c",
"measurement":"V20RegPos",
"statusCode":{"value":0},
"serverTimestamp":"2023-06-07T08:45:09.378Z",
"sourceTimestamp":"2023-06-07T08:45:09.378Z"}

When I try to parse the above payload through the InfluxDB Batch node, I get the following error from influxDB batch node:

Error: A 400 Bad Request error occurred: 
{"error":"unable to parse 'V20RegPos 
0=[object Object],1=[object Object],
10=[object Object],100=[object Object],
101=[object Object],102=[object Object],
103=[object Object],104=[object Object],
105=[object Object],106=[object Object],
107=[object Object],108=[object Object],
109=[object Object],11=[object Object],
110=[object Object],111=[object Object],
112=[object Object],113=[object Object],
114=[object Object],115=[object Object],
116=[object Object],117=[object Object],
118=[object Object],119=[object Object],
12=[object Object],120=[object Object],
121=[object Object],122=[object Object],
123=[object Object],124=[object Object],
125=[object Object],126=[object Object],
127=[object Object],128=[object Object],
129=[object Object],13=[object Object],
130=[object Object],131=[object Object],
132=[object Object],133=[object Object],
.
.
.
89=[object Object],9=[object Object],
90=[object Object],91=[object Object],
92=[object Object],93=[object Object],
94=[object Object],95=[object Object],
96=[object Object],97=[object Object],
98=[object Object],99=[object Object] 
1686129685511000000': invalid boolean"}

My Data formating Function:

var timestamp = msg.timestamp*1e6; //new Date().getTime() *1e6;
if (typeof msg.payload === "object"){ //if measurement/payload is a struct/object
msg.payload = [
    {
        measurement: msg.measurement,
        fields: msg.payload,
        timestamp: timestamp
    }
]
}
else{ // if the measurement/payload is singler var
    msg.payload = [
    {
        measurement: msg.measurement,
        fields: {value: msg.payload},
        timestamp: timestamp
    }
]
}

return msg;

My NodeRed Flow:

image
image1143Ă—218 17.8 KB

Any Idea, what am I doing wrong here?

2

The fields property has to be an object of your field values, you are sending a single point with fields as an array. That won't work.

You need to create a point for each element of your array in msg.payload.

like that (untested)

msg.payload = msg.payload.map(el => {
    return {
        measurement: msg.measurement,
        fields: el,
        timestamp: timestamp
    }
});

It uses the Array.map() function to convert your payload array into an array of Influx points.

1 Like
3

Then InfluxDB is storing only the last element/index per batch/timestamp, I want to store all the indeces values per timestamp. How can I do it?
The structure of data stored in InfluxDB:
image

4

Ah, right. Points with the same timestamp will be overwritten. So you need to add a discriminator to make them unique.

I hope you are familiar with the key concepts of InfluxDB.

So I suggest adding a tag to each point that represents the position.

try this

msg.payload = msg.payload.map((el, idx) => {
    return {
        measurement: msg.measurement,
        tags: {
                position: idx
        },
        fields: el,
        timestamp: timestamp
    }
});
1 Like
5

@kuema Thanks a lot for your code and prompt answers. Now I got all the data with same timestamps inside InfluxDB, but now I don't know if its possible to display the arrays in grafana with its indices (of tags). Any Idea, how I can display this data with tags in grafana in a meaningful way?
Data Structure stored in InfluxDB:
image

6

What does the position in the array mean in real terms?

7

its a station number (or position/coordinate) of a linear machine

8

Ok, so you have everything you need in the db. Exactly what output do you want to get?

9

I want to display all the array elements with its timestamp in grafana, but there the options seemed to be limited with grafana query commands:

image
image1500Ă—815 56.9 KB

Any Idea on how to make an influx query so that I can display all the array elements in order with their timestamp in grafana?

10

Doesn't the previous screenshot you posted show that, apart from the fact that you have not sorted the index tag.

11

Any Idea on how to sort the arrays with their index tag?

12

You are not sorting arrays, you are sorting records.

What do the influx docs say about sorting?

14

I have used the Influx Query with 'Where' Clause as follows:

SELECT distinct("${variabel}") FROM "V20RegPos" WHERE ("position" = '0') AND $timeFilter GROUP BY time($__interval) fill(none)

SELECT distinct("${variabel}") FROM "V20RegPos" WHERE ("position" = '1') AND $timeFilter GROUP BY time($__interval) fill(none)
.
.
.

SELECT distinct("${variabel}") FROM "V20RegPos" WHERE ("position" = '319') AND $timeFilter GROUP BY time($__interval) fill(none)
15

I am away at the moment, but I think it is order-by or similar