Bash – Using jq to extract values and format in CSV

bashcsvjqjsonpython

I have the below JSON file:

{
"data": [
    {
        "displayName": "First Name",
        "rank": 1,
        "value": "VALUE"
    },
    {
        "displayName": "Last Name",
        "rank": 2,
        "value": "VALUE"
    },
    {
        "displayName": "Position",
        "rank": 3,
        "value": "VALUE"
    },
    {
        "displayName": "Company Name",
        "rank": 4,
        "value": "VALUE"
    },
    {
        "displayName": "Country",
        "rank": 5,
        "value": "VALUE"
    },
]
}

I would like to have a CSV file in this format:

First Name, Last Name, Position, Company Name, Country
VALUE, VALUE, VALUE, VALUE, VALUE, VALUE

Is this possible by using only jq? I don't have any programming skills.

Best Answer

jq has a filter, @csv, for converting an array to a CSV string. This filter takes into account most of the complexities associated with the CSV format, beginning with commas embedded in fields. (jq 1.5 has a similar filter, @tsv, for generating tab-separated-value files.)

Of course, if the headers and values are all guaranteed to be free of commas and double quotation marks, then there may be no need to use the @csv filter. Otherwise, it would probably be better to use it.

For example, if the 'Company Name' were 'Smith, Smith and Smith', and if the other values were as shown below, invoking jq with the "-r" option would produce valid CSV:

$ jq -r '.data | map(.displayName), map(.value) | @csv' so.json2csv.json
"First Name","Last Name","Position","Company Name","Country"
"John (""Johnnie"")","Doe","Director, Planning and Posterity","Smith, Smith and Smith","Transylvania"
Related Question