JQ – Handling Null (Empty Array) While Converting JSON to CSV with JQ

csvjqjsonUbuntu

When trying to convert json to csv, I get an error about null with the following jq query:

printf "[]" | jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'

It gives the following error,

jq: error (at <stdin>:0): Cannot iterate over null (null)

but works fine if the json array is non-empty and has an object in it:

$ printf '[{"a":1}]' | jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'
"a"
1

The jq grammar looks great, but I am not familiar with it as I just started to use this tool.

Can someone please explain how to patch the query to output nothing when the array is empty? (first example).

Best Answer

You can use a Error Suppression / Optional Operator: ? block to counter against an empty array invocation and return without throwing any errors to the console as

printf '[]' | jq -r '.? |(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv'

where .? can also be written explicitly using a try catch block as try .? and have rest of the filter following it. The part of the filter after the optional operator is skipped if the expression fails.

Related Solutions

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.

JQ – Export JSON to CSV with Headers

I think this will work :

jq  -r '["title","name","good","status"],(to_entries|.[]|  
        [.key,.value.good,.value.name,
          (if .value.status == null then "" else .value.status end )]
        )|@csv'

New version more generic

jq -r ' to_entries as $row | 
  ( ( map(keys_unsorted ) | add | unique ) as $cols |
    ( ["title" , $cols] | flatten) , 
  ( $row | .[] as $onerow | $onerow | 
       ( [ .key , ( $cols |   
           map ($onerow.value[.] as $v | if $v == null then "" else $v end )  ) ] 
         | flatten ) ) ) | @csv '

Best Answer

Related Solutions

Openweathermap json to csv

JQ – Export JSON to CSV with Headers

Related Question