Converting JSON to CSV using jq with header line

Question

I have a task to convert a JSON file to a CSV with a header at the top. The header has to be in a particular order. So the order of the keys in the JSON file will be in different order than the header in the CSV output file. In this case, "DeletedOn" in the JSON file is the last key (8th position) but in the CSV output file it has to be in the 6th position. Here is a sample of the JSON input:

{"ReportingUid":"75a333a2-f939-8a42-bb31-d6f10366193e","ThirdParty":"Pharmacy","ThirdPartyLinkUid":"92a63912-7703-4bb9-9176-004b41be3c09","ExternalId":"9994:9042722","ThirdPartyType":"Pharmacy","CreatedOn":"2019-04-25T15:03:45+00:00","UpdatedOn":null,"DeletedOn":"2019-04-25T15:23:20+00:00"}
{"ReportingUid":"3d0431b1-72d8-092c-471d-3d60407af689","ThirdParty":"Pharmacy","ThirdPartyLinkUid":"e1e18066-2f43-43db-a141-99ec26103243","ExternalId":"9994:9042729","ThirdPartyType":"Pharmacy","CreatedOn":"2019-04-25T18:47:35+00:00","UpdatedOn":null,"DeletedOn":"2019-04-25T19:09:11+00:00"}

This is the desired output I wish to achieve:

"ReportingUid","ThirdParty","ThirdPartyLinkUid","ExternalId","ThirdPartyType","DeletedOn","CreatedOn","UpdatedOn"
"75a333a2-f939-8a42-bb31-d6f10366193e","Pharmacy","92a63912-7703-4bb9-9176-004b41be3c09","9994:9042722","Pharmacy","2019-04-25T15:23:20+00:00","2019-04-25T15:03:45+00:00",
"3d0431b1-72d8-092c-471d-3d60407af689","Pharmacy","e1e18066-2f43-43db-a141-99ec26103243","9994:9042729","Pharmacy","2019-04-25T19:09:11+00:00","2019-04-25T18:47:35+00:00",

I have been able to figure out how to order the header file to my specifications but the header file keeps repeating for each JSON object. My output:

["ReportingUid","ThirdParty","ThirdPartyLinkUid","ExternalId","ThirdPartyType","DeletedOn","CreatedOn","UpdatedOn"]
"75a333a2-f939-8a42-bb31-d6f10366193e","Pharmacy","92a63912-7703-4bb9-9176-004b41be3c09","9994:9042722","Pharmacy","2019-04-25T15:23:20+00:00","2019-04-25T15:03:45+00:00",
["ReportingUid","ThirdParty","ThirdPartyLinkUid","ExternalId","ThirdPartyType","DeletedOn","CreatedOn","UpdatedOn"]
"3d0431b1-72d8-092c-471d-3d60407af689","Pharmacy","e1e18066-2f43-43db-a141-99ec26103243","9994:9042729","Pharmacy","2019-04-25T19:09:11+00:00","2019-04-25T18:47:35+00:00",

I need to get rid of the repeating header and also the [] for each header line. Here is my code:

jq -r -c '["ReportingUid", "ThirdParty", "ThirdPartyLinkUid", "ExternalId", "ThirdPartyType", "DeletedOn", "CreatedOn", "UpdatedOn"],(. | [
    ."ReportingUid",
    ."ThirdParty",
    ."ThirdPartyLinkUid",
    ."ExternalId",
    ."ThirdPartyType",
    ."DeletedOn",
    ."CreatedOn",
    ."UpdatedOn"] | @csv)' "cb_linkage_part.json" > "cb_linkage_part.csv"

pmf · Accepted Answer · 2025-10-31 18:15:00Z

3

You may want to re-use the list of keys (which is hard-coded here, but could also be imported from outside using e.g. the --args command-line option) for both printing the header and referencing into each record object. Printing the header just once is achieved by using inputs in combination with the -n or --null-input command-line flag.

# jq -nr
[
    "ReportingUid",
    "ThirdParty",
    "ThirdPartyLinkUid",
    "ExternalId",
    "ThirdPartyType",
    "DeletedOn",
    "CreatedOn",
    "UpdatedOn"
] as $keys
| $keys, (inputs | [.[$keys[]]])
| @csv

"ReportingUid","ThirdParty","ThirdPartyLinkUid","ExternalId","ThirdPartyType","DeletedOn","CreatedOn","UpdatedOn"
"75a333a2-f939-8a42-bb31-d6f10366193e","Pharmacy","92a63912-7703-4bb9-9176-004b41be3c09","9994:9042722","Pharmacy","2019-04-25T15:23:20+00:00","2019-04-25T15:03:45+00:00",
"3d0431b1-72d8-092c-471d-3d60407af689","Pharmacy","e1e18066-2f43-43db-a141-99ec26103243","9994:9042729","Pharmacy","2019-04-25T19:09:11+00:00","2019-04-25T18:47:35+00:00",

Demo

answered Oct 31 at 18:15

pmf

38.3k3 gold badges31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sunpack Oct 31 at 18:49

thanks this worked and much shorter! I just had to add an apostrophe before the first [ bracket and after @csv. Also thanks for the tip regarding the input command line flag

Collectives™ on Stack Overflow

Converting JSON to CSV using jq with header line

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related