SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @json NVARCHAR(MAX) = '
[
    {
        "resourceType": "virtualMachines",
        "name": "Standard_E16-4as_v5",
        "tier": "Standard",
        "size": "E16-4as_v5",
        "family": "standardEASv5Family",
        "locations": ["SouthAfricaWest"],
        "locationInfo": [
            {
                "location": "SouthAfricaWest",
                "zones": [],
                "zoneDetails": []
            }
        ],
        "capabilities": [
            {
                "name": "MaxResourceVolumeMB",
                "value": "0"
            },
            {
                "name": "vCPUs",
                "value": "16"
            }
        ],
        "restrictions": []
    },
    {
        "resourceType": "virtualMachines",
        "name": "Standard_E15",
        "tier": "Standard",
        "size": "E15",
        "family": "standardEASv5Family",
        "locations": ["Africa"],
        "locationInfo": [
            {
                "location": "Africa",
                "zones": [],
                "zoneDetails": []
            }
        ],
        "capabilities": [
            {
                "name": "MaxResourceVolumeMB",
                "value": "25"
            },
            {
                "name": "vCPUs",
                "value": "18"
            }
        ],
        "restrictions": []
    }
]';

WITH JsonTable AS (
    SELECT 
        JSON_VALUE(value, '$.name') AS VMName,
        JSON_QUERY(value, '$.capabilities') AS Capabilities
    FROM OPENJSON(@json)
    WHERE JSON_VALUE(value, '$.resourceType') = 'virtualMachines'
)

SELECT
    JT.VMName AS Name,
    MAX(CASE WHEN Cap.name = 'vCPUs' THEN Cap.value ELSE NULL END) AS vCPUs,
    MAX(CASE WHEN Cap.name = 'MaxResourceVolumeMB' THEN Cap.value ELSE NULL END) AS MaxResourceVolumeMB
FROM JsonTable JT
CROSS APPLY OPENJSON(Capabilities) 
WITH (
    name NVARCHAR(100) '$.name',
    value NVARCHAR(100) '$.value'
) AS Cap
GROUP BY JT.VMName

Name	vCPUs	MaxResourceVolumeMB
Standard_E15	18	25
Standard_E16-4as_v5	16	0

Warning: Null value is eliminated by an aggregate or other SET operation.