Delete info from a JSON using Python

Question

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']

print(out)

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
    **kw)
  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)

the key "Peripherals" is with a big P in your sample

Anton vBR
– Anton vBR

2017-07-26 06:00:31 +00:00
Commented Jul 26, 2017 at 6:00 — Anton vBR
– Anton vBR, Commented Jul 26, 2017 at 6:00
I still receive the same error after its changed

W. Stephens
– W. Stephens

2017-07-26 06:02:30 +00:00
Commented Jul 26, 2017 at 6:02 — W. Stephens
– W. Stephens, Commented Jul 26, 2017 at 6:02

t.m.adam · Accepted Answer · 2017-07-26 06:56:54Z

1

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:
    out_file.write('[\n')
    for i,v in enumerate(dicts):
        out_file.write(json.dumps(v)+('\n' if i == len(dicts)-1 else ',\n'))
    out_file.write(']')

Use this code for properly formatted json data:

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

with open('firetobq_peripheral.json', 'w') as out_file:
    out_file.write(json.dumps(dicts, indent=2))

edited Jul 26, 2017 at 6:56

answered Jul 26, 2017 at 6:06

t.m.adam

15.4k3 gold badges34 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

W. Stephens Over a year ago

Thank you this did exactly what I asked, however I need the JSON to come back with each row on a new line. In my code to download the JSON i added ` out_file.write("\n")`. But that doesnt seem to be working now.

W. Stephens Over a year ago

Is it not possible to keep the file as a JSON?

t.m.adam Over a year ago

You can use the indent argument, eg: indent=2, but it will place every item in a new line.

W. Stephens Over a year ago

could you provide an example of that Im not quite sure how to implement that

W. Stephens Over a year ago

If by "every item will be on a new line" you mean that AppName and foundedPeripheralCount and etc.. will be on new lines that isn't what Im looking for either. I need each set of data to be included in one line but every new set of data on a new line.

|

Collectives™ on Stack Overflow

Delete info from a JSON using Python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related