jq – converting json to csv issue. Unable to convert sub-array

March 11, 2022

I was fighting to understand jq syntax but i have issues with it. I’m trying as some other people to convert json to csv. I found many entries on the forum but none of them seem to work for me. I always get some error or partial result when i narrow down the filter.

Goal:

I grab data from https://www.wunderground.com/ using
https://api.weather.com/v2/pws/history/hourly?stationId=IBONIE3&format=json&units=m&date=20210731&apiKey="
I want convert this data into csv

What i get from source is data for 1 day in 1h time jumps. Data is stored under ‘observations’. That looks like this:

{"observations":[
    {
      "stationID": "IBONIE3",
      "tz": "Europe/Warsaw",
      "obsTimeUtc": "2021-07-31T21:59:50Z",
      "obsTimeLocal": "2021-07-31 23:59:50",
      "epoch": 1627768790,
      "lat": 52.203785,
      "lon": 20.618021,
      "solarRadiationHigh": 0,
      "uvHigh": 0,
      "winddirAvg": 257,
      "humidityHigh": 74,
      "humidityLow": 71,
      "humidityAvg": 71,
      "qcStatus": 1,
      "metric": {
        "tempHigh": 20,
        "tempLow": 20,
        "tempAvg": 20,
        "windspeedHigh": 8,
        "windspeedLow": 0,
        "windspeedAvg": 2,
        "windgustHigh": 12,
        "windgustLow": 0,
        "windgustAvg": 3,
        "dewptHigh": 15,
        "dewptLow": 15,
        "dewptAvg": 15,
        "windchillHigh": 20,
        "windchillLow": 20,
        "windchillAvg": 20,
        "heatindexHigh": 20,
        "heatindexLow": 20,
        "heatindexAvg": 20,
        "pressureMax": 994.58,
        "pressureMin": 993.91,
        "pressureTrend": 0,
        "precipRate": 0,
        "precipTotal": 0
      }

}]}

obviously this is just 1 entry out of 24 in single file.

When i do this:

cat file.json | jq '.observations[0],.observations[0].metric | keys_unsorted | @csv'

"\"stationID\",\"tz\",\"obsTimeUtc\",\"obsTimeLocal\",\"epoch\",\"lat\",\"lon\",\"solarRadiationHigh\",\"uvHigh\",\"winddirAvg\",\"humidityHigh\",\"humidityLow\",\"humidityAvg\",\"qcStatus\",\"metric\""
"\"tempHigh\",\"tempLow\",\"tempAvg\",\"windspeedHigh\",\"windspeedLow\",\"windspeedAvg\",\"windgustHigh\",\"windgustLow\",\"windgustAvg\",\"dewptHigh\",\"dewptLow\",\"dewptAvg\",\"windchillHigh\",\"windchillLow\",\"windchillAvg\",\"heatindexHigh\",\"heatindexLow\",\"heatindexAvg\",\"pressureMax\",\"pressureMin\",\"pressureTrend\",\"precipRate\",\"precipTotal\""

I do get header in proper format, but when i do this

cat file.json | jq -r '.observations[] | map(values) | @csv

jq: error (at <stdin>:1): object ({"tempHigh"...) is not valid in a csv row

i get error. Which is obvious for me as this goes into sub-array called metric and this is where error is being displayed. I can only get these metrics by running this:

cat file.json | jq -r '.observations[].metric | map(values) | @csv
20,18,19,0,0,0,0,0,0,15,14,14,20,18,19,20,18,19,994.58,994.24,0.34,0,0

skipping all the other data, but this is not what i want.

How i can convert this metric array into the non-array object?
Is there any way to do that in single query?

Obviously header cannot have also variable "metric" and instead all the items from that sub-array – metric. I can fix header manually or even skip it, but how to get whole data, not only metric?

>Solution :

If no keys collide, you could integrate the .metric subarray’s items into the actual record:

jq -r '
  .observations[] |= (. + .metric | del(.metric))
  | (.observations[0] | keys_unsorted), (.observations[] | map(values))
  | @csv
' file.json

"stationID","tz","obsTimeUtc","obsTimeLocal","epoch","lat","lon","solarRadiationHigh","uvHigh","winddirAvg","humidityHigh","humidityLow","humidityAvg","qcStatus","tempHigh","tempLow","tempAvg","windspeedHigh","windspeedLow","windspeedAvg","windgustHigh","windgustLow","windgustAvg","dewptHigh","dewptLow","dewptAvg","windchillHigh","windchillLow","windchillAvg","heatindexHigh","heatindexLow","heatindexAvg","pressureMax","pressureMin","pressureTrend","precipRate","precipTotal"
"IBONIE3","Europe/Warsaw","2021-07-31T21:59:50Z","2021-07-31 23:59:50",1627768790,52.203785,20.618021,0,0,257,74,71,71,1,20,20,20,8,0,2,12,0,3,15,15,15,20,20,20,20,20,20,994.58,993.91,0,0,0

Demo

If they happen to collide, add some prefix to their names to disambiguate:

jq -r '
  .observations[] |= (. + (.metric | with_entries(.key |= "metric_\(.)")) | del(.metric))
  | (.observations[0] | keys_unsorted), (.observations[] | map(values))
  | @csv
' file.json

"stationID","tz","obsTimeUtc","obsTimeLocal","epoch","lat","lon","solarRadiationHigh","uvHigh","winddirAvg","humidityHigh","humidityLow","humidityAvg","qcStatus","metric_tempHigh","metric_tempLow","metric_tempAvg","metric_windspeedHigh","metric_windspeedLow","metric_windspeedAvg","metric_windgustHigh","metric_windgustLow","metric_windgustAvg","metric_dewptHigh","metric_dewptLow","metric_dewptAvg","metric_windchillHigh","metric_windchillLow","metric_windchillAvg","metric_heatindexHigh","metric_heatindexLow","metric_heatindexAvg","metric_pressureMax","metric_pressureMin","metric_pressureTrend","metric_precipRate","metric_precipTotal"
"IBONIE3","Europe/Warsaw","2021-07-31T21:59:50Z","2021-07-31 23:59:50",1627768790,52.203785,20.618021,0,0,257,74,71,71,1,20,20,20,8,0,2,12,0,3,15,15,15,20,20,20,20,20,20,994.58,993.91,0,0,0

Demo