Combine several messages from MQTT into one request to DataBase

[SuperBoss9]

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?

[Steve-Mcl]

@SuperBoss9

Is there a handsome way

Indeed

See this article in the cookbook for an example of how to join messages into one object.

[Steve-Mcl]

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.

[SuperBoss9]

@Steve-Mcl

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.

Do you mean store into an array at Flow level variable?

@Steve-Mcl

See this article in the cookbook for an example of how to join messages into one object.

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.

[Steve-Mcl]

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

[Steve-Mcl]

@SuperBoss9

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.

The join is really simple. Give it a go.

[dceejay]

If you're doing it in a function node - just use local context - no need to expose it to the flow.

[Steve-Mcl]

@dceejay

If you're doing it in a function node - just use local context

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)

[dceejay]

The assumption I used was ...

@SuperBoss9

Is there a handsome way (without Flow variables) to do so?

[Steve-Mcl]

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

[SuperBoss9]

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.

[Steve-Mcl]

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.

[SuperBoss9]

@Steve-Mcl

If you gather all the values into one object, when do you write it to DB?

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:

1. Store with narrow sequences (as at yours example). In that case I need to use different Measurements (Tables) to store different nature of data.
2. Use wide sequences (as I planned to do initially) where I have a number of Tags (qualifiers, keys) that will help me to select and combine data.

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.

[SuperBoss9]

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.

[SuperBoss9]

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.