python JSON format invalid

Question

I'm trying to get date and time flowing into Azure IoT hub to enable me to analyze using Azure DX as time series. I can get the temperature and humidity (humidity at the moment is just a random number). If I use this code, all works well and the JSON is well formatted and flows into IoT hub and onto Azure DX:

The basis for the code is taken from the Microsoft examples here - https://github.com/Azure-Samples/azure-iot-samples-python/blob/master/iot-hub/Quickstarts/simulated-device/SimulatedDeviceSync.py

import asyncio
import random
from azure.iot.device import Message
from azure.iot.device.aio import IoTHubDeviceClient
import time
from datetime import datetime
from w1thermsensor import W1ThermSensor
sensor = W1ThermSensor()
import json

CONNECTION_STRING = "xxxxx"
HUMIDITY = 60

MSG_TXT = '{{"temperature": {temperature},"humidity": {humidity}}}'

async def main():

    try:
        # Create instance of the device client
        client = IoTHubDeviceClient.create_from_connection_string(CONNECTION_STRING)

        print("Simulated device started. Press Ctrl-C to exit")
        while True:

            humidity = round(HUMIDITY + (random.random() * 20), 2)
            temperature = sensor.get_temperature()

            msg_txt_formatted = MSG_TXT.format(temperature=temperature, humidity=humidity)
            message = Message(msg_txt_formatted)

            # Send a message to the IoT hub
            print(f"Sending message: {message}")
            await client.send_message(message)
            await asyncio.sleep(1)

    except KeyboardInterrupt:
        print("Simulated device stopped")

if __name__ == '__main__':
    asyncio.run(main())

The JSON format is valid and works well -

{ "temperature": 7, "humidity": 66.09 }

If I try to add a date/time field like this:

import asyncio
import random
from azure.iot.device import Message
from azure.iot.device.aio import IoTHubDeviceClient
import time
from datetime import datetime
from w1thermsensor import W1ThermSensor
sensor = W1ThermSensor()
import json


CONNECTION_STRING = "xxxxx"
HUMIDITY = 60

x = datetime.now()
timesent = str(x)


MSG_TXT = '{{"temperature": {temperature},"humidity": {humidity},"timesent": {timesent}}}'

async def main():

    try:
        # Create instance of the device client
        client = IoTHubDeviceClient.create_from_connection_string(CONNECTION_STRING)

        print("Simulated device started. Press Ctrl-C to exit")
        while True:

            humidity = round(HUMIDITY + (random.random() * 20), 2)
            temperature = sensor.get_temperature()

            msg_txt_formatted = MSG_TXT.format(temperature=temperature, humidity=humidity, timesent=timesent)
            message = Message(msg_txt_formatted)

            # Send a message to the IoT hub
            print(f"Sending message: {message}")
            await client.send_message(message)
            await asyncio.sleep(1)

    except KeyboardInterrupt:
        print("Simulated device stopped")

if __name__ == '__main__':
    asyncio.run(main())

The output from the JSON is no longer valid and Azure DX will not map. The invalid JSON I get is:

"{\"temperature\": 7,\"humidity\": 72.88, \"timesent\": 2022-11-08 14:21:04.021812}"

I suspect this is something to do with the date/time being formatted as a string, but I'm totally lost.

Would anyone have any ideas how I can send this data?

timesent's value needs to be in quotes, there is no native "Date" type in JSON. — Christoph Dahlen, Nov 08 '22 at 14:56
Thanks for responding @ChristophDahlen. This makes sense from some of the testing I have been doing. Out of curiosity though if I am creating the variable 'timesent' which would hold the value '2022-11-08 14:21:04.021812' as an example. If I just put "" around the timesent then timesent becomes the string not the value it holds. Does that make sense? — JoeHo, Nov 08 '22 at 16:51
"2022-11-08 14:21:04.021812" is a string representing a date and time (without timezone). The interpretation back into a Date is subject to mapping. Various date formats do exists, but the [most commonly](https://stackoverflow.com/questions/10286204/what-is-the-right-json-date-format/15952652#15952652) used ist [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601). — Christoph Dahlen, Nov 08 '22 at 17:09
These two posts solved it for me - https://stackoverflow.com/questions/11875770/how-to-overcome-datetime-datetime-not-json-serializable https://stackoverflow.com/questions/2150739/iso-time-iso-8601-in-python — JoeHo, Nov 10 '22 at 11:04
You can consolidate both the answers and add what exactly worked for you as an answer to this query for the community benefit. — AshokPeddakotla, Nov 15 '22 at 13:13

LeelaRajesh_Sayana · Answer 1 · 2022-11-30T20:39:20.910

@JoeHo, thank you for pointing the sources that helped you resolve the issue. I am posting the solution here so that other community members facing similar issue would benefit. Making the below modifications to the code helped me resolve the issue.

def json_serial(obj):
    if isinstance(obj, (datetime, date)):
        return obj.isoformat()
    raise TypeError ("Type %s not serializable" % type(obj))
x = datetime.now().isoformat();
timesent = dumps(datetime.now(), default=json_serial);
MSG_TXT = '{{"temperature": {temperature},"humidity": {humidity}, "timesent": {timesent}}}'

My table on the Azure data explorer has the following filed definitions defined.

.create table jsondata (temperature: real, humidity: real, timesent: datetime)

My data mapping query is as below

.create table jsondata ingestion json mapping 'jsonMapping' '[{"column":"humidity","path":"$.humidity","datatype":"real"},{"column":"temperature","path":"$.temperature","datatype":"real"}, {"column":"timesent","path":"$.timesent","datatype":"datetime"}]'

I then connected the Azure Data Explorer table to IoT Hub using the steps outlined in the following resource Connect Azure Data Explorer table to IoT hub

When I execute the program, I could see the Azure IoT Hub telemetry data flow getting bound to the Azure Data explorer table without any issues.

python JSON format invalid

1 Answers1