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

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?

CodePudding user response：

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