将嵌套的json数组插入sql server中的多个表中

我在 Sql 服务器中有以下 Json 对象。我想将此数据插入多个表及其关系（即外键）：

DECLARE @JsonObject NVARCHAR(MAX) = N'{  
   "FirstElement":{  
      "Name":"ABC",
      "Location":"East US",
      "Region":"West US",
      "InnerElement":[
         {  
            "Name":"IE1",
            "Description":"IE1 Description",
            "Type":"Small",
            "InnerMostElement":[  
               {
                  "Key":"Name",
                  "Value":"IME1"
               },
                {
                  "Key":"AnotherProperty",
                  "Value":"Value1"
               }
            ]
         },
         {  
            "Name":"IE2",
            "Description":"IE2 Description",
            "Type":"Medium",
            "InnerMostElement":[ 
               {
                  "Key":"Name",
                  "Value":"IME2"
               },
                {
                  "Key":"Address",
                  "Value":"Xyz"
               }, 
               {
                  "Key":"Type",
                  "Value":"Simple"
               },
                {
                  "Key":"LastProperty",
                  "Value":"ValueX"
               }
            ]
         }
      ]
   }
}'

表结构附在这里：

我想插入第一元素数据输入Table1, 内部元素数据输入Table2 and 最里面的元素数据输入Table3.

最简单的部分是第一个表，因为我们只插入一行并且它没有依赖项：

BEGIN TRANSACTION;

INSERT Table1([Name], [Location], [Region])
SELECT [Name], [Location], [Region]
FROM OPENJSON(@JsonObject, '$.FirstElement')
WITH (
    [Name] VARCHAR(100),
    [Location] VARCHAR(100),
    [Region] VARCHAR(100)
);

DECLARE @Table1Id INT = SCOPE_IDENTITY();

困难的部分是下一张桌子。我们需要捕获已插入行的所有标识，以及尚未插入到表 3 中的所有数据。因为OUTPUT的条款INSERT仅限于输出基表中的值，我们需要使用MERGE诡计：

DECLARE @Table3Input TABLE([Table2Id] INT, [InnerMostElement] NVARCHAR(MAX));

MERGE Table2
USING (
    SELECT [Name], [Description], [Type], [InnerMostElement]
    FROM OPENJSON(@JsonObject, '$.FirstElement.InnerElement')
    WITH (
        [Name] VARCHAR(100),
        [Description] VARCHAR(100),
        [Type] VARCHAR(100),
        [InnerMostElement] NVARCHAR(MAX) AS JSON
    )
) AS J
ON 1 = 0    -- Always INSERT
WHEN NOT MATCHED THEN 
    INSERT([Table1Id], [Name], [Description], [Type])
    VALUES (@Table1Id, J.[Name], J.[Description], J.[Type])
    OUTPUT inserted.Id, J.[InnerMostElement]
    INTO @Table3Input([Table2Id], [InnerMostElement]);

如果主要使用 JSON 来填充表格，那么使用可能会更方便SEQUENCE对象生成连续值（使用sp_sequence_get_range），无需将整个 JSON 捕获到临时表中。这将大大简化这一过程并消除对MERGE.

最后一张表又很简单：

INSERT Table3([Table2Id], [Key], [Value])
SELECT [Table2Id], KV.[Key], KV.[Value]
FROM @Table3Input CROSS APPLY (
    SELECT [Key], [Value]
    FROM OPENJSON([InnerMostElement])
    WITH (
        [Key] VARCHAR(100),
        [Value] VARCHAR(100)
    )
) AS KV;

COMMIT;

该事务在逻辑上是必要的，以确保该对象被完全插入或根本不插入。

最终输出：

+----+------+----------+---------+
| Id | Name | Location | Region  |
+----+------+----------+---------+
|  1 | ABC  | East US  | West US |
+----+------+----------+---------+

+----+----------+------+-----------------+--------+
| Id | Table1Id | Name |   Description   |  Type  |
+----+----------+------+-----------------+--------+
|  1 |        1 | IE1  | IE1 Description | Small  |
|  2 |        1 | IE2  | IE2 Description | Medium |
+----+----------+------+-----------------+--------+

+----+----------+-----------------+--------+
| Id | Table2Id |       Key       | Value  |
+----+----------+-----------------+--------+
|  1 |        1 | Name            | IME1   |
|  2 |        1 | AnotherProperty | Value1 |
|  3 |        2 | Name            | IME2   |
|  4 |        2 | Address         | Xyz    |
|  5 |        2 | Type            | Simple |
|  6 |        2 | LastProperty    | ValueX |
+----+----------+-----------------+--------+

为了完整起见，将其转换回 JSON 的方法如下：

SELECT 
  [Name] AS 'FirstElement.Name', 
  [Location] AS 'FirstElement.Location', 
  [Region] AS 'FirstElement.Region',
  (
    SELECT 
      [Name], 
      [Description], 
      [Type],
      (
        SELECT 
          [Key], 
          [Value]
        FROM Table3
        WHERE Table3.Table2Id = Table2.Id
        FOR JSON PATH
      ) AS 'InnerMostElement'
    FROM Table2
    WHERE Table2.Table1Id = Table1.Id
    FOR JSON PATH
  ) AS 'FirstElement.InnerElement'
FROM Table1
FOR JSON PATH;