如何使用MS SQL从JSON数组的特定对象中读取指定字段值

假设你的JSON数据已存储在变量或表字段中，以下是提取每个虚拟机的name、MaxResourceVolumeMB和vCPUs字段的SQL查询：

DECLARE @json NVARCHAR(MAX) = N'[
    {
        "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": []
    }
]';

SELECT
    vm.name,
    MAX(CASE WHEN cap.name = 'MaxResourceVolumeMB' THEN cap.value END) AS MaxResourceVolumeMB,
    MAX(CASE WHEN cap.name = 'vCPUs' THEN cap.value END) AS vCPUs
FROM OPENJSON(@json)
WITH (
    name NVARCHAR(100) '$.name',
    capabilities NVARCHAR(MAX) '$.capabilities' AS JSON
) AS vm
CROSS APPLY OPENJSON(vm.capabilities)
WITH (
    name NVARCHAR(100) '$.name',
    value NVARCHAR(100) '$.value'
) AS cap
GROUP BY vm.name;

查询结果： #

代码说明： #

1. OPENJSON(@json) 解析外层虚拟机JSON数组，通过WITH子句直接提取name字段，并将capabilities数组保留为JSON类型供后续解析。
2. CROSS APPLY OPENJSON(vm.capabilities) 逐个解析每个虚拟机的capabilities数组，提取其中的指标名称和对应值。
3. 用GROUP BY和条件聚合MAX(CASE...)，将每个虚拟机的不同能力指标转换为独立列，得到规整的表格结果。

内容的提问来源于stack exchange，提问作者ARUNKUMAR RAVI