Convert json to csv using jq, with array nested within array

Question

How can I flatten this json array and write out to a csv file using jq so that each row contains all the fields i.e.

I'm a bit of a novice with jq but started with this, which does not capture the inner nested array

jq -r '(.[0] | keys_unsorted) as $keys | $keys, map([.[ $keys[] ]])[]'

[
   {
      "name":"PM2.5",
      "unit":"ug/m3",
      "values":[
         {
            "timestamp":"2020-11-16T13:10:21.135Z",
            "value":3.696
         },
         {
            "timestamp":"2020-11-16T13:10:31.649Z",
            "value":3.696
         },
         {
            "timestamp":"2020-11-16T13:10:42.35Z",
            "value":3.696
         },
         {
            "timestamp":"2020-11-16T13:10:52.953Z",
            "value":3.696
         }
      ]
   },
   {
      "name":"PM10",
      "unit":"ug/m3",
      "values":[
         {
            "timestamp":"2020-11-16T13:10:21.135Z",
            "value":7.596
         },
         {
            "timestamp":"2020-11-16T13:10:31.649Z",
            "value":7.596
         },
         {
            "timestamp":"2020-11-16T13:10:42.35Z",
            "value":7.596
         },
         {
            "timestamp":"2020-11-16T13:10:52.953Z",
            "value":7.596
         }
      ]
   }
]

To a csv as follows:

"name", "unit", "timestamp", "value"
"PM2.5", "ug/m3", "2020-11-16T13:10:21.135Z", 3.696
"PM2.5", "ug/m3", "2020-11-16T13:10:31.649Z", 3.696
"PM2.5", "ug/m3", "2020-11-16T13:10:42.35Z", 3.696
"PM2.5", "ug/m3", "2020-11-16T13:10:52.953Z", 3.696  
"PM10", "ug/m3", "2020-11-16T13:10:21.135Z", 7.596
"PM10", "ug/m3", "2020-11-16T13:10:31.649Z", 7.596
"PM10", "ug/m3", "2020-11-16T13:10:42.35Z", 7.596
"PM10", "ug/m3", "2020-11-16T13:10:52.953Z", 7.596

@Shawn yes sorry was trying to add but was getting flagged for being mostly code by stack overflow, I have added an example now — William Grimes
– William Grimes, Commented Nov 16, 2020 at 14:44

peak · Accepted Answer · 2020-11-17 11:48:39Z

5

$ jq -r '
  [["name","unit","timestamp","value"]], 
   [ .[] 
     | [ .name, .unit ] + 
       (.values[] | [.timestamp,.value]) ] 
  | .[] 
  | @csv' input.json 
"name","unit","timestamp","value"
"PM2.5","ug/m3","2020-11-16T13:10:21.135Z",3.696
"PM2.5","ug/m3","2020-11-16T13:10:31.649Z",3.696
"PM2.5","ug/m3","2020-11-16T13:10:42.35Z",3.696
"PM2.5","ug/m3","2020-11-16T13:10:52.953Z",3.696
"PM10","ug/m3","2020-11-16T13:10:21.135Z",7.596
"PM10","ug/m3","2020-11-16T13:10:31.649Z",7.596
"PM10","ug/m3","2020-11-16T13:10:42.35Z",7.596
"PM10","ug/m3","2020-11-16T13:10:52.953Z",7.596

Builds an array of arrays, one per row including a header line, and then formats them all as CSV records.

edited Nov 17, 2020 at 11:48

peak

119k21 gold badges185 silver badges218 bronze badges

answered Nov 16, 2020 at 14:28

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

William Grimes Over a year ago

Thanks not quite the output format I was after, I have improved the question

peak Over a year ago

This can be simplified (and made more efficient) by removing the need for the final .[], as follows: '["name", "unit", "timestamp", "value"] , (.[] | [.name, .unit] + (.values[] | [.timestamp, .value])) | @csv'

Collectives™ on Stack Overflow

Convert json to csv using jq, with array nested within array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related