JSON formatted data is a common way of sending data over MQTT.
Storing the data directly as JSON formatted data is also common
However it is also very common to store this data as CSV or a least convert it to CSV for use in legacy systems.
Converting Simple JSON Data
This is actually quite straight forward provided the data is a single python dictionary object without any nesting.
This is easier to explain and see with example data . So if we take the data shown below:
{"ms":115318,"Urms":0,"Umin":0,"Umax":87,"count":mcount}
A CSV file consists of a header which contains the column names followed by rows of data.
The elements are normally separated by a comma
column1, column2, column3,
row1 1,21,345
row2 2 ,23,347
What we need to do is to extract the column names which are essentially the keys in the JSON data and then extract the data which are the values.
We could do this manually using code like that below:
To extract the keys and create the header use:
columns =""
for key in data:
print("key =",key)
if columns =="":
columns=key
else:
columns=columns+","+key
print(columns)
this gives us an array of keys as shown in the screen shot:
You can then extract the data elements by looping though them as shown below:
line_out=""
for key in data:
#print("here ",data[key])
if line_out =="":
line_out=str(data[key])
else:
line_out=line_out+","+str(data[key])
print(line_out)
Now we see the data as shown in the screen shot:
Now we just need to write it to a file so our psuedo code is
get header
write header
loop data
get data
write data
end loop
and example code is shown below:
filename="test.csv"
fo=open(filename, 'w')
for key in data:
print("key =",key)
if columns =="":
columns=key
else:
columns=columns+","+key
print(columns)
fo.write(columns+"\n")
#start data loop here
line_out=""
for key in data:
#print("here ",data[key])
if line_out =="":
line_out=str(data[key])
else:
line_out=line_out+","+str(data[key])
print(line_out)
fo.write(line_out+"\n")
#end data loop here
fo.close()
Usage Examples
Both the MQTT logger and topic logger include this conversion code.
Related tutorials and resources:
- JSON Basics For Beginners-With Examples and Exercises
- Beginners Guide to Data and Character Encoding