JSON to CSV using jq or python

Question

Need help converting this JSON and using a specific element of the Column Dimension as the Column header line in CSV using jq

Input JSONyour text

{
    "Columns": [
        {
            "Id": "ItemName",
            "IsEditable": false
        },
        {
            "Id": "Date",
            "IsEditable": false
        },
        {
            "Id": "Qty",
            "IsEditable": false
        }
    ],
    "Rows": [
        {
            "Values": [
                "ITEM1",
                "10-MAY-2024",
                "100"
            ]
        },
        {
            "Values": [
                "ITEM2",
                "20-MAY-2024",
                "200"
            ]
        },
        {
            "Values": [
                "ITEM3",
                "30-MAY-2024",
                "400"
            ]
        }
    ],
    "QueryHandle": {
        "QueryID": "00101",
        "Worksheet": {
            "Name": "Test20",
            "Scope": "NA"
        },
        "WorkAlloc": "2024-MAY"
    },
    "RowCount": 3
}

Required Output in CSV format ( I only the data below and the rest of the elements in JSON to be ignored).

The header columns come from .Columns[]|.Id

ItemName, Date, Qty
"Item1","10-MAY-2024","100"
"Item2","20-MAY-2024","200"
"Item3","20-MAy-2024","400"

Tried following this post:

How to convert arbitrary simple JSON to CSV using jq?

but haven't been able to get it to work

Answer 1

You need to collect the output rows into arrays. As for the headers, you have alreday given the path yourself: .Columns[].Id. And the actual rows are also already organized into an array.

jq -r '[.Columns[].Id], .Rows[].Values | @csv'

"ItemName","Date","Qty"
"ITEM1","10-MAY-2024","100"
"ITEM2","20-MAY-2024","200"
"ITEM3","30-MAY-2024","400"

Demo

Note: To change capitalization, use ascii_upcase or ascii_downcase. Split into single characters first, if not all of them need to change case, then add them together again.