openweathermap json to csv

Regular expressions and sed aren't a great match for this kind of task, but it's fairly simple using jq, a JSON processing tool:

$ jq -r '.list[]|[.dt, .main[]] | @csv' < data.json
1519333200,271.62,271.62,272.921,1028.3,1037.2,1028.3,88,-1.3
1519344000,271.22,271.22,272.193,1028.11,1037.04,1028.11,100,-0.98

jq takes in a JSON data stream and filters it to extract the data you want, analogously to sed but for structured data rather than text. This command takes each element of the "list" array (.list[]) and plucks out the "dt" field (.dt) of the object inside along with everything in the "main" object too (.main[]), putting the whole lot into an array and converting that to a CSV row (@csv).

If you're interested in particular fields, or you want to reorder them, you can list them out too:

$ jq -r '.list[]|[.dt, .main.temp_min, .main.humidity] | @csv' < data.json
1519333200,271.62,88
1519344000,271.22,100

Just list out each one you care about and ignore the rest.

If you want to get into other parts of the data, you can put that in there too: .clouds will look into the clouds field, or you can read the wind speed with .wind.speed - always a dot before the name, like you were writing JavaScript. The jq manual is quite comprehensive for whatever custom behaviour you need.

Just a quick addenda to excelent Michael Homer answer:

If the jq expression becomes large, we can make a script with it:

Example:

$ cat mkweatherreport
#!/usr/bin/jq -rf
.list[] |
  [
     .dt,
     .main.temp_min,        # in Kelvin degrees
     .main.humidity
  ] | @csv

and then:

$ mkweatherreport f.json
1519333200,271.62,88
1519344000,271.22,100

This may be easiest if we could first extract the elements of the list array, and then convert them to CSV. We can do that with a combination of jq (to extract the individual list elements) and mlr (to convert these elements to CSV in a convenient way):

$ jq '.list[]' file | mlr --j2c cat
dt,main.temp,main.temp_min,main.temp_max,main.pressure,main.sea_level,main.grnd_level,main.humidity,main.temp_kf,weather.1.id,weather.1.main,weather.1.description,weather.1.icon,clouds.all,wind.speed,wind.deg,rain,snow,sys.pod,dt_txt
1519333200,271.62,271.62,272.921,1028.3,1037.2,1028.3,88,-1.3,800,Clear,clear sky,01n,0,4.86,78.004,{},{},n,2018-02-22 21:00:00
1519344000,271.22,271.22,272.193,1028.11,1037.04,1028.11,100,-0.98,800,Clear,clear sky,01n,0,4.52,80.0016,{},{},n,2018-02-23 00:00:00

The jq call would, given the data in the question (corrected to close the array), first produce the equivalent of

{"dt":1519333200,"main":{"temp":271.62,"temp_min":271.62,"temp_max":272.921,"pressure":1028.3,"sea_level":1037.2,"grnd_level":1028.3,"humidity":88,"temp_kf":-1.3},"weather":[{"id":800,"main":"Clear","description":"clear sky","icon":"01n"}],"clouds":{"all":0},"wind":{"speed":4.86,"deg":78.004},"rain":{},"snow":{},"sys":{"pod":"n"},"dt_txt":"2018-02-22 21:00:00"}
{"dt":1519344000,"main":{"temp":271.22,"temp_min":271.22,"temp_max":272.193,"pressure":1028.11,"sea_level":1037.04,"grnd_level":1028.11,"humidity":100,"temp_kf":-0.98},"weather":[{"id":800,"main":"Clear","description":"clear sky","icon":"01n"}],"clouds":{"all":0},"wind":{"speed":4.52,"deg":80.0016},"rain":{},"snow":{},"sys":{"pod":"n"},"dt_txt":"2018-02-23 00:00:00"}

The call to Miller then reads these individual objects, flattening them out, and converts them to CSV (--j2c means "read JSON, write CSV", short for (and added more recently than) --ijson --ocsv).

You would modify the jq expression to delete or modify the fields from each list element that you do not want. For example, to only return the first element from the weather sub-array, you would use .list[] | .weather |= first.