How to Convert JSON data into a Dataset form to add in Table.
There are several approaches to this that have been developed in this recent thread:
JSON to Ignition Dataset
They all work, so I suppose which one you choose will depend on what flavor of convolution your development team prefers.
how to solve this error
Traceback (most recent call last):
File "", line 9, in
KeyError: 'results'
This is my json code. The same type data is going to be added after certain interval.
{"device_id": "f8f0056d6be3","device_type": "Disinfection","time_date":
1702309720,"firmware_version": "v2.21j_Quad_DM_WFI, Board r0","pressure_in":
20,"pressure_concentrate": 0,"pressure_out": 0,"gal_total_outlet":
623930,"flow_inlet_gpm": 0.00,"flow_outlet_gpm": 0.00,"tds_inlet":
396,"tds_outlet": 76,"temp_inlet": 70.8,"pump_on": 1,"solenoid_open":
1,"pct_rem_prefilter": 0,"pct_rem_membrane": 83,"pct_rem_aux_pre":
0,"pct_rem_aux_post": 0,"total_use_prefilter": 218243,"total_use_membrane":
166269,"total_use_aux_pre": 218243,"total_use_aux_post":
166269,"days_rem_aux_pre": 0,"days_rem_prefilter": 0,"days_rem_membrane":
1427,"days_rem_aux_post": 0,"usage_est_daily_outlet": 584,"max_gal_prefilter":
100000,"max_gal_membrane": 1000000,"max_gal_aux_pre":
100000,"max_gal_aux_post": 60000,"recovery_pct": 79,"rpm_set":
2300,"rpm_meas": 0,"pressure_pump_in": 20,"temp_eBox": 16.8,"temp_motor":
183.4,"valves": "11-0010-0","resist_12": 0.00,"resist_memb": 0.00,"effic":
"Std","tds_inlet_contin": 396,"tds_outlet_contin": 76,"outlet_resist":
0.25,"UV_irradiance": 2.81,"UV_dosage": 999.90,"UV_bulb_days_rem":
0,"status_msg": "CHECK OUTLET FLOWMETER","outlet_flow_sensor_failure":
1,"solenoid_driver_fault": 1,"alert_UV_irradiance": 1,"pump_hrs": 173.2}**strong text**
As much as we like to think we're magicians some of the time, we don't know which code you decided to use, nor what line 9 is. Although that error is pretty easy to decipher. The code is trying to get a "results" key from an object (dict?) and it doesn't exist in the dict keys
1 Like
I used the code from here.
I just Copy pasted it.
https://forum.inductiveautomation.com/t/json-to-ignition-dataset/82274/4
That code reads in a json file from a file on the gateway. Is that what you want to do?
Not necessarily. If this is Vision, and a valid file path is supplied, it will read the file from the client. It's Christmas where I live, so I won't have time to look at this today.
2 Likes
Ah yes, I didn't see the vision tag!
Still, if this is all he did
Then it won't work either way. It needs the file path to work
Thank You for your time. I will provide detailed question.
Happy Christmas!
The error is occurring is because the example JSON doesn't use a 'results' key. That said, the linked dataset converter also assumes that the JSON file will have more than one row of data, or perhaps better stated: a nested data structure. Consequently, the copy and pasted code would also fail for this use case since this JSON is just flat list of key value pairs.
To create an initial dataset using this file, the code would have to be simplified to look like this:
filepath = 'C:/example/file/path/rowData.json'
# Get the file as a string
jsonString = system.file.readFileAsString(filepath)
# Load the file as a json object
jsonFile = system.util.jsonDecode(jsonString)
# Get the headers from the first key's keys
headers = jsonFile.keys()
# Initialize a data list for or the row lists that will comprise the data in the dataset
data = [[jsonFile[header] for header in headers]]
# Convert the headers and data into a dataset
dataset = system.dataset.toDataSet(headers, data)
Result:
To add a row to an existing dataset in a table using a single row file, the code will look like this:
filepath = 'C:/example/file/path/rowData.json'
# Get the file as a string
jsonString = system.file.readFileAsString(filepath)
# Load the file as a json object
jsonFile = system.util.jsonDecode(jsonString)
# Get the headers from the first key's keys
headers = jsonFile.keys()
# Get the existing dataset from whereever it is stored
dataset = event.source.parent.getComponent('Power Table').data
# Get the next row from the flat JSON file
row = [jsonFile[header] for header in headers]
# Create a new dataset by adding a row to the old one
newDataset = system.dataset.addRow(dataset, dataset.rowCount, row)
# Replace the old dataset with the new one
event.source.parent.getComponent('Power Table').data = newDataset
Result:
1 Like