Transposing jq array elements (building x*y key/value pairs from x arrays of y items)

I have the following AWS CLI JSON output that contains Instance ID, IP address and some other values from my AWS tags (a list of desired FQDNs and if a proxy should be used or not):

[
    [
        {
            "InstanceId": "i-11111111111111111",
            "ClusterName": "vpn",
            "Ip": "192.168.0.1",
            "FqdnList": "vpn.example.com",
            "Proxy": null
        }
    ],
    [
        {
            "InstanceId": "i-22222222222222222",
            "ClusterName": "sql",
            "Ip": "192.168.0.2",
            "FqdnList": "db.example.com;sql.example.com",
            "Proxy": "nginx"
        }
    ],
    [
        {
            "InstanceId": "i-33333333333333333",
            "ClusterName": "nginx",
            "Ip": "192.168.0.3",
            "FqdnList": "nginx.example.com;db.example.com;sql.example.com",
            "Proxy": null
        }
    ],
    [
        {
            "InstanceId": "i-44444444444444444",
            "ClusterName": "vpn",
            "Ip": "192.168.0.4",
            "FqdnList": "vpn.example.com",
            "Proxy": null
        }
    ],
    [
        {
            "InstanceId": "i-55555555555555555",
            "ClusterName": "nginx",
            "Ip": "192.168.0.5",
            "FqdnList": "nginx.example.com;db.example.com;sql.example.com",
            "Proxy": null
        }
    ]
]

My final goal is to use this JSON to build the list of DNS A records (for another script that populates our DNS zone), so I am trying to transpose it to something simpler such as:

{
    "vpn.example.com": [
        "192.168.0.1",
        "192.168.0.4",
    ],

    "nginx.example.com": [
        "192.168.0.3"
        "192.168.0.5"
    ],
    "db.example.com": [
        "192.168.0.3"
        "192.168.0.5"
    ],
    "sql.example.com": [
        "192.168.0.3"
        "192.168.0.5"
    ]
}

or

[
    {
        "name": "vpn.example.com",
        "value": "192.168.0.1"
    },
    {
        "name": "vpn.example.com",
        "value": "192.168.0.4"
    },
    {
        "name": "nginx.example.com",
        "value": "192.168.0.3"
    },
    {
        "name": "nginx.example.com",
        "value": "192.168.0.5"
    },
    {
        "name": "db.example.com",
        "value": "192.168.0.5"
    },
    {
        "name": "db.example.com",
        "value": "192.168.0.3"
    },
    {
        "name": "sql.example.com",
        "value": "192.168.0.3"
    },
    {
        "name": "sql.example.com",
        "value": "192.168.0.5"
    }
]

I've managed to remove the items that are being a proxy (Proxy != null, because their addresses are already repeated in the proxy's tag) and make an array from the FQDNs string using:

jq '
  .[].[]
  | select(.Proxy | . == null)
  | {
      Ip: .Ip,
      Fqdns: .FqdnList | split(";"),
    }
  '

Which gives:

{
  "Ip": "192.168.0.1",
  "Fqdns": [
    "vpn.example.com"
  ]
}
{
  "Ip": "192.168.0.3",
  "Fqdns": [
    "nginx.example.com",
    "db.example.com",
    "sql.example.com"
  ]
}
{
  "Ip": "192.168.0.4",
  "Fqdns": [
    "vpn.example.com"
  ]
}
{
  "Ip": "192.168.0.5",
  "Fqdns": [
    "nginx.example.com",
    "db.example.com",
    "sql.example.com"
  ]
}

But how do I "create" rows/items for each item in Fqdns arrays?

I'm not sure it's even possible in jq.

It can easily be done in a PHP/Python script but I rather keep it "simple".

Can map() + group_by() be useful like in this SO answer? "Transposing" objects in jq

reduce over the items, split the .FqdnList string at ;, and populate an array in the result object using the substrings as keys and the .Ip as array item.

reduce .[][] as $i ({}; .[$i.FqdnList | splits(";")] += [$i.Ip])

{
  "vpn.example.com": [
    "192.168.0.1",
    "192.168.0.4"
  ],
  "db.example.com": [
    "192.168.0.2",
    "192.168.0.3",
    "192.168.0.5"
  ],
  "sql.example.com": [
    "192.168.0.2",
    "192.168.0.3",
    "192.168.0.5"
  ],
  "nginx.example.com": [
    "192.168.0.3",
    "192.168.0.5"
  ]
}

Demo

To include the .Proxy condition, change the generator along the lines of: reduce (.[][] | select(.Proxy)) as ….

For the alternative output, use your initial attempt, wrap it into an array (to get the outer array), and replace split with splits as it evaluates to a stream of items which automatically multiplies the surrounding object (or manually generate the stream using .[]).

[.[][] | {name: .FqdnList | splits(";"), value: .Ip}]

[
  {
    "name": "vpn.example.com",
    "value": "192.168.0.1"
  },
  {
    "name": "db.example.com",
    "value": "192.168.0.2"
  },
  {
    "name": "sql.example.com",
    "value": "192.168.0.2"
  },
  {
    "name": "nginx.example.com",
    "value": "192.168.0.3"
  },
  {
    "name": "db.example.com",
    "value": "192.168.0.3"
  },
  {
    "name": "sql.example.com",
    "value": "192.168.0.3"
  },
  {
    "name": "vpn.example.com",
    "value": "192.168.0.4"
  },
  {
    "name": "nginx.example.com",
    "value": "192.168.0.5"
  },
  {
    "name": "db.example.com",
    "value": "192.168.0.5"
  },
  {
    "name": "sql.example.com",
    "value": "192.168.0.5"
  }
]

Demo

Similarly here, to include the .Proxy condition, change the generator along the lines of: [.[][] | select(.Proxy) | …].