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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

{ "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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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!