How may I convert this JSON into CSV?

Question

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"

Answer 1

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"