如何用JQ将大型自定义JSON转换为CSV？求更优方案

需求说明 #

需要将以IP为键的嵌套JSON数据，转换为扁平化的CSV格式——将嵌套字段用/拼接路径作为表头（如network_zone/0、service/name），并将IP作为_key列。

原始JSON数据 #

{
    "10.10.10.1": {
        "asset_id": 1,
        "referencekey": "ASSET-00001",
        "hostname": "testDev01",
        "fqdn": "ip-10-10.10.1.ap-northeast-2.compute.internal",
        "network_zone": [
            "DEV",
            "Dev"
        ],
        "service": {
            "name": "TEST_SVC",
            "account": "AWS_TEST",
            "billing": "Testpay"
        },
        "aws": {
            "tags": {
                "Name": "testDev01",
                "Service": "TEST_SVC",
                "Usecase": "Dev",
                "billing": "Testpay",
                "OsVersion": "20.04"
            },
            "instance_type": "t3.micro",
            "ami_imageid": "ami-e000001",
            "state": "running"
        }
    },
    "10.10.10.2": {
        "asset_id": 3,
        "referencekey": "ASSET-47728",
        "hostname": "Infra_Live01",
        "fqdn": "ip-10-10-10-2.ap-northeast-2.compute.internal",
        "network_zone": [
            "PROD",
            "Live"
        ],
        "service": {
            "name": "Infra",
            "account": "AWS_TEST",
            "billing": "infra"
        },
        "aws": {
            "tags": {
                "Name": "Infra_Live01",
                "Service": "Infra",
                "Usecase": "Live",
                "billing": "infra",
                "OsVersion": "16.04"
            },
            "instance_type": "r5.large",
            "ami_imageid": "ami-e592398b",
            "state": "running"
        }
    }
}

目标CSV格式 #

_key,asset_id,referencekey,hostname,fqdn,network_zone/0,network_zone/1,service/name,service/account,service/billing,aws/tags/Name,aws/tags/Service,aws/tags/Usecase,aws/tags/billing,aws/tags/OsVersion,aws/instance_type,aws/ami_imageid,aws/state
10.10.10.1,1,ASSET-00001,testDev01,ip-10-10.10.1.ap-northeast-2.compute.internal,DEV,Dev,TEST_SVC,AWS_TEST,Testpay,testDev01,TEST_SVC,Dev,Testpay,20.04,t3.micro,ami-e000001,running
10.10.10.2,3,ASSET-47728,Infra_Live01,ip-10-10-10-2.ap-northeast-2.compute.internal,PROD,Live,Infra,AWS_TEST,infra,Infra_Live01,Infra,Live,infra,16.04,r5.large,ami-e592398b,running

实现方法（JQ） #

JQ是处理JSON数据的高效工具，可直接完成这种扁平化转换，命令如下：

jq -r '
to_entries | map(.value + {_key: .key}) |
(.[0] | paths | map(tostring) | join("/")) as $headers |
[$headers] + map([paths as $p | getpath($p)]) |
@csv
' input.json

命令解释：

1. to_entries | map(.value + {_key: .key})：将顶层IP键值对转换为带_key字段的资产对象数组，把IP存入_key字段。
2. (.[0] | paths | map(tostring) | join("/")) as $headers：从第一个资产对象提取所有字段路径，将路径元素用/拼接，生成CSV表头。
3. [$headers] + map([paths as $p | getpath($p)])：组合表头和所有资产的扁平化值数组。
4. @csv：将数组转换为标准CSV格式，自动处理特殊字符的引号包裹。

兼容调整（针对结构不一致的JSON）：

如果部分资产缺失某些字段，可修改值提取逻辑，用空字符串填充缺失项，避免报错：

jq -r '
to_entries | map(.value + {_key: .key}) |
(.[0] | paths | map(tostring) | join("/")) as $headers |
[$headers] + map([paths as $p | getpath($p)? // ""]) |
@csv
' input.json

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