Help on handling MQTT data

[mcc37]

Hi guys,

I need some help to transform data form MQTT Trigger.

As I need to extract data (room name) from Topic name, I only activate JSON Parse Body option.

Then, I would like to transform the JSON form message to a table, exactly like if I had activated the message option in the MQTT Trigger. Do I need a function for this, or is there a node able to do so ?

If it’s a function, can you help with an example ?

I search the node or function called function1 in the following flow :

Thanks a lot for your help !

[mcc37]

I think it would be a function quite close to that :

const message_body = $json["message"];

const result = [ ];

result.push(message_body);

return result;

But I get this error :

ERROR: All returned items have to contain a property named “json”!

Any idea to solve it ?

[Jon]

You don’t have a property called json, you would need to have result[index].json.message.

Take a look at Function | Docs for a couple of examples.

[mcc37]

Thanks @Jon for your help, I did try this :

const message_body = $json["topic"]
;
const array = JSON.parse(message_body);
const result = [ ];

result.push({json : {array}});

return result;

I think I’m not doing the right javascript.

I need to find the right function that just provide a table with humidity, temperature, … as column.

It’s certainly obvious with javascript/json skills, but I can’t find it

[Jon]

If I was near a computer I would pop an example together for you it is a bit tricky from a mobile device.

When you say a table what do you mean? Are you planning to pop the data in a spreadsheet?

[mcc37]

Likely, I want to insert it into a TimescaleDB instance.

This N8N flow is to add “one column” with a room name to the result I would get if I activate JSON Pase Body / Only message.

I have the right function to extract the room name from the topic path, here it is :

const topic_path = $json["topic"]

return [
  {
    json: {
      room: topic_path.split('/')[1]
    }
  }
]

Now I just want to extract on the other hand the differents values from the body message, and the. multiplex it so it gets ready to insert into TimeScaleDB. I need the same exact result I easily get with options JSON Pase Body / Only message activated (but if I do this, I miss the topic path to get the room name).

Quite frustrating !

[Jon]

If it is just posting and the visuals don’t matter in n8n what about just using $json[“message”].temperature, it looks like it is already an object so you should be able to just call it as it is whenever you need it or do it in a set node. You could also use {{$json[“topic”].split("/")[1]}} in the expression editor at any point as well.

Could be worth a go and you wouldn’t need to use a function node.

[mcc37]

It looks like a nice idea @Jon.

You mean like this :

I got something empty.

[Jon]

Bit hard to read on mobile but the theory looks good, if you use the expression builder to select the node you should be able to browse to the value you want.

[mcc37]

Probably easier or you to read with a screenshot

My last set is like this :

And like this :

Both return Not found

[Jon]

Shouldn’t topic have the ’ around it?

[mcc37]

Shame on me ! Yes, you are right. Now I get the Room name.

But still not the temperature.

[Jon]

On the plus side half way there and no function node

What do you get if you just put {{$json[‘message’]}} does that show the full string?

A real ugly solution could be…

{{JSON.parse($json[‘message’]).temperature}}

[mcc37]

If I do this, I get full string :

If I do the “ugly” stuff : I have this error

If I do it regular : not found

Argh !

[Jon]

Can you try changing the ’ to " it is hard to see if it is the right character

I would have thought that would do it as well, if you drop the .temperature does it return the same data with object at the start?

[mcc37]

Id did try with “” => {{JSON.parse($json[“message”]).temperature}}

I still get [not found].

I did simplify the flow to put this Set after the data mockup, still the same issue.

It seems quite strange

[Jon]

What if you remove the .temperature what do you get?

[mcc37]

{{$json[“message”]}} give { “battery”: 40, “humidity”: 54.64, “linkquality”: 255, “pressure”: 1009, “temperature”: 20.01, “voltage”: 2885 },

[mcc37]

It seems the issue is my Data mockup, your idea works quite well with data coming fron MQTT Trigger. Thanks a lot @Jon

[Jon]

Alright that is good to hear