0

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!

2

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 '
  • Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach? – TechStud Jul 4 '18 at 5:57
  • What I have come up wtih so far is ["key",(map(keys_unsorted) | add | unique)[]],(to_entries | .[] | [.key,.value.good,.value.name,.value.status]) | @csv. I need the last part [.key,.value.good,.value.name,.value.status] to fetch the keys dynamically. Any thoughts? – TechStud Jul 6 '18 at 6:24
  • I now have the reverse... ["Title",(map(keys_unsorted) | add | unique)[]] as $cols | $cols, map(. as $row | $cols | map($row[.]))[] | @csv. ugh. Just need to get this mashed together. I feel like I am so close (albeit, not elegant). – TechStud Jul 6 '18 at 21:03
  • GENIUS! Thanks again @EchoMike444! This works perfectly! (Proof: jqplay.org/s/x7YZoq-YnO ) PS. Typo $oner.value -> $onerow.value – TechStud Jul 8 '18 at 4:32
  • After exporting the JSON data to CSV, and editing the details in Excel, is there a relatively easy way to export it back to a JSON file? – TechStud Jul 9 '18 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.