使用jq将JSON转指定顺序表头CSV时的重复表头问题

问题背景 #

需要将JSON文件转换为指定字段顺序的CSV，要求把JSON中第8个键DeletedOn调整到CSV的第6位。当前使用jq实现时，表头随每个JSON对象重复出现，且表头带方括号，不符合预期。

输入输出示例 #

JSON输入

{"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"}

预期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",

当前错误输出

["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",

现有代码

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"

解决办法 #

问题根源在于：当前代码对每个JSON对象都输出了表头数组，且表头数组未经过@csv转换，导致方括号保留；同时表头重复输出。

修正后的代码使用jq的-n参数（不从输入自动读取JSON），先一次性输出格式化后的表头，再遍历所有输入对象生成数据行：

jq -r -n '
    # 定义表头数组并转成CSV格式，输出一次
    ["ReportingUid", "ThirdParty", "ThirdPartyLinkUid", "ExternalId", "ThirdPartyType", "DeletedOn", "CreatedOn", "UpdatedOn"] | @csv,
    # 遍历所有输入的JSON对象，提取指定字段并转CSV
    inputs | [
        .ReportingUid,
        .ThirdParty,
        .ThirdPartyLinkUid,
        .ExternalId,
        .ThirdPartyType,
        .DeletedOn,
        .CreatedOn,
        .UpdatedOn
    ] | @csv
' "cb_linkage_part.json" > "cb_linkage_part.csv"

代码说明 #

1. -n：禁用jq默认的逐对象读取模式，手动通过inputs处理所有输入的JSON对象
2. 表头数组通过@csv转换为标准CSV格式，仅输出一次
3. inputs遍历所有输入的JSON对象，每个对象按指定顺序提取字段，再用@csv转换为数据行

运行上述代码后，输出将完全符合预期的CSV格式，表头仅出现一次且无方括号。

内容的提问来源于stack exchange，提问作者sunpack