Export JSON to CSV with Headers using JQ

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP











up vote
0
down vote

favorite
1












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!







share|improve this question

























    up vote
    0
    down vote

    favorite
    1












    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!







    share|improve this question























      up vote
      0
      down vote

      favorite
      1









      up vote
      0
      down vote

      favorite
      1






      1





      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!







      share|improve this question













      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!









      share|improve this question












      share|improve this question




      share|improve this question








      edited Jul 6 at 5:19
























      asked Jul 4 at 2:01









      TechStud

      34




      34




















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          1
          down vote



          accepted










          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 '





          share|improve this answer























          • Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach?
            – TechStud
            Jul 4 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 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 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 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 at 14:24










          Your Answer







          StackExchange.ready(function()
          var channelOptions =
          tags: "".split(" "),
          id: "106"
          ;
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function()
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled)
          StackExchange.using("snippets", function()
          createEditor();
          );

          else
          createEditor();

          );

          function createEditor()
          StackExchange.prepareEditor(
          heartbeatType: 'answer',
          convertImagesToLinks: false,
          noModals: false,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          );



          );








           

          draft saved


          draft discarded


















          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f453343%2fexport-json-to-csv-with-headers-using-jq%23new-answer', 'question_page');

          );

          Post as a guest






























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          1
          down vote



          accepted










          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 '





          share|improve this answer























          • Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach?
            – TechStud
            Jul 4 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 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 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 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 at 14:24














          up vote
          1
          down vote



          accepted










          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 '





          share|improve this answer























          • Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach?
            – TechStud
            Jul 4 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 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 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 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 at 14:24












          up vote
          1
          down vote



          accepted







          up vote
          1
          down vote



          accepted






          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 '





          share|improve this 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 '






          share|improve this answer















          share|improve this answer



          share|improve this answer








          edited Jul 8 at 16:34


























          answered Jul 4 at 4:19









          EchoMike444

          3942




          3942











          • Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach?
            – TechStud
            Jul 4 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 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 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 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 at 14:24
















          • Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach?
            – TechStud
            Jul 4 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 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 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 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 at 14:24















          Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach?
          – TechStud
          Jul 4 at 5:57




          Amazing! Thanks @EchoMike444. That works for defined / known string/keys, but is there a dynamic approach?
          – TechStud
          Jul 4 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 at 6:24




          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 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 at 21:03





          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 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 at 4:32




          GENIUS! Thanks again @EchoMike444! This works perfectly! (Proof: jqplay.org/s/x7YZoq-YnO ) PS. Typo $oner.value -> $onerow.value
          – TechStud
          Jul 8 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 at 14:24




          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 at 14:24












           

          draft saved


          draft discarded


























           


          draft saved


          draft discarded














          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f453343%2fexport-json-to-csv-with-headers-using-jq%23new-answer', 'question_page');

          );

          Post as a guest













































































          Popular posts from this blog

          How to check contact read email or not when send email to Individual?

          Displaying single band from multi-band raster using QGIS

          How many registers does an x86_64 CPU actually have?