How to transform a complex,nested json into csv

by Alby   Last Updated July 20, 2019 18:26 PM

Here's the input json

{
    "step": 1,
    "inputs": {
        "first_input": {
            "file_id": "xxx"
        }
        "second_input": 3
    }
}
{
    "step": 2,
    "inputs": {
        "my_first_arg": {
            "file_id": "yyy"
        }
        "my_second_arg": 0
    }
}

The desired csv file is

1,first_input,"xxx"
1,second_input,3
2,my_first_arg:"yyy"
2,my_second_arg,0

The challenge here is that each inputs field contains a dictionary with different key names and the different types of values. Basically I want the value of the step field, all keys of inputs, and the children nodes of values of inputs, but having related data stay in the same row in the csv.



Answers 1


Consider using python and it's json module to parse the json, then you can do pretty much whatever you want. Here is an example that does pretty much what you want. So, i'd say, get it to spit it out exactly the way you want, change it to load the json from file, change the csv output from StringIO to an actual file, and you are pretty much done.

import json, csv, io

json_str = '[{"step":1,"inputs":{"first_input":{"file_id":"xxx"},"second_input":3}},{"step":2,"inputs":{"my_first_arg":{"file_id":"yyy"},"my_second_arg":0}}]'

def value(jsn):
    if not isinstance(jsn, dict):
        return jsn
    return next(iter(jsn))

jsndata = json.loads(json_str)
csvdata = io.StringIO()
csvwriter = csv.writer(csvdata)
for i in jsndata:
    step = i['step']
    for key in i['inputs']:
        val = value(i['inputs'][key])
        csvwriter.writerow([step, key, val]);

print(csvdata.getvalue())
Boris Lipschitz
Boris Lipschitz
July 20, 2019 18:25 PM

Related Questions


Updated August 10, 2019 18:26 PM

Updated January 31, 2018 23:26 PM

Updated January 29, 2019 21:26 PM

Updated August 09, 2016 08:12 AM

Updated March 11, 2017 19:26 PM