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:

"distance","time","type"
,20,"Event1"
100,,"Event2"
Screen Shot 2020-08-07 at 4 39 16 PM