How to select dynamic attributes from JSON with DuckDB?

NorbertK
July 30, 2024
121 views
0 votes
2 Answers

I use ansible to select internal details of my hosts and get a large JSON list of the results. I want to see the networking details of every interface on every host in a nice table.

Sample 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"
        }
      ]
    }
  }
]

For each host, the interface names are listed in the ansible_interfaces attribute:

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

And I need to dynamically extract each corresponding 'ansible_' || <interface_name> value, e.g.

ansible_enp0s3
ansible_lo

The end result should have the following structure :

┌──────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│     column_name      │                                                            column_type                                                            │  null   │   key   │ default │  extra  │
│       varchar        │                                                              varchar                                                              │ varchar │ varchar │ varchar │ varchar │
├──────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ ansible_hostname     │ VARCHAR                                                                                                                           │ YES     │         │         │         │
│ interface_name       │ VARCHAR                                                                                                                           │ YES     │         │         │         │
│ interface_column_n…  │ VARCHAR                                                                                                                           │ YES     │         │         │         │
│ interface_data       │ STRUCT(active BOOLEAN, device VARCHAR, ipv4 STRUCT(address VARCHAR, broadcast VARCHAR, netmask VARCHAR, network VARCHAR, prefix…  │ YES     │         │         │         │
└──────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────┴─────────┴─────────┴─────────┘

The content should be like this

┌────────────────────┬────────────────┬──────────────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  ansible_hostname  │ interface_name │ interface_column_n…  │                                                              interface_data                                                            │
│      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'…  │
└────────────────────┴────────────────┴──────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Tags: duckdb json

Answers

Chosen as BEST ANSWER

The user adityawarmanfw on Discord did the work to solve this problem. His demonstration is here:

WITH t AS (
  SELECT j
  FROM read_json('https://gist.githubusercontent.com/adityawarmanfw/3107de51d1901eb3b918bcc70a5ffb31/raw/d73e365a492f6ceb50ed25816b5a90fcf57b8eb6/ansible-test.json') AS j
), get_interface_name AS (
  SELECT 
      j->> '$.ansible_hostname' AS hostname, 
      concat('ansible_', unnest(j->> '$.ansible_interfaces[*]')) AS interface_name
  FROM t  
)
SELECT 
    get_interface_name.hostname,
    interface_name, 
    from_json(interface_data, '{"active":"BOOLEAN","device":"VARCHAR","ipv4":{"address":"VARCHAR","broadcast":"VARCHAR","netmask":"VARCHAR","network":"VARCHAR","prefix":"VARCHAR"},"ipv6":[{"address":"VARCHAR","prefix":"VARCHAR","scope":"VARCHAR"}]}') AS interface_data
FROM get_interface_name
LEFT JOIN LATERAL (
    SELECT
        t.j->> '$.ansible_hostname' AS hostname, 
        json(t.j->> concat('$.', get_interface_name.interface_name)) AS interface_data
    FROM t
  ) get_interfaces ON TRUE
WHERE get_interface_name.hostname = get_interfaces.hostname

(Edit)

This is based on trying to understand how
https://stackoverflow.com/a/78676516/ works.

Returning each row as a struct

https://duckdb.org/docs/sql/expressions/subqueries#returning-each-row-of-the-subquery-as-a-struct

If you use the "table name" in a select query, you get a struct for each row:

from 'ansible-test.json' as tbl
select tbl

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                    tbl                                                    │
│ struct(_ansible_facts_gathered boolean, ansible_all_ipv4_addresses varchar[], ansible_all_ipv6_addresse…  │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ {'_ansible_facts_gathered': true, 'ansible_all_ipv4_addresses': [10.20.5.204], 'ansible_all_ipv6_addres…  │
│ {'_ansible_facts_gathered': true, 'ansible_all_ipv4_addresses': [10.20.5.204], 'ansible_all_ipv6_addres…  │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────┘

JSON extraction

https://duckdb.org/docs/extensions/json#json-extraction-functions

The struct can be parsed as JSON, which allows the use of dynamic extraction paths:

from 'ansible-test.json' as tbl
select
   ansible_hostname,
   json_extract(tbl, 'ansible_' || unnest(ansible_interfaces)) as interface_data

┌─────────────────────┬────────────────────────────────────────────────────────────────────────────────────────────────┐
│  ansible_hostname   │                                         interface_data                                         │
│       varchar       │                                              json                                              │
├─────────────────────┼────────────────────────────────────────────────────────────────────────────────────────────────┤
│ ansible-vb-pelikan  │ {"active":true,"device":"enp0s3","ipv4":{"address":"10.20.5.204","broadcast":"10.20.5.255","…  │
│ ansible-vb-pelikan  │ {"active":true,"device":"lo","ipv4":{"address":"127.0.0.1","broadcast":"","netmask":"255.0.0…  │
│ ansible-vb-pelikan2 │ {"active":true,"device":"enp0s4","ipv4":{"address":"10.20.5.204","broadcast":"10.20.5.255","…  │
│ ansible-vb-pelikan2 │ {"active":true,"device":"lo1","ipv4":{"address":"127.0.0.1","broadcast":"","netmask":"255.0.…  │
└─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────┘

Transforming JSON

https://duckdb.org/docs/extensions/json#transforming-json

The interface_data JSON is then transformed into a STRUCT (which requires providing a schema)

from (
   from 'ansible-test.json' as tbl
   select
      ansible_hostname,
      json_transform(
         json_extract(tbl, 'ansible_' || unnest(ansible_interfaces)),
         '{"active":"BOOLEAN","device":"VARCHAR",
           "ipv4":{"address":"VARCHAR","broadcast":"VARCHAR","netmask":"VARCHAR","network":"VARCHAR","prefix":"VARCHAR"},
           "ipv6":[{"address":"VARCHAR","prefix":"VARCHAR","scope":"VARCHAR"}]}'
      ) as interface_data
)
select 
   ansible_hostname, 
   interface_data.device as interface_name,
   'ansible_' || interface_data.device as interface_column_name,
   interface_data

┌─────────────────────┬────────────────┬──────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────┐
│  ansible_hostname   │ interface_name │ interface_column_n…  │                                      interface_data                                       │
│       varchar       │    varchar     │       varchar        │ struct(active boolean, device varchar, ipv4 struct(address varchar, broadcast varchar, …  │
├─────────────────────┼────────────────┼──────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────┤
│ ansible-vb-pelikan  │ enp0s3         │ ansible_enp0s3       │ {'active': true, 'device': enp0s3, 'ipv4': {'address': 10.20.5.204, 'broadcast': 10.20.…  │
│ ansible-vb-pelikan  │ lo             │ ansible_lo           │ {'active': true, 'device': lo, 'ipv4': {'address': 127.0.0.1, 'broadcast': , 'netmask':…  │
│ ansible-vb-pelikan2 │ enp0s4         │ ansible_enp0s4       │ {'active': true, 'device': enp0s4, 'ipv4': {'address': 10.20.5.204, 'broadcast': 10.20.…  │
│ ansible-vb-pelikan2 │ lo1            │ ansible_lo1          │ {'active': true, 'device': lo1, 'ipv4': {'address': 127.0.0.1, 'broadcast': , 'netmask'…  │
└─────────────────────┴────────────────┴──────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────┘

Please signup or login to give your own answer.

Click here to cancel reply.