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

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 2 years ago.

Improve this 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

What didn't work about your attempt using OPENJSON? What was that attempt? — Thom A
– Thom A ♦, Commented Sep 8, 2023 at 11:46
Does this answer your question? SQL Server OPENJSON read nested json — siggemannen
– siggemannen, Commented Sep 8, 2023 at 11:53

user9517769 · Accepted Answer · 2023-09-08 11:54:20Z

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

answered Sep 8, 2023 at 11:54

user9517769

Sign up to request clarification or add additional context in comments.

4 Comments

Thom A Over a year ago

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?

siggemannen Over a year ago

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

ARUNKUMAR RAVI Over a year ago

@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 Over a year ago

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')

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related