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

avatar
Chayanan Leksunthorn
9 августа 2021 в 03:09
68
2
1

Я нашел этот пример в 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'
Источник

Ответы (2)

avatar
SteveC
9 августа 2021 в 03:39
1

Вы можете использовать OPENJSON и указать схему, используя WITH примерно так. Столбец "EmpName" считывается из JSON и переименовывается в "Имя"

.
select *
from openjson(@data, N'$.Employees') 
     with (Name      nvarchar(200) N'strict $.EmpName',
           Department   nvarchar(20),
           [Address]    nvarchar(20),
           Salary       nvarchar(20));
Name            Department      Address                 Salary
Rohan Sharma    IT              101, Sector 5, Gurug    100000
Manohar Lal     Human Resources 17, Park Avenue, Mum    78000
Emp 03          Demo            Address03               9999
Emp 04          Demo            Address04               9999
Emp N           Demo            AddressN                9999
avatar
Yogendra Dubey
9 августа 2021 в 04:24
-1

Если у вас есть текст JSON, который хранится в таблице базы данных, вы можете читать или изменять значения JSON с помощью встроенных функций, таких как

  • ISJSON (Transact-SQL) проверяет, содержит ли строка действительный JSON.
  • JSON_VALUE (Transact-SQL) извлекает скалярное значение из строки JSON.
  • JSON_QUERY (Transact-SQL) извлекает объект или массив из JSON строка.
  • JSON_MODIFY (Transact-SQL) изменяет значение в строке JSON.

В приведенном ниже примере будут извлечены Empname и Department из JSonCol (столбец со значением JSON)

SELECT JSON_VALUE(jsonCol, '$.Employee.EmpName') AS Empname,
JSON_VALUE(jsonCol, '$.Employee.Deapartment') As Department
FROM Table_Name
Where ISJSON(JsonCol)>0;