MQTT JSON to database

We have a medical based room alert system which outputs a json style mqtt when a room is calling and again when a call is cancelled. All room states are included in every message.

I would like to take this data and log it to a database in a format that would allow me to see when a room was activated then deactivated. This would allow us to see how long it took for the alert to be cancelled. An audit trail.

However I’ve spent a bit of time on this but seem to be going in circles and not getting very far. What I can’t figure out is how to separate the rooms. My issue just now is if room 1 calls then room 2 calls it sees the call of room 1 again when the script processes for room 2. I’m a bit lost on how to approach this. Would I need an item per room but how would I then just peel out the bit I need from the mqtt?

Could anyone advise on how this can be achieved?

The data sent is (the below is showing room 1, 4 and 6 as active/calling).

{“device”:“callunit”,“r1”:1,“r2”:0,“r3”:0,“r4”:1,“r5”:0,“r6”:1,“r7”:0,“r8”:0}

I’m also hoping to plum this into a habpanel with a graphical view of each room individually but I would look at this after I get the data logging going

Thank you

There are several approaches but the big thing is yes, you will need one Item for each Item.

One approach I might take is to create one Generic MQTT Thing with one Switch type Channel for each room.

Each one will subscribe to the same topic.

Apply a different JSONPATH transformation to each Thing to extract the 1 or 0 for just that room (e.g. JSONPATH($.r4) to extract the 1 for room 4 in your sample JSON).

In the Channel configuration set the custom ON value to 1 and the custom OFF value to 0 which will convert the 1 and 0 to something the Switch can understand.

Link Switch Items to these Channels and you will use those Item to put them on HABPanel.

Though I gotta say I shudder a bit at the thought of openHAB being used in a medical context like this. Though if it’s just monitoring MQTT and displaying information that isn’t so bad.

Thank you! - That’s pretty much what I was thinking of doing. Not sure how it’ll look in a database though but will see what I can manage.

With regards to it being medical, the system is but its independent and not reliant on openhab in any way. We’re purely using this as a data gatherer and not for life and death decisions!

It could look like this. This is a display of my door contacts.

Off course this is not habpanel but the Android app/basicui, but you get the idea. This is just standard persistence and graph, no rules or other magic involved.

It will be stored as a time series. Each item gets it’s own table. Each row is a timestamp and a state.