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

mkdthanga · 7 June 2023 09:33

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:

Any Idea, what am I doing wrong here?

kuema · 7 June 2023 09:57

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.

mkdthanga · 7 June 2023 11:06

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:

kuema · 7 June 2023 11:35

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
    }
});

mkdthanga · 7 June 2023 11:58

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

Colin · 7 June 2023 12:03

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

mkdthanga · 7 June 2023 12:22

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

Colin · 7 June 2023 12:39

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

mkdthanga · 7 June 2023 12:57

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:

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?

Colin · 7 June 2023 13:05

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

mkdthanga · 7 June 2023 13:22

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

Colin · 7 June 2023 13:26

You are not sorting arrays, you are sorting records.

What do the influx docs say about sorting?

mkdthanga · 13 June 2023 11:09

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)

Colin · 18 June 2023 16:12

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

Topic		Replies	Views
Node-red-contrib-influxdb : influxdb batch : not sure about processing an array of data General	2	193	29 March 2023
Array send to InfluxDB General	7	1021	19 August 2022
Input data to Influxdb with dynamic fields General	3	720	13 January 2022
Single to batch influxdb General	7	1373	11 June 2021
Writing data to Indluxdb batch node General	29	1443	18 October 2021

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

Related Topics