将嵌套的json数组插入sql server中的多个表中 [英] Insert nested json array into multiple tables in sql server

问题描述

我在 Sql 服务器中有以下 Json 对象.我想将此数据插入到具有它们的关系(即外键)的多个表中:

I have following Json Object in Sql server. I want to insert this data into multiple tables with their relation (i.e. foreign key):

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 中插入FirstElement 数据，在Table2 和InnerMostElement 中插入InnerElement 数据Table3 中的数据.

I want to insert the FirstElement data in Table1, InnerElement data in Table2 and InnerMostElement data in Table3.

推荐答案

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

The easy part is the first table, because we're only inserting one row and it has no dependencies:

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 的数据.因为 INSERT 的 OUTPUT 子句仅限于输出值仅在基表中，我们需要使用 MERGE 技巧:

The hard part is the next table. We need to capture all the identities of the inserted rows, but also all the data yet to be inserted into table 3. Because the OUTPUT clause of INSERT is restricted to outputting values in the base table only, we need to use MERGE trickery:

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 的需求.

If the tables are to be primarily filled using JSON, it may be more convenient to use SEQUENCE objects to generate consecutive values (using sp_sequence_get_range) without the need to capture the whole JSON into a temporary table. That would greatly simplify this and remove the need for 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;

事务在逻辑上是确保该对象完全插入或根本不插入的必要条件.

The transaction is logically necessary to ensure this object is entirely inserted, or not at all.

最终输出:

+----+------+----------+---------+
| 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:

For completeness, here's how you'd turn that back into 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;

这篇关于将嵌套的json数组插入sql server中的多个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！