# Parse and transform MQTT message cluster to influx measurements

**URL:** <https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569>\
**Category:** General\
**Created:** [14 March 2023 22:44 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569 "2023-03-14T22:44:40Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [14 March 2023 22:44 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/1 "2023-03-14T22:44:40Z")

</div>

Hi,

I want to store incoming MQTT messages into Influx messages. The incoming messages look like this:

solaranzeige/wr1/ac-power 220  
solaranzeige/wr1/ac-frequency 50.01  
solaranzeige/wr2/ac-power 225  
solaranzeige/wr1/ac-frequency 50.06

These Mqtt messages come in every 10 seconds. I can already receive the Mqtt messages and join them to one msg.payload resulting in this:

{"solaranzeige/wr1/ac-power“:220,“solaranzeige/wr1/ac-frequency“:50.01,“solaranzeige/wr2/ac-power“:225,“solaranzeige/wr2/ac-frequency“:50.06}

What I want is to store this to an influxdb database solaranzeige. And either two measurements wr1, wr2 with ac-power,ac-frequency being field keys. Or into one measurement (name to be discussed) with ac-power, ac-frequency being fields and „wr1,wr2“ being a tag.

And maybe I would like to filter the incoming messages in order to only pass certain topics (or key/value fields).

Any hint would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![cameo69](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cameo69/32/66593_2.png) [@cameo69](https://discourse.nodered.org/u/cameo69)\
**Post date:** [14 March 2023 23:20 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/2 "2023-03-14T23:20:16Z")

</div>

Something like this?  
Splits your msg in two (wr1 and wr2) and writes them into influxdb.

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

```auto
[{"id":"347f100875322db2","type":"influxdb out","z":"dfd70fafb4d381bc","influxdb":"e10beff6461faf32","name":"write solaranzeige to sensordb","measurement":"solaranzeige","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":870,"y":600,"wires":[]},{"id":"0e41344b8b2f75a0","type":"debug","z":"dfd70fafb4d381bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":510,"y":520,"wires":[]},{"id":"ecc928dc611867c3","type":"debug","z":"dfd70fafb4d381bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":810,"y":560,"wires":[]},{"id":"0d009ea810f4a5b8","type":"inject","z":"dfd70fafb4d381bc","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\t \"solaranzeige/wr1/ac-power\":220,\t \"solaranzeige/wr1/ac-frequency\":50.01,\t \"solaranzeige/wr2/ac-power\":225,\t \"solaranzeige/wr2/ac-frequency\":50.06\t}","payloadType":"jsonata","x":290,"y":600,"wires":[["0e41344b8b2f75a0","619e05b66f0bf779","46874677c76b3f17"]]},{"id":"619e05b66f0bf779","type":"change","z":"dfd70fafb4d381bc","name":"prepare data for influx","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\t \"sensor\": 'wr1',\t \"ac-power\": msg.payload.'solaranzeige/wr1/ac-power',\t \"ac-frequency\": msg.payload.'solaranzeige/wr1/ac-frequency'\t} ","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":560,"wires":[["e2d106bd391b0d05"]]},{"id":"46874677c76b3f17","type":"change","z":"dfd70fafb4d381bc","name":"prepare data for influx","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\t \"sensor\": 'wr2',\t \"ac-power\": msg.payload.'solaranzeige/wr2/ac-power',\t \"ac-frequency\": msg.payload.'solaranzeige/wr2/ac-frequency'\t} ","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":600,"wires":[["e2d106bd391b0d05"]]},{"id":"e2d106bd391b0d05","type":"junction","z":"dfd70fafb4d381bc","x":700,"y":580,"wires":[["ecc928dc611867c3","347f100875322db2"]]},{"id":"e10beff6461faf32","type":"influxdb","hostname":"influxdb","port":"8086","protocol":"http","database":"sensordb","name":"","usetls":false,"tls":"","influxdbVersion":"1.x","url":"http://influxdb:8086","rejectUnauthorized":false}]

```

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/4/4463462aaff2af4a5ad9ccb49dbf373a0edc1a13.png)

---

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [15 March 2023 05:53 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/3 "2023-03-15T05:53:20Z")

</div>

@cameo69

Very cool. Thanks for the fast reply.

I did forget to mention that this is a very stripped down example. In fact it is more like 40 measurements per device. I hoped for some brilliant magic function that would automatically strip

x/y/z value

to a

tag: x  
field: z  
value

object ready to inject in Influx regardless of how many x/y/z triples I feed it.

---

<div class="post-metadata">

**Author:** ![cameo69](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cameo69/32/66593_2.png) [@cameo69](https://discourse.nodered.org/u/cameo69)\
**Post date:** [15 March 2023 06:52 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/4 "2023-03-15T06:52:29Z")

</div>

This might be more than I can do with my limited JSONata skills, but I suggest you post the real messages here. I am sure that makes it easier for ppl to help.

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [15 March 2023 08:01 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/5 "2023-03-15T08:01:42Z")

</div>

There are several ways to filter the topics,

1. You can use dynamic subscriptions and only subscribe to the topics required.
2. you can use a switch node with the property field set to JSONata J: `$$.topic in ["topic/1","topic/2","topic/etc"]`, and the rule `is true`.

As to a dynamic way to set the fields and tags,. please supply an output format/s for the input you have shown, as guessing is not the best way to do this.

---

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [15 March 2023 09:44 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/6 "2023-03-15T09:44:13Z")

</div>

Hi @cameo69,

the real messages are the same structure. Excerpt:

```auto
solaranzeige/pv-garage/zentralertimestamp 1678873357
solaranzeige/pv-garage/wattstundengesamtheute 1489.23
solaranzeige/pv-garage/firmware 1
solaranzeige/pv-garage/ac_ausgangsfrequenz 49.99747467041
solaranzeige/pv-garage/solarspannung_string_2 308.56726074219
solaranzeige/pv-garage/solarstrom_string_2 1.9309170246124
solaranzeige/pv-garage/ac_wirkleistung 271.63934326172
solaranzeige/pv-garage/ac_ausgangsstrom 1.1686055958271
solaranzeige/pv-garage/ac_ausgangsspannung 233.96688842773
solaranzeige/pv-garage/solarstrom 1.2068809270859
solaranzeige/pv-garage/solarspannung 368.55401611328
solaranzeige/pv-garage/solarspannung_string_1 368.55401611328
solaranzeige/pv-garage/solarstrom_string_1 1.2068809270859
solaranzeige/pv-garage/geraetestatus 0
solaranzeige/pv-garage/errorcodes 0
solaranzeige/pv-garage/solarleistung_string_1 444.80081264802
solaranzeige/pv-garage/solarleistung_string_2 595.81777700511
solaranzeige/pv-garage/gen24 1
solaranzeige/pv-garage/temperatur 0
solaranzeige/pv-garage/wattstundengesamtjahr 0
solaranzeige/pv-garage/wattstundengesamt 0
solaranzeige/pv-garage/modulpvleistung 6270

```

Appr. 60 every 10 seconds. Per device (device in this case being pv-garage). The individual measurements (like modulpvleistung) might vary from device to device.

---

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [15 March 2023 09:47 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/7 "2023-03-15T09:47:48Z")

</div>

@E1cid thanks!

I would like to transform:

```auto
solaranzeige/pv-garage/zentralertimestamp 1678873357
solaranzeige/pv-garage/wattstundengesamtheute 1489.23
solaranzeige/pv-garage/firmware 1
solaranzeige/pv-garage/ac_ausgangsfrequenz 49.99747467041
solaranzeige/pv-garage/solarspannung_string_2 308.56726074219
solaranzeige/pv-garage/solarstrom_string_2 1.9309170246124
solaranzeige/pv-garage/ac_wirkleistung 271.63934326172
solaranzeige/pv-garage/ac_ausgangsstrom 1.1686055958271
solaranzeige/pv-garage/ac_ausgangsspannung 233.96688842773
solaranzeige/pv-garage/solarstrom 1.2068809270859
solaranzeige/pv-garage/solarspannung 368.55401611328
solaranzeige/pv-garage/solarspannung_string_1 368.55401611328
solaranzeige/pv-garage/solarstrom_string_1 1.2068809270859
solaranzeige/pv-garage/geraetestatus 0
solaranzeige/pv-garage/errorcodes 0
solaranzeige/pv-garage/solarleistung_string_1 444.80081264802
solaranzeige/pv-garage/solarleistung_string_2 595.81777700511
solaranzeige/pv-garage/gen24 1
solaranzeige/pv-garage/temperatur 0
solaranzeige/pv-garage/wattstundengesamtjahr 0
solaranzeige/pv-garage/wattstundengesamt 0
solaranzeige/pv-garage/modulpvleistung 6270

```

Into an Influx Measurement solaranzeige:

```auto
Timestamp, Device (Tag), wattstundengesamtheute (Field), firmware (Field), ac_ausgangsfrequenz (field) etc.
1678873357, pv-garage, 1489.23, 1, 49.99747467041

```

Maybe alternatively into one measurement per device. So measurement pv-garage with

```auto
Timestamp, wattstundengesamtheute (Field), firmware (Field), ac_ausgangsfrequenz (field) etc.
1678873357, 1489.23, 1, 49.99747467041

```

---

<div class="post-metadata">

**Author:** ![cameo69](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cameo69/32/66593_2.png) [@cameo69](https://discourse.nodered.org/u/cameo69)\
**Post date:** [15 March 2023 11:35 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/8 "2023-03-15T11:35:54Z")

</div>

I am not sure about this. Is this one message per topic2 containing all topic3 or one message per triple?

Would strongly suggest to post a real message verbatim, then it should become clear which field is populated, how.

---

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [15 March 2023 11:56 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/9 "2023-03-15T11:56:26Z")

</div>

Hi @cameo69,

before I do the join it looks like this:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/b/9bf1abdd700be461cb2806ef4abb322648fddb5e.png)

After the join:

`{"solaranzeige/pv-garage/zentralertimestamp":1678881301,"solaranzeige/pv-garage/wattstundengesamtheute":4096.35,"solaranzeige/pv-garage/firmware":1,"solaranzeige/pv-garage/ac_ausgangsfrequenz":50.001197814941,"solaranzeige/pv-garage/solarspannung_string_2":311.28298950195}`

Regards  
JP

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [15 March 2023 14:12 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/10 "2023-03-15T14:12:36Z")

</div>

If values are coming in separately, then unless you have a good reason, send them to separate Measurements in Influx. There is generally no significant benefit in complicating the system by joining them together into one measurement. Use the KISS principle.

---

<div class="post-metadata">

**Author:** ![cameo69](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cameo69/32/66593_2.png) [@cameo69](https://discourse.nodered.org/u/cameo69)\
**Post date:** [15 March 2023 15:27 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/11 "2023-03-15T15:27:36Z")

</div>

From your example I deduct that it is all `solaranzeige`.

This should do the trick more flexible:

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

```auto
[{"id":"14a022506648ecd8","type":"debug","z":"dfd70fafb4d381bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":450,"y":680,"wires":[]},{"id":"b8e85eaee907f68a","type":"debug","z":"dfd70fafb4d381bc","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":680,"wires":[]},{"id":"9239fcc8dd1f03ab","type":"inject","z":"dfd70fafb4d381bc","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\t \"solaranzeige/wr1/ac-power\":220,\t \"solaranzeige/wr1/ac-frequency\":50.01,\t \"solaranzeige/wr2/ac-power\":225,\t \"solaranzeige/wr2/ac-frequency\":50.06,\t \"solaranzeige/pv-garage/zentralertimestamp\": 1678881301,\t \"solaranzeige/pv-garage/wattstundengesamtheute\": 4096.35,\t \"solaranzeige/pv-garage/firmware\": 1,\t \"solaranzeige/pv-garage/ac_ausgangsfrequenz\": 50.001197814941,\t \"solaranzeige/pv-garage/solarspannung_string_2\": 311.28298950195,\t \"solaranzeige/dumdidum/solarspannung_string_2\": 311.28298950195\t}","payloadType":"jsonata","x":290,"y":640,"wires":[["14a022506648ecd8","5c1c8ce1664b6345"]]},{"id":"5c1c8ce1664b6345","type":"change","z":"dfd70fafb4d381bc","name":"prepare data for influx","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $divided := $spread(msg.payload).(\t {\t \"sensor\": $split($keys($)[0],\"/\")[1],\t $split($keys($)[0],\"/\")[2] : $lookup($, $keys($)[0])\t }\t );\t\t $distinct($divided.sensor).([\t $filter($divided, function($v, $i, $a) {$v.sensor = $} ) ~> $merge() \t])\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":640,"wires":[["b8e85eaee907f68a","b6c8086220147b43"]]},{"id":"b6c8086220147b43","type":"split","z":"dfd70fafb4d381bc","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":670,"y":640,"wires":[["8093dce92045ed90"]]},{"id":"8093dce92045ed90","type":"debug","z":"dfd70fafb4d381bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":910,"y":680,"wires":[]},{"id":"8a55489d629c5941","type":"influxdb out","z":"dfd70fafb4d381bc","influxdb":"e10beff6461faf32","name":"write solaranzeige to sensordb","measurement":"solaranzeige","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":970,"y":640,"wires":[]},{"id":"e10beff6461faf32","type":"influxdb","hostname":"influxdb","port":"8086","protocol":"http","database":"sensordb","name":"","usetls":false,"tls":"","influxdbVersion":"1.x","url":"http://influxdb:8086","rejectUnauthorized":false}]

```

Result looks like this and could be easily sent to influxdb.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/0/903fdb5bba4912d68f6c9ead2fc07db5c551b68d.png)

JSONata for reference: [https://try.jsonata.org/K\_ak\_MUhp](https://try.jsonata.org/K_ak_MUhp)

---

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [15 March 2023 15:37 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/12 "2023-03-15T15:37:09Z")

</div>

Hi @Colin ,

agreed this is a solution and I am coming from the SQL not the non-SQL world in which you would most likely never do this. 🙂

All the measurements are taken from one device (sensor) at one point in time. It is just the process of translating these to separate MQTT messages (instead of putting all of them in one JSON object and send that one message via MQTT) that creates the problem.

To me it feels natural to have all measurements taken from one device at one point in time (every 10 seconds) to put into one measurement (table) in influx as well. Agreed this is not absolutely necessary.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [15 March 2023 15:39 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/13 "2023-03-15T15:39:55Z")

</div>

Note that if you want to sent fields and tags to the influxdb out node then you have to send an array of two objects, the first containing the fields and the second containing the tags. Something like

```auto
[
  {
    "ac-power": 225,
    "ac-frequency": 50.01
  },
  {
    sensor: "wr1"
  }
]

```

Also I recommend not using names with special characters such as a minus sign. Otherwise it just causes problems. They could perhaps be changed to underscores.

---

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [15 March 2023 15:54 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/14 "2023-03-15T15:54:06Z")

</div>

I was just looking into that.

@cameo69 your proposal is awesome and extremely close to the final solution. The icing on the cake would be to extract the sensor field and move things from

```auto
{"sensor":"pv-garage","zentralertimestamp":1678895197,"wattstundengesamtheute":7839.21,"firmware":1,"ac_ausgangsfrequenz":49.959522247314,"solarspannung_string_2":299.48822021484,"solarstrom_string_2":0.38880056142807,"ac_wirkleistung":283.07800292969}

```

to

```auto
[
   {
      "zentralertimestamp":1678895197,
      "wattstundengesamtheute":7839.21,
      "firmware":1,
      "ac_ausgangsfrequenz":49.959522247314,
      "solarspannung_string_2":299.48822021484,
      "solarstrom_string_2":0.38880056142807,
      "ac_wirkleistung":283.07800292969
   },
  {
    sensor: "pv-garage"
  }
]

```

I will try to find a solution but maybe this also is something which you guys simply laugh and point me to an oneline solution. 🙂

You have been tremendous help so far and I cannot thank you enough!

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [15 March 2023 16:13 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/15 "2023-03-15T16:13:19Z")

</div>

> [@j.koopmann](#):
>
> All the measurements are taken from one device (sensor) at one point in time.

OK, in that case I agree, it is worth putting them back together. Can you change the sending device code so that it doesn't split them up, or is it something you don't have control over?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [15 March 2023 16:16 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/16 "2023-03-15T16:16:38Z")

</div>

Still require the exact format you wish to send to influx, I have taken what i think it should be.  
You may have to adjust  
example

```auto
[{"id":"9239fcc8dd1f03ab","type":"inject","z":"65617ffeb779f51c","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\t \"solaranzeige/wr1/ac-power\":220,\t \"solaranzeige/wr1/ac-frequency\":50.01,\t \"solaranzeige/wr2/ac-power\":225,\t \"solaranzeige/wr2/ac-frequency\":50.06,\t \"solaranzeige/pv-garage/zentralertimestamp\": 1678881301,\t \"solaranzeige/pv-garage/wattstundengesamtheute\": 4096.35,\t \"solaranzeige/pv-garage/firmware\": 1,\t \"solaranzeige/pv-garage/ac_ausgangsfrequenz\": 50.001197814941,\t \"solaranzeige/pv-garage/solarspannung_string_2\": 311.28298950195,\t \"solaranzeige/dumdidum/solarspannung_string_2\": 311.28298950195\t}","payloadType":"json","x":130,"y":3520,"wires":[["14a022506648ecd8","5c1c8ce1664b6345"]]},{"id":"14a022506648ecd8","type":"debug","z":"65617ffeb779f51c","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":290,"y":3560,"wires":[]},{"id":"5c1c8ce1664b6345","type":"change","z":"65617ffeb779f51c","name":"prepare data for influx","rules":[{"t":"set","p":"payload","pt":"msg","to":"$keys($$.payload).(\t $topic := $split($, \"/\");\t {\t $topic[2]: $lookup($$.payload,$),\t \"sensor\": $topic[1]\t }\t){sensor:$merge($)} ~> |$.*|{},[\"sensor\",\"firmware\"]|","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":3520,"wires":[["b6c8086220147b43"]]},{"id":"b6c8086220147b43","type":"split","z":"65617ffeb779f51c","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":490,"y":3460,"wires":[["d2b826ea4a6a4a4b"]]},{"id":"d2b826ea4a6a4a4b","type":"change","z":"65617ffeb779f51c","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t $$.payload,\t {\t \"sensor\": $$.topic\t }\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":620,"y":3520,"wires":[["8093dce92045ed90"]]},{"id":"8093dce92045ed90","type":"debug","z":"65617ffeb779f51c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":750,"y":3560,"wires":[]}]

```

example output per device

```auto
[
  {
    "ac-power":220,
    "ac-frequency":50.01
  },
  {
    "sensor":"wr1"
  }
]

```

The expression that prepares the data to split

```auto
$keys($$.payload).(
   $topic := $split($, "/");
   {
       $topic[2]: $lookup($$.payload,$),
       "sensor": $topic[1]
   }
){sensor:$merge($)} ~> |$.*|{},["sensor","firmware"]|

```

[edit] Added `"firmware"`to the delete section, any readings you do not want sent add to the delete array. If you want firmware remove it from the delete array.

---

<div class="post-metadata">

**Author:** ![cameo69](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/cameo69/32/66593_2.png) [@cameo69](https://discourse.nodered.org/u/cameo69)\
**Post date:** [15 March 2023 16:17 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/17 "2023-03-15T16:17:08Z")

</div>

🙂

```auto
[{"id":"3d353a08b767cc7b","type":"debug","z":"dfd70fafb4d381bc","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":450,"y":900,"wires":[]},{"id":"4ae3f61cab8f50f3","type":"debug","z":"dfd70fafb4d381bc","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":900,"wires":[]},{"id":"69de696ea3fd0d39","type":"inject","z":"dfd70fafb4d381bc","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\t \"solaranzeige/wr1/ac-power\":220,\t \"solaranzeige/wr1/ac-frequency\":50.01,\t \"solaranzeige/wr2/ac-power\":225,\t \"solaranzeige/wr2/ac-frequency\":50.06,\t \"solaranzeige/pv-garage/zentralertimestamp\": 1678881301,\t \"solaranzeige/pv-garage/wattstundengesamtheute\": 4096.35,\t \"solaranzeige/pv-garage/firmware\": 1,\t \"solaranzeige/pv-garage/ac_ausgangsfrequenz\": 50.001197814941,\t \"solaranzeige/pv-garage/solarspannung_string_2\": 311.28298950195,\t \"solaranzeige/dumdidum/solarspannung_string_2\": 311.28298950195\t}","payloadType":"jsonata","x":290,"y":860,"wires":[["3d353a08b767cc7b","fd95d439d95a5be3"]]},{"id":"fd95d439d95a5be3","type":"change","z":"dfd70fafb4d381bc","name":"prepare data for influx","rules":[{"t":"set","p":"payload","pt":"msg","to":"(\t $divided := $spread(msg.payload).(\t {\t \"sensor\": $split($keys($)[0],\"/\")[1],\t $split($keys($)[0],\"/\")[2] : $lookup($, $keys($)[0])\t }\t );\t $vals := $distinct($divided.sensor).(\t [\t $filter(\t $divided,\t function($v, $i, $a) {$v.sensor = $} \t ) ~> $merge() \t]\t );\t $vals.(\t [\t [\t $ ~> |$|{},\t [\"sensor\"]|,\t {\"sensor\": $.sensor}\t ]\t ]\t )\t\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":860,"wires":[["4ae3f61cab8f50f3","732cb9631b2b35b2"]]},{"id":"732cb9631b2b35b2","type":"split","z":"dfd70fafb4d381bc","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":670,"y":860,"wires":[["8ec7fe81cd1ea23f"]]},{"id":"8ec7fe81cd1ea23f","type":"debug","z":"dfd70fafb4d381bc","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":910,"y":900,"wires":[]},{"id":"2bb4918a48999c5e","type":"influxdb out","z":"dfd70fafb4d381bc","influxdb":"e10beff6461faf32","name":"write solaranzeige to sensordb","measurement":"solaranzeige","precision":"","retentionPolicy":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"organisation","bucket":"bucket","x":970,"y":860,"wires":[]},{"id":"e10beff6461faf32","type":"influxdb","hostname":"influxdb","port":"8086","protocol":"http","database":"sensordb","name":"","usetls":false,"tls":"","influxdbVersion":"1.x","url":"http://influxdb:8086","rejectUnauthorized":false}]

```

> **[JSONata Playground - Get familiar with the JSONata language](https://www.stedi.com/jsonata/playground?statev2=eJyNU%2B2OmzAQfBUL8QMkLsHk4%2BCq9kUAnSzYUOvAUNuQXoA%2BexfClaSQXpHAa%2B%2FM7KxtwtBQZS0TMBwDflYSlOKlwAnr3I7i2EaCkAhBOZNMXIBnsD1LumXJU1WeQUbGC%2FE813kMO0n4UYNI3gfowd24dB3s%2FaV5%2BAdsoXlcA1fNU4YzjC4gtGQ5SM0LUJoV1UCkx2ff9%2Blu3dHMPjOtla5FCiIDxQr9HWoNg8LeDY6b3arTmX7isjgzORI%2BqcSSV1arjIlMTS1ePjp0KQ2efboP9p9ojMuqYkLUIntVWnIcvEFmR%2BnG873ADw4uDVZdp3WRcvz8v0okerwm1qBFzJQ3PIWUvHwlpsLbxFLLtDfX5PC0f6LhwdogVDmeN8Jzri3zDd4VckI3diJjGxl2SGPnjvUY6cUEhfKyfKsry3TIDcSeJfpraH%2B5Wm5Yrka%2FKVeaiwSVpy42V3e3%2FsN7Jyeea5DW3SK52QdnkTnVWAH%2FMMts0B%2FHl9mkNZupFkEfPW7MLc0mv74RswCZgWWTj2Q8DrdNPLQZLv0Nkp3Ztf3SYTifStwt0%2B3doU22%2B3tYPE%2BnELffxltSdl636%2FZG7BgHI%2F4NPaZPYg%3D%3D)**
>
> Get familiar with JSONata syntax in Stedi's playground where you can quickly edit, share, and embed examples.

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

---

<div class="post-metadata">

**Author:** ![j.koopmann](https://avatars.discourse-cdn.com/v4/letter/j/85e7bf/32.png) [@j.koopmann](https://discourse.nodered.org/u/j.koopmann)\
**Post date:** [16 March 2023 06:51 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/18 "2023-03-16T06:51:56Z")

</div>

Sending software is not mine but I will investigate. You all have been a tremendous help. Thank you guys so much.

---

<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:** [15 May 2023 06:52 UTC](https://discourse.nodered.org/t/parse-and-transform-mqtt-message-cluster-to-influx-measurements/76569/19 "2023-05-15T06:52:39Z")

</div>

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