在SQL Server中将嵌套JSON数组插入多张关联表

Got it, let's break down how to insert your nested JSON into tables with foreign key relationships. First, I'll assume a logical table structure that matches your JSON hierarchy—feel free to adjust this to match your actual schema:

-- Main table for FirstElement
CREATE TABLE FirstElements (
    FirstElementID INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Location NVARCHAR(100),
    Region NVARCHAR(100)
);

-- Child table for InnerElement (foreign key to FirstElements)
CREATE TABLE InnerElements (
    InnerElementID INT IDENTITY(1,1) PRIMARY KEY,
    FirstElementID INT NOT NULL FOREIGN KEY REFERENCES FirstElements(FirstElementID),
    Name NVARCHAR(100) NOT NULL,
    Description NVARCHAR(MAX),
    Type NVARCHAR(50)
);

-- Grandchild table for InnerMostElement (foreign key to InnerElements)
CREATE TABLE InnerMostElements (
    InnerMostElementID INT IDENTITY(1,1) PRIMARY KEY,
    InnerElementID INT NOT NULL FOREIGN KEY REFERENCES InnerElements(InnerElementID),
    [Key] NVARCHAR(100) NOT NULL,
    [Value] NVARCHAR(MAX) NOT NULL
);

Now, here's the code to parse your JSON and insert into these tables while preserving the foreign key links. We'll use OPENJSON to unpack the nested structure and OUTPUT clauses to capture generated IDs for the child tables:

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":"AnotherProperty", "Value":"Value2" } 
                ] 
            } 
        ] 
    } 
}';

-- Table variable to hold the inserted FirstElementID
DECLARE @InsertedFirstElements TABLE (FirstElementID INT);

-- Step 1: Insert into FirstElements and capture the generated ID
INSERT INTO FirstElements (Name, Location, Region)
OUTPUT inserted.FirstElementID INTO @InsertedFirstElements
SELECT 
    JSON_VALUE(@JsonObject, '$.FirstElement.Name') AS Name,
    JSON_VALUE(@JsonObject, '$.FirstElement.Location') AS Location,
    JSON_VALUE(@JsonObject, '$.FirstElement.Region') AS Region;

-- Table variable to hold inserted InnerElementIDs and their parent FirstElementID
DECLARE @InsertedInnerElements TABLE (InnerElementID INT, FirstElementID INT);

-- Step 2: Insert into InnerElements, link to FirstElements, and capture IDs
INSERT INTO InnerElements (FirstElementID, Name, Description, Type)
OUTPUT inserted.InnerElementID, inserted.FirstElementID INTO @InsertedInnerElements
SELECT 
    fe.FirstElementID,
    ie.Name,
    ie.Description,
    ie.Type
FROM @InsertedFirstElements fe
CROSS APPLY OPENJSON(@JsonObject, '$.FirstElement.InnerElement')
WITH (
    Name NVARCHAR(100) '$.Name',
    Description NVARCHAR(MAX) '$.Description',
    Type NVARCHAR(50) '$.Type',
    InnerMostElement NVARCHAR(MAX) '$.InnerMostElement' AS JSON
) ie;

-- Step 3: Insert into InnerMostElements, link to InnerElements
INSERT INTO InnerMostElements (InnerElementID, [Key], [Value])
SELECT 
    ie.InnerElementID,
    ime.[Key],
    ime.[Value]
FROM @InsertedInnerElements ie
CROSS APPLY OPENJSON(@JsonObject, '$.FirstElement.InnerElement')
WITH (
    Name NVARCHAR(100) '$.Name',
    InnerMostElement NVARCHAR(MAX) '$.InnerMostElement' AS JSON
) json_ie
CROSS APPLY OPENJSON(json_ie.InnerMostElement)
WITH (
    [Key] NVARCHAR(100) '$.Key',
    [Value] NVARCHAR(MAX) '$.Value'
) ime
-- Match the InnerElement by Name (adjust if you have a unique identifier other than Name)
WHERE json_ie.Name = (SELECT Name FROM InnerElements WHERE InnerElementID = ie.InnerElementID);

Key Notes: #

• OUTPUT Clauses: These are critical because they let us capture the auto-generated identity IDs from the parent tables, which we need for the foreign keys in child tables.
• CROSS APPLY OPENJSON: This is how we unpack nested JSON arrays. Each CROSS APPLY drills down one level in the JSON hierarchy.
• Matching Child Records: In Step 3, we match InnerElement records by their Name—if your InnerElement has a unique identifier (like a natural key), use that instead for more reliable matching.
• Error Handling: You might want to wrap this in a transaction to ensure all inserts succeed or fail together, especially since foreign keys enforce referential integrity.

内容的提问来源于stack exchange，提问作者Naman Goyal