Converting/ Rebuilding Array of Objects {different length}

Hey, I get data from MSSQL with this code where the payload is the all entries of the named "key"
This is the SELECT-Code:

The payload I get is an array with all entries of named "key" I selected:

Now i want to rebuild this payload. {not every payload array got same count of entries}
The new payload should be like the following picture (1 key called "key" with the value of it, and 1 key as array called "Signalrauschen (dB)" with all their values of the full payload and finally 1 key as array called "Signalstärke (dBm)" with all their values of the full payload. Finally Payload should look like this:

So I need help to get a function all values from all date.
and once a function to only get the values from now Date().

A function node would work. Pass the msg to a function and just scan it pushing the values where they are needed.

const array1 = [
  {"key": "123", "Signalstärke (dBm)":-109, "Signalrauschen (dB)": 1}, 
  {"key": "123", "Signalstärke (dBm)":-106, "Signalrauschen (dB)": 3.8}, 
  {"key": "123", "Signalstärke (dBm)":-96, "Signalrauschen (dB)": 2.8}, 
  {"key": "123", "Signalstärke (dBm)":-92, "Signalrauschen (dB)": 4.2}
]; //demo data - replace this with const array1 = msg.payload;

var reduced = {key: "", "Signalstärke (dBm)": [], "Signalrauschen (dB)": []};
for(let i = 0; i < array1.length; i++) {
  const el = array1[i];
  reduced.key = el.key;
  reduced["Signalrauschen (dB)"].push(el["Signalrauschen (dB)"]);
  reduced["Signalstärke (dBm)"].push(el["Signalstärke (dBm)"]);

msg.payload = reduced;
return msg;

you can also do this in a change node using the following JSONata expression

   "Signalstärke (dBm)": `Signalstärke (dBm)`,
   "Signalrauschen (dB)": `Signalrauschen (dB)`
[{"id":"84127a3a.646268","type":"inject","z":"5a245aa1.510164","name":"","props":[{"p":"payload.ProgramNumber.value","v":"[{\"key\":\"123\",\"Signalstärke (dBm)\":-109,\"Signalrauschen (dB)\":1},{\"key\":\"123\",\"Signalstärke (dBm)\":-106,\"Signalrauschen (dB)\":3.8},{\"key\":\"123\",\"Signalstärke (dBm)\":-96,\"Signalrauschen (dB)\":2.8},{\"key\":\"123\",\"Signalstärke (dBm)\":-92,\"Signalrauschen (dB)\":4.2}]","vt":"json"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":210,"y":3400,"wires":[["b2aa8e7f.b22818"]]},{"id":"b2aa8e7f.b22818","type":"change","z":"5a245aa1.510164","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.ProgramNumber.value.${\"Signalstärke (dBm)\": `Signalstärke (dBm)`,\"Signalrauschen (dB)\": `Signalrauschen (dB)`}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":430,"y":3420,"wires":[["c354c6a3.09a65"]]},{"id":"c354c6a3.09a65","type":"debug","z":"5a245aa1.510164","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":600,"y":3400,"wires":[]}]

that works pretty well for all date.
is there a way to only move the data into the new payload if the date is from today?

Yeah, only query the database for today's data with a where clause.

how should it work if my [seen] is like Date.Month.Year, Hours:Minutes:Seconds, and i only want entries from the Day without care about the time?

Several options but the best bet is to store data as proper date time instead of string then in the where clause of your SQL check if datetime is dateadd -24h or use datepart to check dmy is equal to today.

This is simple SQL stuff. Do a web search.

1 Like

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