jsonbashjqexport-to-csv

Using jq to parse multilevel json


I'm trying to parse a JSON file that look like this:

[
  {
    "date":"date1",
    "results":
      [  
        {
            "field1":"string11",
            "field2":"string12",
            "field3":"string13",
        },
        {
            "field1":"string21",
            "field2":"string22",
            "field3":"string23",
        }
      ]
  },
  {
    "date":"date2",
    "results":
      [  
        {
            "field1":"string11",
            "field2":"string12",
            "field3":"string13",
        },
        {
            "field1":"string21",
            "field2":"string22",
            "field3":"string23",
        }
      ]
  }
]

and I would like an output like this:

08/05/2024,string11,string12,string13
08/05/2024,string21,string22,string23
09/05/2024,string11,string12,string13
09/05/2024,string21,string22,string23

I tried:

jq -Mr '.. | objects | select(has("field1")) | [.field1, .field2, .field3,] | @csv' file.json

and got:

string11,string12,string13
string21,string22,string23
string11,string12,string13
string21,string22,string23

and tried:

jq -r '.[] | [.date, .results[].field1, .results[].field2, .results[].field3] | @csv' file.json

and got only the first line:

date1,string11,string12,string13
date2,string11,string12,string13

and I would like to have:

date1,string11,string12,string13
date1,string21,string22,string23
date2,string11,string12,string13
date2,string21,string22,string23

Solution

  • Save the .date, and re-use it once traversed down into .results, either by using a variable (Demo), or by concatenating with another filter (Demo).

    jq -r '.[] | . as {$date} | .results[] | [$date, .[]] | @csv'
    # or
    jq -r '.[] | [.date] + (.results[] | [.[]]) | @csv'
    
    "date1","string11","string12","string13"
    "date1","string21","string22","string23"
    "date2","string11","string12","string13"
    "date2","string21","string22","string23"
    

    Replace the latter .[] with .field1, .field2, .field3 to be explicit.