Converting/ Rebuilding Array of Objects {different length}

General
1

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:

grafik
grafik510Ă—736 16.9 KB

The payload I get is an array with all entries of named "key" I selected:
grafik
grafik251Ă—505 4.51 KB

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

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().

2

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;
3

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

payload.ProgramNumber.value.${
   "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":[]}]

Screenshot_20210325-115405
Screenshot_20210325-1154051920Ă—1080 196 KB

4

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?

5

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

6

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?

7

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
8

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