如何用DuckDB从Ansible生成的JSON中提取动态属性

我用Ansible收集了主机内部信息，得到一个大型JSON结果列表，希望将每台主机上所有网卡的网络详情整理为清晰的表格。

示例JSON #

[
  {
    "_ansible_facts_gathered": true,
    "ansible_all_ipv4_addresses": [
      "10.20.5.204"
    ],
    "ansible_all_ipv6_addresses": [
      "2a00:6020:41e1:fea0:a00:27ff:fee0:63ba",
      "2a00:6020:41e1:fe85:5:f9db:2e3f:1e85",
      "2a00:6020:41e1:fe85:a00:27ff:fee0:63ba",
      "fe80::a00:27ff:fee0:63ba"
    ],
    "ansible_apparmor": {
      "status": "enabled"
    },
    "ansible_architecture": "x86_64",
    "ansible_bios_date": "12/01/2006",
    "ansible_bios_vendor": "innotek GmbH",
    "ansible_fqdn": "ansible-vb-pelikan.fm174.intern",
    "ansible_hostname": "ansible-vb-pelikan",
    "ansible_hostnqn": "",
    "ansible_interfaces": [
      "enp0s3",
      "lo"
    ],
    "ansible_enp0s3": {
      "active": true,
      "device": "enp0s3",
      "ipv4": {
        "address": "10.20.5.204",
        "broadcast": "10.20.5.255",
        "netmask": "255.255.255.0",
        "network": "10.20.5.0",
        "prefix": "24"
      },
      "ipv6": [
        {
          "address": "2a00:6020:41e1:fea0:a00:27ff:fee0:63ba",
          "prefix": "64",
          "scope": "global"
        }
      ]
    },
    "ansible_lo": {
      "active": true,
      "device": "lo",
      "ipv4": {
        "address": "127.0.0.1",
        "broadcast": "",
        "netmask": "255.0.0.0",
        "network": "127.0.0.0",
        "prefix": "8"
      },
      "ipv6": [
        {
          "address": "::1",
          "prefix": "128",
          "scope": "host"
        }
      ]
    }
  },
  {
    "_ansible_facts_gathered": true,
    "ansible_all_ipv4_addresses": [
      "10.20.5.204"
    ],
    "ansible_all_ipv6_addresses": [
      "2a00:6020:41e1:fea0:a00:27ff:fee0:63ba",
      "2a00:6020:41e1:fe85:5:f9db:2e3f:1e85",
      "2a00:6020:41e1:fe85:a00:27ff:fee0:63ba",
      "fe80::a00:27ff:fee0:63ba"
    ],
    "ansible_apparmor": {
      "status": "enabled"
    },
    "ansible_architecture": "x86_64",
    "ansible_bios_date": "12/01/2006",
    "ansible_bios_vendor": "innotek GmbH",
    "ansible_fqdn": "ansible-vb-pelikan.fm174.intern",
    "ansible_hostname": "ansible-vb-pelikan2",
    "ansible_hostnqn": "",
    "ansible_interfaces": [
      "enp0s4",
      "lo1"
    ],
    "ansible_enp0s4": {
      "active": true,
      "device": "enp0s4",
      "ipv4": {
        "address": "10.20.5.204",
        "broadcast": "10.20.5.255",
        "netmask": "255.255.255.0",
        "network": "10.20.5.0",
        "prefix": "24"
      },
      "ipv6": [
        {
          "address": "2a00:6020:41e1:fea0:a00:27ff:fee0:63ba",
          "prefix": "64",
          "scope": "global"
        }
      ]
    },
    "ansible_lo1": {
      "active": true,
      "device": "lo1",
      "ipv4": {
        "address": "127.0.0.1",
        "broadcast": "",
        "netmask": "255.0.0.0",
        "network": "127.0.0.0",
        "prefix": "8"
      },
      "ipv6": [
        {
          "address": "::1",
          "prefix": "128",
          "scope": "host"
        }
      ]
    }
  }
]

提取规则 #

每个主机的网卡名称列在ansible_interfaces属性中：

"ansible_interfaces": [
  "enp0s3",
  "lo"
]

需要动态提取对应的ansible_<interface_name>属性值，例如：

ansible_enp0s3
ansible_lo

期望结果结构 #

┌──────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬──────────┬──────┬────────┬────────┐
│      列名        │                                                            列类型                                                            │ 允许为空 │ 主键 │ 默认值 │ 额外属性 │
│     varchar      │                                                              varchar                                                              │ varchar  │ varchar │ varchar │ varchar │
├──────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────┼──────┼────────┼────────┤
│ 主机名           │ VARCHAR                                                                                                                           │ 是       │      │        │        │
│ 网卡名称         │ VARCHAR                                                                                                                           │ 是       │      │        │        │
│ 网卡字段名       │ VARCHAR                                                                                                                           │ 是       │      │        │        │
│ 网卡详情         │ STRUCT(active BOOLEAN, device VARCHAR, ipv4 STRUCT(address VARCHAR, broadcast VARCHAR, netmask VARCHAR, network VARCHAR, prefix…  │ 是       │      │        │        │
└──────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────┴──────┴────────┴────────┘

内容示例 #

┌──────────────────┬──────────────┬────────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│      主机名      │   网卡名称    │   网卡字段名    │                                                              网卡详情                                                            │
│     varchar      │   varchar    │     varchar    │ struct(active boolean, device varchar, ipv4 struct(address varchar, broadcast varchar, netmask varchar, network varchar, prefix v…  │
├──────────────────┼──────────────┼────────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ ansible-vb-pelikan │ lo         │ ansible_lo     │ {'active': true, 'device': lo, 'ipv4': {'address': 127.0.0.1, 'broadcast': '', 'netmask': 255.0.0.0, 'network': 127.0.0.0, 'prefix'…  │
│ cloud            │ lo          │ ansible_lo     │ {'active': true, 'device': lo, 'ipv4': {'address': 127.0.0.1, 'broadcast': '', 'netmask': 255.0.0.0, 'network': 127.0.0.0, 'prefix'…  │
│ file-server      │ lo          │ ansible_lo     │ {'active': true, 'device': lo, 'ipv4': {'address': 127.0.0.1, 'broadcast': '', 'netmask': 255.0.0.0, 'network': 127.0.0.0, 'prefix'…  │
└──────────────────┴──────────────┴────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

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