I need help again, problem with Object array with 3 values into a function node

Hi,

What am I doing wrong, I have wasted hours trying to find out !

I only want the object array with 3 values to go into my database once.


I have an object array with 3 values

Object Array



I also have another object with 1 value.
batt



Image No1 works but I get the same data 3 times in my database.

Image No2 works I get the data only once in my database ...
BUT i get this error in the debug sidebar... TypeError: Cannot read properties of undefined (reading 'temperature_01')



one


\


Arrays


The code in the function node that sends data to my database is


let device          = "IP92";                                                               // ENTER HERE ...  the IP address.
let location        = "PC room";                                                           // ENTER HERE ... Location

let new_date        = new Date();                                                           // Does NOT change with time zones across the world.
let iso             = new Date().toISOString();                                             // RFC 3339 format ... for Grafana etc.
let timestamp       = Math.round(new_date.getTime() / 1000);                                // getTime = milliseconds ... this removes last 3 digits to change to seconds ... for Grafana etc.

var temperature     = msg.payload.data.temperature_01;
var humidity        = msg.payload.data.humidity_01;
var battery         = msg.payload.data.battery;


let   payload =
      { 
          humidity:     humidity,                                                           // ENTER HERE ... any new items to add to database, in this section.
          iso:          iso,
          location:     location,      
          temperature:  temperature,
          timestamp:    timestamp,
          battery:      battery
      };


payload = JSON.stringify(payload);                                                          // Converts a JavaScript value to a JSON string

const Data_sequence = `( device,device_data) values('${device}','${payload}')`              // This is the actual data.
const topic = `INSERT INTO  energy ${Data_sequence}`                                        // The insert string to send to database.

msg.topic     = topic;
msg.payload   = "";

return msg;

So many things wrong, where to start!

This is an object with properties. An array is something else.

image

You should put them in series switch (check has key) -> switch (has other key) -> switch (has other key) -> link

This is NOT a safe way to insert data into a database. you should use prepared statements. Assuming mysql (you didnt say), the readme has info on how to do prepared queries

No 1 gives an output on ALL three has key selections because they are all valid. Just go with Option 2

No 2 Are you also sending in the battery_voltage msg, because this does not have a temperature_01 property? If so you can (hopefully) get rid of the error message by adding a check that the data exists

if (!Object.hasOwn(msg.payload, 'data') {
  return
}

let temperature     = msg.payload.data.temperature_01
let humidity        = msg.payload.data.humidity_01
let battery         = msg.payload.data.battery

I am assuming that every msg received has all three properties? If not add a check for each property in the function

Hi Steve and Buckskin,

Thanks for that, when I read that Steve, I thought I was being good for once getting the object correct :roll_eyes:
No problem, that had been changed on my flow to read Object with properties.

I did try your suggested way and it does work, but say I want 10 values, it gets to be a long flow :upside_down_face:

This is NOT a safe way to insert data into a database.

The output is going to a SQLite database, I will have to look at using prepared statements.
But it may also go into a PostgreSQL one at some point.

Buckskin I also tried your way and again that worked with no errors. :grinning:
and it is compact and easily expandable.

I am assuming that every msg received has all three properties?

Yes

But also there is a single object with battery_voltage and I may be wrong, but was that causing the error ?

I must admit I do not understand the

if (!Object.hasOwn(msg.payload, 'data') {
return
}


Can you give a quick idea of what it actually does, as I always learn by my mistakes and then document it for the future :+1:



I went to JSON objects thinking it would make my code easier, but it seems to have made it much harder to understand, if like me you don't know the correct terminology, to do a search on the internet.

Thanks so far guys for your help much appreciated, if you or anyone else has any thoughts on improving this please tell me, as I always thought JSON objects were more elegant to use and my code and flows looks a bit of a mess and not easy to understand.

Thanks

I know & understand this fully & appreciate you are making efforts :+1:

To help a little more, JSON Object is not really a thing.

JSON is the string representation of a JavaScript Object.

It stands for JavaScript Object Notation

Using objects can be very useful and provide a means of future extensibility. Keep at it. You will "get it"

Hi Steve thanks,
Your few words have clarified about the JSON object that I am still using the wrong term.
JSON is the string representation of a JavaScript Object. :+1:

Any quick pointers on this as I have looked briefly online and it does look very complicated, or in my case do I not need to as much, as the messages are coming from my own Arduino module and i wrote the code ?

As I have got older I realise i learn much quicker with visual images than with lots of text.
Your answer with added text on my No1 image helped me understand easily.

Thanks

From the docs:

When using Via msg.topic, parameters can be passed in the query using a msg.payload array. Ex:

msg.topic = `INSERT INTO user_table (name, surname) VALUES ($name, $surname)`
msg.payload = ["John", "Smith"]
return msg;

That is true yes however, if you practice best practice, you will learn best practice. Also, building these queries are often easier while also being secure by default.

1 Like

Thanks to both of you, you gave me enough info to make changes to my Arduino code and solve the issues I had and this is now how I imagined JSON to work. :slightly_smiling_face:

I change the battery code sent from the Arduino and added a battery ( object ? ) with a voltage property.
Steve I honestly mean this, if I am using any descriptions wrong still please let me know.

So now the DHT switch node just looks for data and then the other battery switch node looks for battery

I now I have no error codes and even have data in my database !


03


02

1 Like

Perfect.

It really is worth learning the correct terms, it helps with searching and explaining issues.

Here are a few more tips I hope you find helpful:

  • You can use typeof myvar to find the type of a thing
    • e.g. node.warn({ typeofpayload: typeof msg.payload })
  • Objects have Properties
    • You can access them using . i.e: myObject.property1
    • You can access them using [] i.e: myObject["property1"]
  • Arrays have Elements
    • The first element is 0
    • You can test how many elements it has using myArray.length
    • You can check it is an array using Array.isArray(myArray)

Objects

// below is a CONSTANT object with 3 properties
const myObj =  {
   property1: 1,
   property2: "two"
   property3: [1,1,1]
}

// Constant means you cannot change the type
myObj = "hello" // ERROR!

// But you can change its properties
myObj.property1 = "one" // changed from a number 1 to a string "one"

// And you can add ...
myObj.property4 = "I am number four"

// ... and remove properties
delete myObj.property4

Arrays

// create an empty array
const myArray = []

// create an empty with stuff
const myArrayWithStuff = [1, "two", [1,1,1]]

// you can add things to the end
myArrayWithStuff.push('i am number four')

// or the beginning
myArrayWithStuff.unshift('element 0')

// you can pop things off the end
let lastEl = myArrayWithStuff.pop()


// you can remove & grab the first element
let firstEl = myArrayWithStuff.shift()

// and you can clear it 
myArrayWithStuff.length = 0

// you can test a variable to see if it is an array
if(Array.isArray(myArray) {
  // yup, its an array
}


It is good practice to check your types

let n = "I am supposed to be a number but a bug in my flows made me a string"
if (typeof n !== 'number') {
   n = Number(n)
   if (isNaN(n)) {
     throw new Error('it is Not a Number')
   }
}


One last gotcha - do not rely on typeof without extra checks

For example:

const nObj = null
let prop1
if (typeof nObj  === 'object') {
   nProp1 = nObj.property1 // ERROR: Uncaught TypeError: Cannot read properties of null (reading 'property1')
}

// leys try again
const nObj = null
let prop1
// if nObj is _something_ AND it is an object... 
if (nObj && typeof nObj  === 'object') {
   nProp1 = nObj.property1
}

and the kicker:

const myArray = []
let element0
if (typeof myArray === 'object') {
     element0 = myArray[0] // How did we get here!!!
     // myArray is an object?
     // YES, arrays are objects. 
     // You need to use Array.isArray(myArray) to check them
}

// Lets try again
const myArray = []
let element0
// If it IS an array AND it has something in it, then you can access its elements
if (Array.isArray(myArray) && myArray.length > 0) {
    element0 = myArray[0] // Only access element if we know there is one
}

:point_up_2: All untested and hand written, possible blugs lurk :wink:

Bookmarking this . Excellent Information. Thank You. :people_hugging:

1 Like

Thanks Steve a great help and a lot to read there, but I will soon, as I get a bit more time.

I am celebrating ha ha ... a few minutes ago I got Grafana to display Battery and Also DHT temperature in separate graphs from SQLite , still much more to do :+1:

  • But at least I now have, Arduino sending out JSON string ( I nearly typed object, but looked online to check ).

  • Which via Node-RED goes to SQLite and then to Grafana :grinning:


Thanks for your help and everyone else that has got me this far.

No doubt I will be back for more advice in the not too distant future :upside_down_face:

1 Like

If you didn't get this fixed perhaps you might try moving the task to the stored procedure. MySql offers an "insert or update" and "insert or ignore" option. If you have a potential duplicate entry it will just discard it.

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