How to transform a complex,nested json into csv

Question

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.

peak · Accepted Answer · 2019-07-20 19:28:53Z

2

To produce an array of the desired values:

.step as $step
| .inputs
| to_entries[]
| .key as $key
| [$step, .key]
  + (.value | if type=="object" then to_entries[] | [.key, .value] else [$key, .] end)

Tacking on @csv at the end would produce CSV values, with numbers unquoted. Using the -r command-line option and making minor corrections to the sample input would produce:

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

If you really want the output as indicated in the Q, then conditionally add the quotation marks ("\"") where desired, and use join(",") instead of @csv.

answered Jul 20, 2019 at 19:28

peak

119k21 gold badges185 silver badges218 bronze badges

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

Comments

Boris Lipschitz · Accepted Answer · 2019-07-20 18:25:24Z

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())

Collectives™ on Stack Overflow

How to transform a complex,nested json into csv

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related