Today I Learned

A Hashrocket project

How to convert JSON to CSV with jq

I had this json file that was an array of objects and some of those objects had different keys. I wanted to visualize that data in a spreadsheet (data science, AI, machine learning stuff) so I thought about having a CSV file where each JSON key would become a CSV column.

// file.json
    "type": "Event1",
    "time": 20
    "type": "Event2",
    "distance": 100

jq to the rescue:

cat file.json | jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' > file.csv

Here is the output:

Screen Shot 2020-08-07 at 4 39 16 PM