1

I have below JSON document to convert into CSV. I need to iterate over the first column with the 2nd and 3rd (they can be either string or array) at the same time as one iteration.

Example document:

[
  {
    "results": [
       [
        "abc025",
        "true",
        "test.lun"
      ],
       [
        "xyz025",
        [
          "true",
          "false",
          "true"
        ],
        [
          "product.lun",
          "app.lun",
          "ora.lun"
        ]
      ]
    ]
  }
]

Expected CSV:

"abc025","true","test.lun"
"xyz025","true","product.lun"
"xyz025","false","app.lun"
"xyz025","true","ora.lun"
5
  • sure, this one has more than 1 array, also I'd like to learn how to iterate the first string with the 2nd and 3rd array at the same index, the expected output shows the different logic from the last one. thanks
    – Henry Liu
    Aug 23, 2021 at 4:51
  • Are the values in the xyz025 array really unquoted, and you want them quoted? There is a big difference between false (a boolean) and "false" (a string) in JSON.
    – Kusalananda
    Aug 23, 2021 at 6:13
  • yes I will edit the post
    – Henry Liu
    Aug 23, 2021 at 6:30
  • this is so the farest I could go, but got xyz025 missing on the 2nd and 3rd rows. '.[].results[] | [ [ .[0][]? // .[0] ], [ .[1][]? // .[1] ], [ .[2][]? // .[2] ] ] | to_entries | (map(.value) | transpose[]) | @csv'
    – Henry Liu
    Aug 23, 2021 at 6:37
  • thanks for the update, please also edit your question to add your reply in comments into body of the question you have, Aug 23, 2021 at 9:58

1 Answer 1

2

Use the jq expression

.[].results[] | .[0] as $name | .[1:] | map([.[]]? // [.]) |
(.[0]|keys[]) as $i | [ $name, .[][$i] ] | @csv

The first line here picks out $name as the first element (.[0]) from each individual sub-array (abc025 and xyz025 respectively, in separate iterations) and then transforms the remaining data in each sub-array (.[1:]) into arrays of one array per column:

[["true"],["test.lun"]]
[["true","false","true"],["product.lun","app.lun","ora.lun"]]

This is done in the map() call where each element is extracted as an array. If that does not work, the element is put into an array. So each element either remains an array, or is converted into a single element array.

The second line iterates over the indexes of these column arrays and creates the output as CSV.

Testing:

$ jq -r '.[].results[] | .[0] as $name | .[1:] | map([.[]]? // [.]) | (.[0]|keys[]) as $i | [ $name, .[][$i] ] | @csv' file
"abc025","true","test.lun"
"xyz025","true","product.lun"
"xyz025","false","app.lun"
"xyz025","true","ora.lun"

Note that this is a generalization of my solution to your previous question. You may use this code with your data from that question too.

You may also use this on data with more than three columns. The document

[
  {
    "results": [
      [ "abc025",
        "true",
        "test.lun",
        "blueberry" ],
      [ "xyz025",
        ["true","false","true"],
        ["product.lun","app.lun","ora.lun"],
        ["strawberry","cloudberry","lingonberry"] ]
    ]
  }
]

would convert into

"abc025","true","test.lun","blueberry"
"xyz025","true","product.lun","strawberry"
"xyz025","false","app.lun","cloudberry"
"xyz025","true","ora.lun","lingonberry"
1
  • 1
    So much appreciated @Kusalananda, you really helped me a lot on the way learning this new tool.
    – Henry Liu
    Aug 23, 2021 at 7:32

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.