如何通过jq将AWS备份作业JSON转换为带表头的CSV？

问题场景 #

需要将AWS Backup作业的JSON数据转换为包含指定表头（AccountId、BackupJobId、BackupVaultName等）的CSV文件，目前已实现数据值的CSV转换，但无法添加对应表头。

示例JSON数据 #

{"BackupJobs": [{"AccountId": "12345678","BackupJobId": "asd8f6wehrnvas-cker2-dfne","BackupVaultName": "aws/efs/automatic-backup-vault","BackupVaultArn": "arn:aws:backup:ha-middleeast-1:12345678:backup-vault:aws/efs/automatic-backup-vault","RecoveryPointArn": "arn:aws:backup:ha-middleeast-1:12345678:recovery-point:asd8f6wehrnvas-cker2-dfne","ResourceArn": "arn:aws:elasticfilesystem:ha-middleeast-1:12345678:file-system/fs-47jsg3y47","CreationDate": "2023-12-31T10:30:00+05:30","CompletionDate": "2023-01-09T11:56:40.074000+05:30","State": "COMPLETED","PercentDone": "100.0","BackupSizeInBytes": 0,"IamRoleArn": "arn:aws:iam::12345678:role/aws-service-role/backup.amazonaws.com/backuprole","CreatedBy": {"BackupPlanId": "aws/efs/asd8f6wehrnvas-cker2-dfne","BackupPlanArn": "arn:aws:backup:ha-middleeast-1:12345678:backup-plan:aws/efs/asd8f6wehrnvas-cker2-dfne","BackupPlanVersion": "asd8f6wehrnvas-cker2-dfne","BackupRuleId": "asd8f6wehrnvas-cker2-dfne"},"StartBy": "2023-01-09T18:30:00+05:30","ResourceType": "EFS","IsParent": false},{"AccountId": "12345678","BackupJobId": "asd8f6wehrnvas-cker2-dfne","BackupVaultName": "aws/efs/automatic-backup-vault","BackupVaultArn": "arn:aws:backup:ha-middleeast-1:12345678:backup-vault:aws/efs/automatic-backup-vault","RecoveryPointArn": "arn:aws:backup:ha-middleeast-1:12345678:recovery-point:asd8f6wehrnvas-cker2-dfne","ResourceArn": "arn:aws:elasticfilesystem:ha-middleeast-1:12345678:file-system/fs-47jsg3y47","CreationDate": "2023-12-31T10:30:00+05:30","CompletionDate": "2023-01-09T11:56:40.074000+05:30","State": "COMPLETED","PercentDone": "100.0","BackupSizeInBytes": 0,"IamRoleArn": "arn:aws:iam::12345678:role/aws-service-role/backup.amazonaws.com/backuprole","CreatedBy": {"BackupPlanId": "aws/efs/asd8f6wehrnvas-cker2-dfne","BackupPlanArn": "arn:aws:backup:ha-middleeast-1:12345678:backup-plan:aws/efs/asd8f6wehrnvas-cker2-dfne","BackupPlanVersion": "asd8f6wehrnvas-cker2-dfne","BackupRuleId": "asd8f6wehrnvas-cker2-dfne"},"StartBy": "2023-01-09T18:30:00+05:30","ResourceType": "EFS","IsParent": false}]}

现有无表头转换命令 #

aws backup list-backup-jobs --output json |
jq -r '.BackupJobs | map([.AccountId,.BackupJobId,.BackupVaultName,.RecoveryPointArn,.ResourceArn,.CreationDate,.CompletionDate,.State,.PercentDone,.BackupSizeInBytes,.ResourceType] 
                   | join(", ")) 
                   | join("\n")'

解决方案：添加表头的jq命令 #

只需在现有逻辑前插入表头数组，再和数据数组合并后用标准CSV格式化输出：

aws backup list-backup-jobs --output json |
jq -r '["AccountId","BackupJobId","BackupVaultName","RecoveryPointArn","ResourceArn","CreationDate","CompletionDate","State","PercentDone","BackupSizeInBytes","ResourceType"],
       (.BackupJobs | map([.AccountId,.BackupJobId,.BackupVaultName,.RecoveryPointArn,.ResourceArn,.CreationDate,.CompletionDate,.State,.PercentDone,.BackupSizeInBytes,.ResourceType]))
       | @csv'

关键说明 #

• 用@csv替代手动join(", ")，能自动处理含特殊字符（逗号、引号等）的字段，生成符合规范的CSV文件。
• 表头数组的元素顺序必须和数据提取的字段顺序完全一致，确保表头与数据列一一对应。

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