Блог

У меня есть сильно неструктурированная строка JSON, приходящая в ответ на вызов REST API:

 {
    "info": "Test Json Structure",
    "Owner": "Self-Owned",
    "managedObjects": [{
            "Name": "Device1",
            "Class": "A"
        }, {
            "Name": "Device2",
            "Class_145": "Ax01",
            "Class_11": "B",
            "Type_125478": {
                "Model": "1",
                "Manufacturer": "External"
            },
            "Type_SD": {
                "Model": "00",
                "Manufacturer": "Internal"
                }
        }, {
            "Name": "Device3",
            "Class_x": "Cx11",
            "Class_T": "C8Y",
            "Type": {
                "Model": "1x",
                "Manufacturer": "Internal"
            }
        }
    ]
}
  

Как я могу динамически анализировать этот объект с помощью T-SQL, чтобы все дочерние элементы представляли столбцы таблицы? Что еще более важно, как обращаться с Type , Type_125478 , Type_SD объектами, где на самом деле они имеют одинаковую структуру ( Model, Manufacturer ), но некоторые их имена отличаются. Также имейте в виду, что устройство может отправить новый идентификатор ( Type_XYZ ), которого раньше не существовало, но, к счастью, имеет ту же структуру ( Model, Manufacturer ) .

Вы можете использовать что-то подобное, чтобы преобразовать все в табличную структуру и продолжить (требуется версия SQL-Server v2016 ):

 DECLARE @YourJSON NVARCHAR(MAX)=
N'{
    "info": "Test Json Structure",
    "Owner": "Self-Owned",
    "managedObjects": [{
            "Name": "Device1",
            "Class": "A"
        }, {
            "Name": "Device2",
            "Class_145": "Ax01",
            "Class_11": "B",
            "Type_125478": {
                "Model": "1",
                "Manufacturer": "External"
            },
            "Type_SD": {
                "Model": "00",
                "Manufacturer": "Internal"
                }
        }, {
            "Name": "Device3",
            "Class_x": "Cx11",
            "Class_T": "C8Y",
            "Type": {
                "Model": "1x",
                "Manufacturer": "Internal"
            }
        }
    ]
}';
  

—запрос

 SELECT A.info
      ,A.[Owner]
      ,C.[key] AS TagName
      ,CASE WHEN D.Model IS NULL THEN C.[value] END AS TagValue 
      ,D.Model
      ,D.Manufacturer
FROM OPENJSON(@YourJSON)
WITH(info NVARCHAR(MAX)
    ,[Owner] NVARCHAR(MAX)
    ,managedObjects NVARCHAR(MAX) AS JSON) A
OUTER APPLY OPENJSON(A.managedObjects) B
OUTER APPLY OPENJSON(B.[value]) C
OUTER APPLY OPENJSON(CASE WHEN ISJSON(C.[value])=1 THEN C.[value] END) 
WITH (Model NVARCHAR(MAX)
     ,Manufacturer NVARCHAR(MAX))D;
  

—результат

  --------------------- ------------ ------------- ---------- ------- -------------- 
| info                | Owner      | TagName     | TagValue | Model | Manufacturer |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Name        | Device1  |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Class       | A        |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Name        | Device2  |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Class_145   | Ax01     |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Class_11    | B        |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Type_125478 |          | 1     | External     |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Type_SD     |          | 00    | Internal     |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Name        | Device3  |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Class_x     | Cx11     |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Class_T     | C8Y      |       |              |
 --------------------- ------------ ------------- ---------- ------- -------------- 
| Test Json Structure | Self-Owned | Type        |          | 1x    | Internal     |
 --------------------- ------------ ------------- ---------- ------- -------------- 
  

Подсказка: вы можете добавить B.[key] к результату в качестве идентификатора объекта.

Идея вкратце

• Мы используем первый OPENJSON , чтобы попасть в ваш JSON. Предложение WITH — позволяет обращаться к JSON-реквизитам как к столбцам. Мы сами возвращаем managedObejcts as JSON.
• Мы используем еще один OPENJSON , чтобы погрузиться в управляемые объекты.
• Это вернет массив объектов. Мы можем передать value в другой OPENJSON .
• Всякий value раз, когда может быть интерпретирован как JSON сам по себе, мы используем еще один OPENJSON , на этот раз с WITH предложением -снова, чтобы получить внутренние реквизиты в виде столбцов.

Вы можете вставить этот результат в таблицу (объявленную, временную, физическую …) и продолжить работу с этим простым для чтения набором.