Structured JSON to CSV with jq

Question

Using jq I try to convert a JSON to CSV. Here's how my input JSON looks like:

{
  "rows": [
    {
      "a": ["x","y"],
      "b": "some",
      "c": "string"
    },
    {
      "a": ["u","v"],
      "b": "another",
      "c": "string"
    }
  ]
}

The desired output is like:

a   | b       | c
x y | some    | string
u v | another | string

Edit: And for those complaining that this wouldn't be valid CSV, here in RFC 4180 compliant syntax:

a,b,c
x y,some,string
u v,another,string

Using .rows, I successfully get the array:

[{"a":["x","y"],"b":"some","c":"string"},{"a":["u","v"],"b":"another","c":"string"}]

But .rows | @csv says: object ({"a":["x","...) is not valid in a csv row. So, I somehow have to join the array in key "a".

Using .rows[] | .a | join(" ") I get:

"x y"
"u v"

But how do I get this back into my JSON to then use ... | @csv to get my desired CSV data?

Answer 1

With your input, the following jq program:

.rows[]
| map_values(if type == "array" then join(" ") else . end)
| [.[]]
| @csv

produces the comma-separated-values output:

"x y","some","string"
"u v","another","string"

It's a simple matter to add the headers, so I'll leave that to you. Also, let me point out that since you specified CSV output, using @csv is strongly advised, but if for some reason you want to avoid those sometimes-superfluous quotation marks, you can fiddle with the string-valued values to insert the quotation marks as needed, and then use join(",") instead of @csv.

Alternatively, you might like to weave @tsv into your solution.

Caveat

The solution above assumes the a/b/c keys are in the same order throughout, and that the objects have no additional keys, etc. If safety is an issue, then simply modify the line [.[]] in the program to specify exactly what you want.

See also:

For some ideas about handling headers, ensuring consistency, and genericity, see:

jq: Object cannot be csv-formatted, only array

Answer 2

A bit complicated is you want to rotate output.
Tab delimitted output printed in console, but looks like desired except pipes:

function rotate2CSV(inp) {
  var cols = {}; // will be collected in originalKey:[column values]
  for(var rowNo=0;rowNo<inp.rows.length;rowNo++) {
      var rowData = inp.rows[rowNo];
      for(var colsData in rowData) {
          if(!cols[colsData])  cols[colsData] = [];
          cols[colsData].push(rowData[colsData])
      }
  }
  var res = [[]]; // colls rotated content [header keys], [1st row content], ...
  for(var colsData in cols) {
    var col = res[0].indexOf(colsData);
    if(col < 0) {
      col = res[0].length;
      res[0].push(colsData);
    }
    if(col > -1) {
      for(var row=0;row<cols[colsData].length;row++) {
        if(res.length - 1 <= row) res.push([]);
        var cell = cols[colsData][row];
        if(cell instanceof Array) cell = cell.join(' ');
        res[row+1].push(cell);
      }

    }
  }
  for(var rowNo=0;rowNo<res.length;rowNo++) {
    console.log(res[rowNo].join('\t'))
  }
}
rotate2CSV(
  {
    "rows": [
        {
            "a": ["x", "y"],
            "b": "some",
            "c": "string"
        },
        {
            "a": ["u", "v"],
            "b": "another",
            "c": "string"
        }
    ]
  }
);