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
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"
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.