JSON File - extract only the data in a nested array for CSV file

[shwfgd]

Trying to get the data in a nested array "action_history" from a JSON file. Tried normalizing, but hit a wall.

JSON file looks like this:

{
"783": {        
        "recordID": 783,
        "title": "Test1",
        "date": 1723572991,        
        "action_history": [
            {
                "recordID": 783,               
                "time": 1723573283,                
                "actionType": "submit"               
            },           
            {
                "recordID": 783,               
                "time": 1723573425,               
                "actionType": "Save"
            },
            {
                "recordID": 783,               
                "time": 1723575689,               
                "actionType": "Save"
            },
            {
                "recordID": 783,               
                "time": 1723585061,
                "actionType": "Complete"
            }
        ]       
    },
"900": {        
        "recordID": 900,
        "title": "Test2",
        "date": 1723572825,        
        "action_history": [
            {
                "recordID": 900,               
                "time": 1723573300,                
                "actionType": "submit"               
            },           
            {
                "recordID": 900,               
                "time": 1723573350,               
                "actionType": "Save"
            },
            {
                "recordID": 900,               
                "time": 1723585390,
                "actionType": "Complete"
            }
        ]       
    }
}

My current code:

import csv
import json

def json_to_csv(json_file, csv_file):
    with open(json_file) as f:
        data = json.load(f)

        f = csv.writer(open(csv_file, "w+", newline=""))
        header = ["recordID", "title", "date",
                  "action_time",
                  "action_history"
                  ]
        f.writerow(header)
        for x in data.values():
            result = [x["recordID"],
                      x["title"],
                      x["date"],
                      x["action_history"]
                      ]

            f.writerow(result)


# src, dest, function call
json_file = 'C:\\Users\\VHATUCStoehD\\Desktop\\Simple.json'
csv_file = 'C:\\Users\\VHATUCStoehD\\Desktop\\PY_output.csv'
json_to_csv(json_file, csv_file)

Current output:

Output:
recordID,title,date,action_history
783,Test1,1723572991,"[{'recordID': 783, 'time': 1723573283, 'actionType': 'submit'}, {'recordID': 783, 'time': 1723573425, 'actionType': 'Save'}, {'recordID': 783, 'time': 1723585061, 'actionType': 'Complete'}]"
900,Test2,1723572825,"[{'recordID': 900, 'time': 1723573300, 'actionType': 'submit'}, {'recordID': 900, 'time': 1723573350, 'actionType': 'Save'}, {'recordID': 900, 'time': 1723585390, 'actionType': 'Complete'}]"

Preferred output:

Output:
recordID,actionType,time
783,"submit",1723573283
783,"Save",1723573425
783,"Save",1723575689
783,"Complete",1723585061
900,"submit",1723573300
900,"Save",1723573350
900,"Complete",1723585390

[snippsat]

Something like this should work.

import csv
import json

def json_to_csv(json_file, csv_file):
    with open(json_file) as f:
        data = json.load(f)
        f = csv.writer(open(csv_file, "w+", newline=""))
        header = ["recordID", "actionType", "time"]
        f.writerow(header)
        # Iterate through each record in the JSON data
        for record in data.values():
            record_id = record["recordID"]
            for action in record["action_history"]:
                f.writerow([record_id, action["actionType"], action["time"]])

json_file = 'Simple.json'
csv_file = 'output.csv'
json_to_csv(json_file, csv_file)

[shwfgd]

Beautiful! Thank you!