convert very large custom json to csv using jq bash

Question

have a very large JSON data like below

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

Can I use JQ to make the conversion like below? Or is there an easier way to solve it? Thank you

Expected result

_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

Answer 1

jq let's you do the conversion to CSV easily. The following code produces the desired output:

jq -r 'to_entries
       | map([.key, 
              .value.asset_id, .value.referencekey, .value.hostname, .value.fqdn,
              .value.network_zone[0], .value.network_zone[1],
              .value.service.name, .value.service.account, .value.service.billing,
              .value.aws.tags.Name, .value.aws.tags.Service, .value.aws.tags.Usecase, .value.aws.tags.billing, .value.aws.tags.OsVersion,
              .value.aws.instance_type, .value.aws.ami_imageid, .value.aws.state])
       | ["_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"]
         , .[]
       | @csv' "$INPUT"

Remarks

• If some nodes in the input JSON are missing, the code does not break but fills in empty values in the CSV file.
• If more than two network zones are given, only the first two are covered in the CSV file