Replace value in list of dictionary in JSON file-CodePudding

I have a JSON file containing a list of 9000 dictionaries.

Sample

[{"auftrags_id":348667,"vertrags_id":11699,"ursprungsauftrag":"",
"umsatz":0.28,"brutto":0.33,"vertrauensschadenhaftpflicht":"",
"stornoreserve":"","umsatzsteuer_betrag":"0.05","netto":0.28,
"steuerpflichtig":"0","art_der_rechnung":"Rechnung","vp_nummer":538},
{"auftrags_id":348668,"vertrags_id":11699,"ursprungsauftrag":"",
"umsatz":0.28,"brutto":0.33,"vertrauensschadenhaftpflicht":"",
"stornoreserve":"","umsatzsteuer_betrag":"0.05","netto":0.28,
"steuerpflichtig":"0","art_der_rechnung":"Rechnung","vp_nummer":538},
{"auftrags_id":349210,"vertrags_id":24894,"ursprungsauftrag":"X",
"umsatz":0.87,"brutto":1.04,"vertrauensschadenhaftpflicht":"X",
"stornoreserve":"X","umsatzsteuer_betrag":"0.17","netto":0.87,
"steuerpflichtig":"0","art_der_rechnung":"Rechnung","vp_nummer":538}]

To upload the JSON-File to Postgresql, I need to replace the X with a value accepted as float. I think replacing the value 'X' with '0.0001' in every dictionary would do it. Then there are values with no content, "", I don't know how to handle them,maybe also replacing them with '0.0001', just for purpose of uploading.

Desired output:

   [{"auftrags_id":348667,"vertrags_id":11699,"ursprungsauftrag":0.0001,
"umsatz":0.28,"brutto":0.33,"vertrauensschadenhaftpflicht":0.0001,
"stornoreserve":0.0001,"umsatzsteuer_betrag":0.05,"netto":0.28,
"steuerpflichtig":0.0001,"art_der_rechnung":"Rechnung","vp_nummer":538},
{"auftrags_id":348668,"vertrags_id":11699,"ursprungsauftrag":0.0001,
"umsatz":0.28,"brutto":0.33,"vertrauensschadenhaftpflicht":0.0001,
"stornoreserve":0.0001,"umsatzsteuer_betrag":0.05,"netto":0.28,
"steuerpflichtig":0.0001,"art_der_rechnung":"Rechnung","vp_nummer":538},
{"auftrags_id":349210,"vertrags_id":24894,"ursprungsauftrag":0.0001,
"umsatz":0.87,"brutto":1.04,"vertrauensschadenhaftpflicht":0.0001,
"stornoreserve":0.0001,"umsatzsteuer_betrag":0.17,"netto":0.87,
"steuerpflichtig":"0","art_der_rechnung":"Rechnung","vp_nummer":538}]

I already have a code to upload the file, but i need to clean the JSON file for Postgresql to accept it. Appreciate any help!

CodePudding user response：

You could use sed as suggested in the comment under your question. It is a command for the Linux command line (shell). See an article about Linux shell here and about the sed command here.

The Python solution:

#!/usr/bin/python3
import json  # load the builtin JSON module

JSON_FILE_NAME = "dictionaries.json"  # the name of your file with those dictionaries
RESULT_FILE_NAME = "result.json"  # the name of the file that will be created

# Load the file contents into variable dictionaries
with open(JSON_FILE_NAME, "r", encoding="utf8") as file:
    dictionaries = json.load(file)

result = []

for dictionary in dictionaries:  # loop over the dictionaries
    for key, value in dictionary.items():  # loop over the key and value pairs in the dictionary
        if value in ("", "X"):
            # if the value is an empty string or "X", change it to 0.0001
            dictionary[key] = 0.0001
    # append the dictionary to the result list
    result.append(dictionary)

# save the result to a file
with open(RESULT_FILE_NAME, "w", encoding="utf8") as file:
    json.dump(result, file)