Search code examples
jsonexport-to-csvjq

Convert a random json to csv

I have a json file that is outputted by an internal process. The format is not like a typical json. I need to convert this to a flat csv.

{"id":"321321","date1":"01APR2021","date2":"01APR2021","date3":"25MAY2021","numField1":0,"Status":"Success","strField1":null,"listField":[{"name":"val1","version":"v1.1","value":122,"str1":"this is another text","str2":"","str3":"","str4":"","str5":"","str6":"this is a text"},{"name":"val2","version":"v1.1","value":-789,"str1":"","str2":"","str3":"","str4":"","str5":"","str6":""},{"name":"val3","version":"v1.1","value":123,"str1":"","str2":"","str3":"","str4":"","str5":"","str6":""},{"name":"val4","version":"v1.1","value":234,"str1":"","str2":"","str3":"","str4":"","str5":"","str6":"and yet another text"}]}
{"id":"345626","date1":"02APR2021","date2":"02APR2021","date3":"25MAY2021","numField1":0,"Status":"Success","strField1":null,"listField":[{"name":"val1","version":"v1.1","value":588,"str1":"this is another text in row2","str2":"","str3":"","str4":"","str5":"","str6":"this is a text"},{"name":"val2","version":"v1.1","value":189,"str1":"","str2":"","str3":"","str4":"","str5":"","str6":""},{"name":"val3","version":"v1.1","value":521,"str1":"","str2":"","str3":"","str4":"","str5":"","str6":""},{"name":"val4","version":"v1.1","value":453,"str1":"","str2":"","str3":"","str4":"","str5":"","str6":"and yet another text"}]}

I have tried two separate things so far:

cat test | jq -r "[ .id, .date1, .date2, .date3, .numField1, .Status, .strField1 ] | @csv"
>>
"321321","01APR2021","01APR2021","25MAY2021",0,"Success",
"345626","02APR2021","02APR2021","25MAY2021",0,"Success",

cat test | jq -r ".listField | .[] | flatten | @csv"
>>
"val1","v1.1",122,"this is another text","","","","","this is a text"
"val2","v1.1",-789,"","","","","",""
"val3","v1.1",123,"","","","","",""
"val4","v1.1",234,"","","","","","and yet another text"
"val1","v1.1",588,"this is another text in row2","","","","","this is a text"
"val2","v1.1",189,"","","","","",""
"val3","v1.1",521,"","","","","",""
"val4","v1.1",453,"","","","","","and yet another text"

I need output in two formats:

  1. Repeat the output from first code for every row in the 2nd output to get a "long format data".
"321321","01APR2021","01APR2021","25MAY2021",0,"Success","val1","v1.1",122,"this is another text","","","","","this is a text"
"321321","01APR2021","01APR2021","25MAY2021",0,"Success","val2","v1.1",-789,"","","","","",""
"321321","01APR2021","01APR2021","25MAY2021",0,"Success","val3","v1.1",123,"","","","","",""
"321321","01APR2021","01APR2021","25MAY2021",0,"Success","val4","v1.1",234,"","","","","","and yet another text"
"345626","02APR2021","02APR2021","25MAY2021",0,"Success","val1","v1.1",588,"this is another text in row2","","","","","this is a text"
"345626","02APR2021","02APR2021","25MAY2021",0,"Success","val2","v1.1",189,"","","","","",""
"345626","02APR2021","02APR2021","25MAY2021",0,"Success","val3","v1.1",521,"","","","","",""
"345626","02APR2021","02APR2021","25MAY2021",0,"Success","val4","v1.1",453,"","","","","","and yet another text"
  1. Concatenate the 4 rows from 2nd output into one row in a fixed order to get a "wide format data".
"321321","01APR2021","01APR2021","25MAY2021",0,"Success","val1","v1.1",122,"this is another text","","","","","this is a text","val2","v1.1",-789,"","","","","","","val3","v1.1",123,"","","","","","","val4","v1.1",234,"","","","","","and yet another text"
"345626","02APR2021","02APR2021","25MAY2021",0,"Success","val1","v1.1",588,"this is another text in row2","","","","","this is a text","val2","v1.1",189,"","","","","","","val3","v1.1",521,"","","","","","","val4","v1.1",453,"","","","","","and yet another text"
Solution

  • Assuming all the objects in .listField are conformal:

    First task

    [ .id, .date1, .date2, .date3, .numField1, .Status, .strField1 ]
+ (.listField[] | [.[]] )
| @csv

    Second task

    [ .id, .date1, .date2, .date3, .numField1, .Status, .strField1 ]
+ [.listField[] | .[] ]
| @csv

    I'd avoid flatten if possible, as it tends to hide surprises.