Advice on how to process MQTT messages containing complex JSON structures

Emoncms
1

I have been running emonPi for well over a year monitoring our grid/solar/battery set-up and am very pleased with its flexibility. A couple of months ago I introduced an MQTT feed from a Raspberry P Pico W that is collecting various information covering our boiler, thermal store and solar hot water and that is working well; the MQTT messages consist of a set of name/value pairs, and emoncms handles them well. Handling the data this way allows me to use all of the emoncms data & graphing capabilities. So far, so good!

I have just installed a Hildebrand Glow CAD which can send out actual gas and electricity meter readings in real-time using MQTT. Although the messages are valid JSON, they use a hierarchical data format that requires additional processing to extract individual data items.
If I send the messages directly to an ‘emon/glow’ topic I see 3 new inputs, 1 for the status and 1 for each meter - I cannot see any obvious way of breaking out the actual data. If I send to an intermediate topic and use an external system to pre-process the meter messages I can have a separate device for each of the meters, with a series of inputs numbered from 0 for each. But I can’t see what each of these inputs corresponds to, and the relationship between name and input number is probably not fixed.

Here’s an example of the raw gas meter data to give you an idea of what it looks like:
Topic: “glow/A8032AD79A34/SENSOR/gasmeter” Message: “{“gasmeter”:{“timestamp”:“2024-12-04T17:33:11Z”,“energy”:{“import”:{“cumulative”:38859.805,“day”:81.027,“week”:238.392,“month”:274.998,“units”:“kWh”,“cumulativevol”:3480.870,“cumulativevolunits”:“m3”,“dayvol”:81.027,“weekvol”:238.392,“monthvol”:274.998,“dayweekmonthvolunits”:“kWh”,“mprn”:“9193963605”,“supplier”:”—“,“price”:{“unitrate”:0.06318,“standingcharge”:0.23928}}}}}”

If I pre-process it the message part looks like this and emoncms generates inputs 0 thru 15:
{“gasmeter”: {“energy”: {“import”: {“cumulativevol”: 3480.87, “price”: {“standingcharge”: 0.23928, “unitrate”: 0.06318}, “cumulative”: 38859.8, “dayweekmonthvolunits”: “kWh”, “supplier”: “—”, “units”: “kWh”, “week”: 238.392, “dayvol”: 81.027, “weekvol”: 238.392, “day”: 81.027, “monthvol”: 274.998, “cumulativevolunits”: “m3”, “mprn”: “9193963605”, “month”: 274.998}}, “timestamp”: “2024-12-04T17:33:11Z”}}

So my question is, how can I best process these MQTT messages to extract the data that I wish to see into emoncms? I don’t want to store all of it, just create feeds from certain values.
I would prefer to do this on the emonpi platform, rather than on another system, if possible.

Thanks,
Martin

2

Hi Martin,

the easiest would probably be to install Node-RED on the emonpi. Within Node-RED, you can parse arbitrarily complex MQTT messages and forward the simplified extracted data to emoncms.

3

Hi Martin.

I also have a Glow CAD (although no gas supply so it only sends readings for the Electricity meter). However I don’t send its readings directly to emoncms; they go to an InfluxDB database.

I reckon the best strategy is to run an additional utility on the emonpi which Subscribes to the glow/ MQTT Topic, extracts the fields you want and re-Publishes those as new MQTT messages on a different MQTT Topic, which you control the structure and format of, and which emoncms Subscribes to.

The choice of the ‘additional utility’ depends on what sort of tech you are familiar with. André’s suggestion of Node-RED is perfectly good if you have any experience with that. Alternatively, a Python script which uses the Paho libraries for MQTT would be less resource-intensive (but more complex to code).

4

Thanks both, I’m not familiar with Node-RED, so I will probably use Python.
I was using Python earlier (on another Pi Pico) to republish the MQTT JSON - that’s how I captured the sample data above. I didn’t realise that Python3 is already installed on the emonpi, and I have good examples of MQTT & JSON processing code in Python from previous projects.
The main reason that I want the data in the local emoncms database is that I already have a Python script that pulls data from there and updates an Excel spreadsheet - it will keep things simple!

5

If you’re able to python, you can post the data directly to EmonCMS rather than going through MQTT again, if that’s easier.

https://docs.openenergymonitor.org/emoncms/postingdata.html

6

Thanks Tim,

That’s perfect - I already have a framework for doing MQTT to HTTP from another project!

7

Based on Tim’s suggestion, I’ve managed to put together a Python script to take data from the MQTT messages received from the Glow CAD and send it to emoncms via HTTP - and it’s running as a service on the emonpi system. Still a bit of tweaking to do to derive the peak and off-peak consumption figures (although the CAD only receives total consumption from the meter, it does report the current unit rate), but the basic mechanism is working well.

Thanks all.

8

Bit late to the party, but really easy with Node-Red

Subscribe to the Glow topic

glow/XXXXXXXXXX/SENSOR/electricitymeter

as a Parsed JSON Object

Put this in a function -

var newmsg = {};
newmsg.payload = {};

const time = Date.parse(msg.payload.electricitymeter.timestamp)/1000;
const electric = msg.payload.electricitymeter.energy.import.cumulative * 1000;

newmsg.payload = JSON.stringify({time, electric});
return newmsg;

And this in the Gas with a suitable MQTT topic read.

var newmsg = {};
newmsg.payload = {};

const time = Date.parse(msg.payload.gasmeter.timestamp)/1000;
const gasm3 = msg.payload.gasmeter.energy.import.cumulativevol;

newmsg.payload = JSON.stringify({time, gasm3});
return newmsg;

Send both on topic emon/glow

image
image662×119 10.4 KB