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

Improve this question
2

1 Answer 1

Reset to default
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

Improve this answer
4
  • 2
    A good answer explains the solution. Also, out of interest, why use JSON_QUERY and JSON_VALUE and not a WITH clause inside the CTE?
    – Thom A
    Commented Sep 8, 2023 at 12:03
  • Yes, watch out for JSON_QUERY, is a bit dangerous cause it only supports 4000 characters output so larger jsons becomes NULL, you can instead use AS JSON in the WITH clause to return whole json node to the "next" level
    – siggemannen
    Commented Sep 8, 2023 at 12:42
  • @Amira Bedhiafi Thanks its working and I have one doubt "For this scenario need to use group by clause to group vm name so to accept group by did you used MAX or some others reasons? "
    – ARUNKUMAR RAVI
    Commented Sep 8, 2023 at 12:47
  • I have done one simple query based on your input SELECT JSON_VALUE(JT.value, '$.name') 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 openJson(@Json , '$') JT CROSS APPLY OPENJSON(JT.value, '$.capabilities') WITH ( name NVARCHAR(100) '$.name', value NVARCHAR(100) '$.value' ) AS Cap GROUP BY JSON_VALUE(JT.value, '$.name')
    – ARUNKUMAR RAVI
    Commented Sep 8, 2023 at 12:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.