# Combine several messages from MQTT into one request to DataBase

**URL:** <https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915>\
**Category:** General\
**Created:** [6 June 2020 20:10 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915 "2020-06-06T20:10:34Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![SuperBoss9](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/superboss9/32/23462_2.png) [@SuperBoss9](https://discourse.nodered.org/u/SuperBoss9)\
**Post date:** [6 June 2020 20:10 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/1 "2020-06-06T20:10:34Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2020 20:26 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/2 "2020-06-06T20:26:38Z")

</div>

> [@SuperBoss9](#):
>
> Is there a handsome way

Indeed

See [this article in the cookbook](https://cookbook.nodered.org/basic/join-streams) for an example of how to join messages into one object.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2020 20:28 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/3 "2020-06-06T20:28:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![SuperBoss9](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/superboss9/32/23462_2.png) [@SuperBoss9](https://discourse.nodered.org/u/SuperBoss9)\
**Post date:** [6 June 2020 21:10 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/4 "2020-06-06T21:10:11Z")

</div>

> [@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](https://cookbook.nodered.org/basic/join-streams) 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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2020 21:15 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/5 "2020-06-06T21:15:12Z")

</div>

for storing it in one object, i do this...

MQTT (topic/subtopic/#) --\> function node

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2020 21:15 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/6 "2020-06-06T21:15:38Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [6 June 2020 21:16 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/7 "2020-06-06T21:16:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2020 21:17 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/8 "2020-06-06T21:17:36Z")

</div>

> [@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)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [6 June 2020 21:18 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/9 "2020-06-06T21:18:50Z")

</div>

The assumption I used was ...

> [@SuperBoss9](#):
>
> Is there a handsome way (without Flow variables) to do so?

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2020 21:20 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/10 "2020-06-06T21:20:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![SuperBoss9](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/superboss9/32/23462_2.png) [@SuperBoss9](https://discourse.nodered.org/u/SuperBoss9)\
**Post date:** [6 June 2020 21:22 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/11 "2020-06-06T21:22:11Z")

</div>

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:

 ![1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/1/919882e8841eec4a61074b2451ac669b645411e3.png)

And if I subscribe all the topics then I receive them also as separate objects:

 ![2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/c/6ce60771444df608a382b418c9536874ea597fd4.png)

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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 June 2020 21:32 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/12 "2020-06-06T21:32:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![SuperBoss9](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/superboss9/32/23462_2.png) [@SuperBoss9](https://discourse.nodered.org/u/SuperBoss9)\
**Post date:** [6 June 2020 21:48 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/13 "2020-06-06T21:48:57Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![SuperBoss9](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/superboss9/32/23462_2.png) [@SuperBoss9](https://discourse.nodered.org/u/SuperBoss9)\
**Post date:** [8 June 2020 08:44 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/14 "2020-06-08T08:44:13Z")

</div>

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.

 ![Annotation 2020-06-08 114351](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/a/6abb13e8e5490b22c58981418c6942e094aeec93.jpeg)

---

<div class="post-metadata">

**Author:** ![SuperBoss9](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/superboss9/32/23462_2.png) [@SuperBoss9](https://discourse.nodered.org/u/SuperBoss9)\
**Post date:** [8 June 2020 18:18 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/15 "2020-06-08T18:18:37Z")

</div>

And fianlly it looks like this:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/a/2a43d0e08d21effc9a403db20f78b6a4973a63e2.png)

With code similar to this:

```auto
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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [22 June 2020 18:28 UTC](https://discourse.nodered.org/t/combine-several-messages-from-mqtt-into-one-request-to-database/27915/16 "2020-06-22T18:28:50Z")

</div>

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