SQL Server中解析嵌套JSON内的数组并实现存储的方法咨询

你原来的代码报错是因为 JSON_VALUE 仅支持提取JSON中的标量值（数字、字符串、布尔值等），无法直接提取数组、对象这类复合类型，所以 JSON_VALUE(@JsonIn, '$.Sections') 会返回NULL，导致插入失败。

最优表结构设计 #

按照关系型数据库范式，拆分主从表存储是最优方案，表结构参考如下：

-- 房间主表，存储房间核心属性
CREATE TABLE Rooms (
    RoomID INT PRIMARY KEY IDENTITY(1,1),
    InspectionID INT NOT NULL,
    RoomName VARCHAR(200) NOT NULL
)

-- 房间分段关联表，存储Sections数组中的每条数据，通过RoomID和主表关联
CREATE TABLE RoomSections (
    SectionID INT PRIMARY KEY IDENTITY(1,1),
    RoomID INT NOT NULL FOREIGN KEY REFERENCES Rooms(RoomID),
    SectionName VARCHAR(200) NOT NULL,
    Notes VARCHAR(1000) NULL
)

修正后的存储过程 #

使用SQL Server自带的OPENJSON函数可以直接将JSON数组拆解为多行数据，实现一次性批量插入关联表，不需要循环遍历：

CREATE OR ALTER PROCEDURE [API].[AddRoom] (@UserID int, @JsonIn NVARCHAR(MAX), @JsonOut NVARCHAR (MAX) OUTPUT)
AS
BEGIN
    SET NOCOUNT ON;
    -- 提取主表字段
    DECLARE @InspectionID Int = JSON_VALUE(@JsonIn, '$.InspectionID');
    DECLARE @RoomName varchar(200) = JSON_VALUE(@JsonIn, '$.RoomName');
    DECLARE @NewRoomID INT;

    -- 插入主表数据，获取自动生成的RoomID
    INSERT INTO Rooms (InspectionID, RoomName)
    VALUES (@InspectionID, @RoomName);
    SET @NewRoomID = SCOPE_IDENTITY();

    -- 拆解Sections数组，批量插入关联表
    INSERT INTO RoomSections (RoomID, SectionName, Notes)
    SELECT 
        @NewRoomID,
        JSON_VALUE(value, '$.Name'),
        JSON_VALUE(value, '$.Notes')
    FROM OPENJSON(@JsonIn, '$.Sections');

    -- 返回结果
    SET @JsonOut = (SELECT 1 [Status], 'Added new room' [Message] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER);
END

补充说明 #

如果你确实有保留原始Sections JSON的需求，可以在Rooms表中新增Sections NVARCHAR(MAX)类型的字段，插入时使用JSON_QUERY(@JsonIn, '$.Sections')即可获取完整的数组字符串存入该字段。但不推荐仅用该方案存储Sections数据，拆分关联表的方式后续查询、修改、统计分段数据的效率要高很多，也更符合数据库设计规范。

内容的提问来源于stack exchange，提问作者Joseph Lewis