Visualizing Mqtt JSON Payload 3

This is another post that describes how to manipulate the Mqtt payload. The JSON payload in this example comes from a commercial Minew BLE gateway.

You must be using Version 2.3 or greater for this example to work!

For this example, we’ll use some data from a Minew gateway. The data coming from the Minew gateway has a topic of /minew/ac233fc04bba/status and looks like this. The ac233fc04bba is the gateway mac address.

[
   {
      "timestamp":"2020-03-20T08:01:11Z",
      "type":"S1",
      "mac":"AC233FA2495C",
      "bleName":"",
      "rssi":-66,
      "battery":100,
      "temperature":14.94,
      "humidity":65.34
   },
   {
      "timestamp":"2020-03-20T08:01:12Z",
      "type":"S1",
      "mac":"AC233FA249C9",
      "bleName":"",
      "rssi":-66,
      "battery":100,
      "temperature":15.14,
      "humidity":70.68
   }
]

So the payload from the gateway is an array of sensor objects. I want to use the data from the sensor with mac address AC233FA2495C. I’m interested in the battery, temperature and humidity values. The sensor is placed in one of my vegetable beds in full sun in front of my house. I expect it to get relatively hot (and cold).

From Version 2.3 we theoretically can use the JSON extract function to get data directly from this field.

THIS WON’T WORK – UNLESS SENSOR AC233FA2495C is always in array position 0, I can’t guarantee this.

Why, JSON_EXTRACT is limited in it’s features. It can extract values from a known JSON format but not from a format based on a criteria – as far as I can tell.

I get around this by exploding the JSON from gateway as it comes in using a hook. I want a separate database entry for each sensor.

//Called as the data comes in but before it is persisted 
//to the database. Create multiple rows, one for each sensor
//from the minew gateway json
function mqttcogs_msg_in_pre_minew($publish_object)
 {
     $topic = $publish_object->getTopic();

     //Do nothing if it isn't minew data
     if (!(substr( $topic, 0, 6 ) === "/minew")) {
         return $publish_object;
     }
          
     //get an array of objects from the payload
     $mqttmsg = $publish_object->getMessage();
     $payload_arr = json_decode($mqttmsg);
     if (!is_array($payload_arr)) {
     	$payload_arr = array($payload_arr);
     }
     
     $publish_object_arr = array();
     
     //gateway topic is /gw/ac233fc04bba/status
     //extract the gateway id from this
     $topic = explode('/', $topic);
     $gw = $topic[2];
   
    
     //the incoming payload is an array of objects like this
     // {
     //    "timestamp": "2020-01-11T09:54:26Z",
     //    "type": "S1",
     //    "mac": "AC233FA249C9",
     //    "bleName": "",
     //    "rssi": -41,
     //    "battery": 100,
     //    "temperature": 20.79,
     //    "humidity": 57.05
     //}    
     //So we loop and create a new publish object for each one
     foreach($payload_arr as $key => $payload) {
	 //type filter...
         $pb = clone $publish_object;
         
         //for each object we want to make sure the utc field is correct
         //and remove the timestamp from the json as it isn't required
         if (property_exists($payload, 'timestamp')) {
         	$pb->setDateTime(new DateTime($payload->timestamp));
         	unset($payload->timestamp);
         }
         
         if (property_exists($payload, 'mac')) {
           $pb->setTopic('/minew/'.$payload->mac); 
            unset($payload->mac);
         }
         
         $payload->gw = $gw;
         $pb->setMessage(json_encode($payload));
	     array_push($publish_object_arr, $pb);
     }
     return $publish_object_arr;
 }
add_filter('mqttcogs_msg_in_pre', 'mqttcogs_msg_in_pre_minew', 10, 2);

After this hook is running I get a separate row for each sensor in my database. The image shows the change from the original format to the new one after applying the code above.

And now creating some graphs is easy.

[ mqttcogs_drawgoogle ajax="true" charttype="LineChart" options="{width: '100%', height: '100%',title:'Outsidebed Temp (C)'}"][ mqttcogs_data limit="100" order="DESC" topics="/minew/AC233FA2495C$.temperature"][/mqttcogs_drawgoogle] 
[ mqttcogs_drawgoogle ajax="true" charttype="LineChart" options="{width: '100%', height: '100%',title:'Outsidebed Humidity (%)'}"][ mqttcogs_data limit="100" order="DESC" topics="/minew/AC233FA2495C$.humidity"][/mqttcogs_drawgoogle] 
[ mqttcogs_drawgoogle ajax="true" charttype="LineChart" options="{width: '100%', height: '100%',title:'Outsidebed Temperature MinMaxAvg/Day (C)'}"][ mqttcogs_data order="DESC" group="DAY"
aggregations="MIN,MAX,AVG" from="-7" topics="/minew/AC233FA2495C$.temperature,/minew/AC233FA2495C$.temperature,/minew/AC233FA2495C$.temperature"][/mqttcogs_drawgoogle]