使用OPENJSON解析嵌套JSON并保留关联关系导入SQL Server

针对大体积嵌套JSON拆分到主表+子表并保留关联的需求，核心思路是仅解析一次原始JSON，通过临时表存储中间结果，再基于中间结果完成主表和子表的批量插入，避免重复遍历大JSON导致的性能损耗。以下分两种常见场景给出具体实现：

场景1：主表主键使用JSON自带的_id #

如果JSON外层已经包含唯一标识_id，直接用该字段作为主表主键和子表外键：

步骤1：一次性解析JSON到临时表 #

将整个JSON的外层字段和嵌套数组片段一次性提取到临时表，仅遍历一次原始JSON：

-- 创建临时表存储解析后的全量数据
CREATE TABLE #TempMainWithChild (
    MainId NVARCHAR(50), -- 对应JSON的外层_id
    MainName NVARCHAR(100),
    OrdersJson NVARCHAR(MAX), -- 存储orders嵌套数组的JSON字符串
    AddressesJson NVARCHAR(MAX) -- 存储addresses嵌套数组的JSON字符串
)

-- 批量解析原始JSON，提取主表字段和子表数组片段
INSERT INTO #TempMainWithChild
SELECT
    JSON_VALUE(j.value, '$.id') AS MainId,
    JSON_VALUE(j.value, '$.name') AS MainName,
    JSON_QUERY(j.value, '$.orders') AS OrdersJson,
    JSON_QUERY(j.value, '$.addresses') AS AddressesJson
FROM OPENJSON(@YourLargeJson) j

步骤2：插入主表 #

基于临时表的主表字段批量插入：

INSERT INTO MainTbl (_id, name)
SELECT MainId, MainName FROM #TempMainWithChild

步骤3：插入子表 #

通过CROSS APPLY OPENJSON拆分临时表中存储的子表数组片段，同时关联主表的_id：

-- 插入Orders子表
INSERT INTO OrdersTbl (main_id, order_no, amount)
SELECT
    td.MainId,
    JSON_VALUE(o.value, '$.order_no') AS order_no,
    CAST(JSON_VALUE(o.value, '$.amount') AS DECIMAL(18,2)) AS amount
FROM #TempMainWithChild td
CROSS APPLY OPENJSON(td.OrdersJson) o
WHERE td.OrdersJson IS NOT NULL AND td.OrdersJson != '[]'

-- 插入Addresses子表
INSERT INTO AddressesTbl (main_id, city, street)
SELECT
    td.MainId,
    JSON_VALUE(a.value, '$.city') AS city,
    JSON_VALUE(a.value, '$.street') AS street
FROM #TempMainWithChild td
CROSS APPLY OPENJSON(td.AddressesJson) a
WHERE td.AddressesJson IS NOT NULL AND td.AddressesJson != '[]'

场景2：主表使用自增主键（IDENTITY） #

如果主表_id是SQL Server自增字段，需要通过OUTPUT子句捕获插入的自增ID，再关联到子表数据：

步骤1：解析JSON到临时表 #

先将JSON的非主键字段和子表数组片段存入带临时主键的临时表：

CREATE TABLE #TempRawData (
    TempRowId INT IDENTITY(1,1) PRIMARY KEY, -- 临时主键用于关联
    MainName NVARCHAR(100),
    OrdersJson NVARCHAR(MAX),
    AddressesJson NVARCHAR(MAX)
)

INSERT INTO #TempRawData (MainName, OrdersJson, AddressesJson)
SELECT
    JSON_VALUE(j.value, '$.name') AS MainName,
    JSON_QUERY(j.value, '$.orders') AS OrdersJson,
    JSON_QUERY(j.value, '$.addresses') AS AddressesJson
FROM OPENJSON(@YourLargeJson) j

步骤2：插入主表并捕获自增ID #

使用OUTPUT子句将插入的自增ID与临时表的TempRowId关联存储：

CREATE TABLE #MainIdMapping (
    TempRowId INT,
    MainAutoId INT -- 主表生成的自增_id
)

INSERT INTO MainTbl (name)
OUTPUT inserted._id, td.TempRowId INTO #MainIdMapping(MainAutoId, TempRowId)
SELECT MainName FROM #TempRawData td

步骤3：插入子表并关联自增ID #

通过映射表关联临时表和主表ID，拆分插入子表：

-- 插入Orders子表
INSERT INTO OrdersTbl (main_id, order_no, amount)
SELECT
    mm.MainAutoId,
    JSON_VALUE(o.value, '$.order_no') AS order_no,
    CAST(JSON_VALUE(o.value, '$.amount') AS DECIMAL(18,2)) AS amount
FROM #TempRawData td
JOIN #MainIdMapping mm ON td.TempRowId = mm.TempRowId
CROSS APPLY OPENJSON(td.OrdersJson) o
WHERE td.OrdersJson IS NOT NULL AND td.OrdersJson != '[]'

-- 插入Addresses子表
INSERT INTO AddressesTbl (main_id, city, street)
SELECT
    mm.MainAutoId,
    JSON_VALUE(a.value, '$.city') AS city,
    JSON_VALUE(a.value, '$.street') AS street
FROM #TempRawData td
JOIN #MainIdMapping mm ON td.TempRowId = mm.TempRowId
CROSS APPLY OPENJSON(td.AddressesJson) a
WHERE td.AddressesJson IS NOT NULL AND td.AddressesJson != '[]'

核心性能优化点 #

1. 单次JSON解析：所有操作基于一次解析后的临时表，避免多次调用OPENJSON遍历大体积JSON，这是性能提升的关键。
2. JSON_QUERY的使用：直接提取嵌套数组的JSON字符串，而非提前解析，减少解析开销。
3. 集合式操作：所有插入均为批量集合操作，避免逐行插入的性能损耗。
4. 临时表索引：若数据量极大，可给临时表的关联字段（如TempRowId、MainId）添加非聚集索引，加速关联查询。

内容的提问来源于stack exchange，提问作者Dmitriy Ryabin