W. Stephens W. Stephens - 3 years ago 128
JSON Question

Delete info from a JSON using Python

I am currently exporting a database from firebase into a JSON and want to upload this to Bigquery. However, some of the fieldnames in the database have nested information and Bigquery does not accept it this way. How can I delete 'Peripherals' from every dataset that it is present in in my JSON. It is not present in every dataset though. I provided an example of what the JSON code looks like below. Thanks for the help!

{"AppName": "DataWorks", "foundedPeripheralCount": 1, "version": "1.6.1(8056)", "deviceType": "iPhone 6", "createdAt": "2017-04-05T07:05:30.408Z", "updatedAt": "2017-04-05T07:08:49.569Z", "Peripherals": {"1CA726ED-32B1-43B4-9071-B58BBACE20A8": "Arduino"}, "connectedPeripheralCount": 1, "iOSVersion": "10.2.1"}
{"objectId": "20H5Hg2INB", "foundedPeripheralCount": 0, "DeviceVendorID": "5B7F085E-B3B6-4270-97DC-F42903CDEAC1", "version": "1.3.5(5801)", "deviceType": "iPhone 6", "createdAt": "2015-11-10T06:16:45.459Z", "updatedAt": "2015-11-10T06:16:45.459Z", "connectedPeripheralCount": 0, "iOSVersion": "9.1"}
{"AppName": "DataWorks", "foundedPeripheralCount": 2, "version": "1.6.2(8069)", "deviceType": "iPhone 6s", "createdAt": "2017-04-12T10:05:05.937Z", "updatedAt": "2017-07-06T07:33:02.006Z", "Peripherals": {"060EBAFD-3120-4AAD-8B0A-EC14A323FA25": "28902 ", "identifierInternalSensors": "Internal Sensors", "0521A273-FAA5-462E-B9EC-FBB3D60F5E99": "28895 "}, "connectedPeripheralCount": 8, "iOSVersion": "10.2.1"}

I have tried this

import json
with open('firetobq_peripheral.json') as out_file:
out = json.load(out_file)
for element in out:
del element['Peripherals']


but I receive this error

Traceback (most recent call last):
File "editjson.py", line 3, in <module>
out = json.load(out_file)
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/__init__.py", line 290, in load
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/__init__.py", line 338, in loads
return _default_decoder.decode(s)
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/json/decoder.py", line 369, in decode
raise ValueError(errmsg("Extra data", s, end, len(s)))
ValueError: Extra data: line 2 column 1 - line 629 column 1 (char 311 - 203056)

Answer Source

It looks like the data in 'firetobq_peripheral.json' is not valid json. If each row is on a new line you can use this code:

with open('firetobq_peripheral.json', 'r') as in_file:
    dicts = []
    for line in in_file.readlines() : 
        d = json.loads(line.strip())
        if d.get('Peripherals'): 
            del d['Peripherals']
        dicts += [d]

with open('firetobq_peripheral.json', 'w') as out_file:
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download