How to read a value from a particular object in a list with JSON string using MS SQL

Question

[{
        "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": []
    }
    ]

I What to achieve exactly like this table :

Need to know how to read a value from a particular object in a list with JSON string using MS SQL I want to get the values from the above json like the attached table image.

For Example: In first Object of list has name : "Standard_E16-4as_v5" for that I neeed to know the what's the "MaxResourceVolumeMB" and "vCPUs" value

Answer 1

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

fiddle