gistfile1.txt

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