JQ – Export JSON to CSV with Headers

csvjqjsontext processing

I have a (dictionary?) JSON file, similar to: 

{
  "Salad": {
    "name": "Dressing",
    "good": true,
    "status": true
  },
  "Data_XML": {
    "name": "XML",
    "good": false
  },
  "Functionality": {
    "name": "FUNC",
    "good": true
  },
  "Data_JSON": {
    "name": "JSON",
    "good": true,
    "status": false
  }
}

I am looking for a CSV output like this: 

"title","good","name","status"
"Salad",true,"Dressing",true
"Data_XML",false,"XML",""
"Functionality",true,"FUNC",""
"Data_JSON",true,"JSON",false

What I have found and used so far is this… 

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

Output: 

"good","name","status"
true,"Dressing",true
false,"XML",
true,"FUNC",
true,"JSON",false

I would need the equivalent of Salad, Data_XML, Functionality and Data_JSON as the FIRST COLUMN (unsorted) along with the related data.

As for the "good","name","status" these would be the Header fields and as such would need to be dynamically retrieved as they're random (each data set may have 4 and others as much as 10). It's a relatively large file and would be hard to parse these by hand, leaving the "title" as the first column header item. "title","good","name","status","something","else","random","etc"

I feel I am so close… Any help is greatly appreciated!

Best Answer

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 '
Related Question