jsonsql-serveropen-jsonsql-server-json

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


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

enter image description here

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


Solution

  • 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