Hello everyone!
I'm a newbee at Node-Red and I'd like to collect information from my MQTT and push it to my database.
Initially I subscribe for a branch of topics (Devices/+) and receive around 50 MQTT messages. Now I'd like to combine them into one single object and store it into database at once instead of 50 different queries.
Is there a handsome way (without Flow variables) to do so?
Another way (instead of the join) is to write a function that stores into one object (with the topic as the key) then store the object in flow.
That way it's much easier to inspect in context browser side bar.
Do you mean store into an array at Flow level variable?
Looking to this option but can't understand it completely. It seems that I have to add a function that will modify MQTT msg into a way it can be received by Join.
for storing it in one object, i do this...
MQTT (topic/subtopic/#) --> function node
var val = msg.payload; // get the value
var key = msg.topic; //get the topic
var mqttData= flow.get("mqttData") || {}; //get the store object
mqttData[key] = val; // add/update the value
flow.set("mqttData", mqttData); // save to flow context
The join is really simple. Give it a go.
If you're doing it in a function node - just use local context - no need to expose it to the flow.
The assumption is he needs to retrieve it elsewhere for sending to database
(although he could make the function multi purpose - i.e. return the store object mqttData out after each update)
The assumption I used was ...
Absolutely but sometimes its the right thing to do 
however (another assumption) i suspect the OP thought he'd have to do 50 change nodes (hence wanting to avoid context)
Far too many assumptions
The ideal situation is to subscribe to all topics and push all of them into one query.
Now I have something like this that processes each topic separately and put it into DB one by one:
And if I subscribe all the topics then I receive them also as separate objects:
I saw also an example with Batch, that can wait for a number of messages and only then pass them to Join. But this is not a good option due to potential lack of some topics.
What you need to consider - regardless of using node context, flow context, join nodes etc is...
If you gather all the values into one object, when do you write it to DB?
I can guarantee you all 50 items will not update at the same time so you will be writing necessary/unchanged values to the DB in a wide table.
Wouldn't it be better to generate a long table vs wide table?
e.g...
| timestamp | topic | value |
|---|---|---|
| 123456789 | topic1 | 77.3 |
| 123456789 | topic2 | 73.2 |
| 123456792 | topic7 | 2.76 |
| 123456792 | topic4 | 7.01 |
this would negate the need to gather all values into an object & any future data will fit without modification to the database.
I assume that I should push it to DB as soon as I get all the values or timeout passed. Your note makes sense. If some of the topics is missed then I can wait for it until end of the world (or timeout).
As you see as DB I use InfluxDB that is not relational DB and is designed for storing sequences of data. And it is a really matter of the schema design. Here I have two options:
Option 2 is as it is described in the most examples at InfluxDB. Option 1 looks more reasonable in terms to processing of MQTT events. In that case I can use one single (I hope) function that will be universal.
Let me check the Option 1 also.
Anyway combining of several objects into one is also not bad timeleisure.
Finally I came to the narrow-long table where I can distingiush the concrete types with TAGs.
And I can do a selection from # widlcard topic with SWITCH that passes to a neccesary function. The next step is to use function that will do everything inside without any switches in before.
And fianlly it looks like this:
With code similar to this:
switch (msg.topic){
case 'Dacha/pressure':
lLocation="Dacha";
lArea="Pantry";
lMeasurement="Pressure"
msg.valid=true;
break;
case 'Dacha/temp':
lLocation="Dacha";
lArea="Pantry";
lMeasurement="Temperature"
msg.valid=true;
break;
And the sequence in the DB is also anrrow. I use keys to distinguish data. Actually I don't know wheeather it is good or bed. We will se when some amount of data is stored.
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.