1

I'm trying to use jq to convert a JSON output to CSV.

Here's input.json (stripped down a lot - I may have up to 600 array elements, but the same number for each):

    {
  "latitude": [39.582, 39.582, 39.582],
  "longitude": [26.675, 26.675, 26.675],
  "drivingDifficultyIndex": [0, 34, 34],
  "iconCode": [31, 11, 11],
  "observationTimeUtcIso": ["2016-06-26T00:20:00+0000", "2016-06-26T01:20:00+0000", "2016-06-26T02:20:00+0000"],
  "precip1Hour": [0.0, 0.1, 0.5]
}

My best attempt thus far is:

jq --raw-output 'keys , .[] | @csv'

which makes

"drivingDifficultyIndex","iconCode","latitude","longitude","observationTimeUtcIso","precip1Hour"
39.582,39.582,39.582
26.675,26.675,26.675
0,34,34
31,11,11
"2016-06-26T00:20:00+0000","2016-06-26T01:20:00+0000","2016-06-26T02:20:00+0000"
0,0.1,0.5

How to convert arbirtrary simple JSON to CSV using jq? gives some good hints, but I still end up with the data in rows (like above) rather than columns.

What I'm after is this:

"latitude","longitude","drivingDifficultyIndex","iconCode","observationTimeUtcIso","precip1Hour"
39.582, 26.675, 0,  31, \2016-06-26T00:20:00+0000\",    0
39.582, 26.675, 34, 11, \"2016-06-26T01:20:00+0000\",   0.1
39.582, 26.675, 34, 11, \"2016-06-26T02:20:00+0000\",   0.5

With up to 600 elements in each array, I need the CSV file to have a separate column for each array; rows 2 downwards need transposing.

Can someone help jq produce the output I want?

Thanks...

Update: 

jq --raw-output 'keys_unsorted, map(.[0]) , map(.[1]), map(.[2]) |@csv'

gives me what I want, for the example 3 array members above. But how can I get this to work to account for much larger (unknown) number of array elements?

WeatherBadger
  • 33
  • 7
  • Possible duplicate of [How to convert arbirtrary simple JSON to CSV using jq?](https://stackoverflow.com/questions/32960857/how-to-convert-arbirtrary-simple-json-to-csv-using-jq) – Martin Schneider Jan 12 '18 at 10:20

1 Answers1

4

Indeed, using transpose allows a simple, one-line solution along the lines you envisioned:

( keys_unsorted, ([.[]] | transpose)[]) | @csv

With the sample data, invocation of jq with the -r option yields:

"latitude","longitude","drivingDifficultyIndex","iconCode","observationTimeUtcIso","precip1Hour"
39.582,26.675,0,31,"2016-06-26T00:20:00+0000",0
39.582,26.675,34,11,"2016-06-26T01:20:00+0000",0.1
39.582,26.675,34,11,"2016-06-26T02:20:00+0000",0.5
peak
  • 105,803
  • 17
  • 152
  • 177