Есть ли лучший способ извлечь значения из этого JSON?

Question

Я нашел этот пример в https://www.sqlshack.com/extract-scalar-values-from-json-data-using-json_value/

DECLARE @data NVARCHAR(4000);
SET @data = N'{
    "Employees": [
        {
            "EmpName": "Rohan Sharma",
            "Department": "IT",
            "Address": "101, Sector 5, Gurugram, India",
            "Salary": 100000
        },
        {
            "EmpName": "Manohar Lal",
            "Department": "Human Resources",
            "Address": "17, Park Avenue, Mumbai, India",
            "Salary": 78000
        }
    ]
}';
SELECT JSON_VALUE(@data, '$.Employees[0].EmpName') AS 'Name', 
       JSON_VALUE(@data, '$.Employees[0].Department') AS 'Department', 
       JSON_VALUE(@data, '$.Employees[0].Address') AS 'Address', 
       JSON_VALUE(@data, '$.Employees[0].Salary') AS 'Salary'
UNION ALL
SELECT JSON_VALUE(@data, '$.Employees[1].EmpName') AS 'Name', 
       JSON_VALUE(@data, '$.Employees[1].Department') AS 'Department', 
       JSON_VALUE(@data, '$.Employees[1].Address') AS 'Address', 
       JSON_VALUE(@data, '$.Employees[1].Salary') AS 'Salary'

Интересно, есть ли лучшие способы получить все значения в JSON, не указывая $.Employees индексы?

Например, если у меня есть N компонентов в этом JSON, мне не нужно UNION ALL N раз.

В моем проекте будет более 40 компонентов в одном JSON.

DECLARE @data NVARCHAR(4000);
SET @data = N'{
    "Employees": [
        {
            "EmpName": "Rohan Sharma",
            "Department": "IT",
            "Address": "101, Sector 5, Gurugram, India",
            "Salary": 100000
        },
        {
            "EmpName": "Manohar Lal",
            "Department": "Human Resources",
            "Address": "17, Park Avenue, Mumbai, India",
            "Salary": 78000
        },
        {
            "EmpName": "Emp 03",
            "Department": "Demo",
            "Address": "Address03",
            "Salary": 9999
        },
        {
            "EmpName": "Emp 04",
            "Department": "Demo",
            "Address": "Address04",
            "Salary": 9999
        },
        {
            "EmpName": "Emp N",
            "Department": "Demo",
            "Address": "AddressN",
            "Salary": 9999
        }
    ]
}';
SELECT JSON_VALUE(@data, '$.Employees[0].EmpName') AS 'Name', 
       JSON_VALUE(@data, '$.Employees[0].Department') AS 'Department', 
       JSON_VALUE(@data, '$.Employees[0].Address') AS 'Address', 
       JSON_VALUE(@data, '$.Employees[0].Salary') AS 'Salary'
UNION ALL
SELECT JSON_VALUE(@data, '$.Employees[1].EmpName') AS 'Name', 
       JSON_VALUE(@data, '$.Employees[1].Department') AS 'Department', 
       JSON_VALUE(@data, '$.Employees[1].Address') AS 'Address', 
       JSON_VALUE(@data, '$.Employees[1].Salary') AS 'Salary'
UNION ALL
SELECT JSON_VALUE(@data, '$.Employees[2].EmpName') AS 'Name', 
       JSON_VALUE(@data, '$.Employees[2].Department') AS 'Department', 
       JSON_VALUE(@data, '$.Employees[2].Address') AS 'Address', 
       JSON_VALUE(@data, '$.Employees[2].Salary') AS 'Salary'
UNION ALL
SELECT JSON_VALUE(@data, '$.Employees[3].EmpName') AS 'Name', 
       JSON_VALUE(@data, '$.Employees[3].Department') AS 'Department', 
       JSON_VALUE(@data, '$.Employees[3].Address') AS 'Address', 
       JSON_VALUE(@data, '$.Employees[3].Salary') AS 'Salary'
UNION ALL
SELECT JSON_VALUE(@data, '$.Employees[N].EmpName') AS 'Name', 
       JSON_VALUE(@data, '$.Employees[N].Department') AS 'Department', 
       JSON_VALUE(@data, '$.Employees[N].Address') AS 'Address', 
       JSON_VALUE(@data, '$.Employees[N].Salary') AS 'Salary'

Есть ли лучший способ извлечь значения из этого JSON?

Ответы (2)