如何将.NET对象数组转换为SQL Server标准列结构表

需求说明 #

任务是将.NET中的对象数组导入SQL Server的标准列结构表，对象结构示例：

{
   "id": "1",
   "firstName": "Test",
   "lastName": "User",
   "dob": "20-10-2000"
}

目标表结构：

已尝试步骤 #

1. 通过JsonConvert.SerializeObject将对象序列化为字符串，传入存储过程。
2. 修改字符串分隔符：移除数组开头的[{"和结尾的"}]，将对象间的},{替换为|，例如[{person1},{person2}]转为person1|person2。
3. 使用STRING_SPLIT按|分割字符串，使每个对象单独成行。

现有代码：

DECLARE @json NVARCHAR(MAX) = '[{"ID":"1234567890123","FirstName":"John","LastName":"Tester","SecondName":"AVACUM","BirthDate":"1950-04-11T00:00:00","CPAS":"12345678901","PensionType":1,"SurvivorshipType":0,"StartDate":"2010-04-11T00:00:00"}]'

DECLARE @replacedSeparator NVARCHAR(max)
SET @replacedSeparator = REPLACE(@json, '},{', '|');

-- replace beginning of stringified array
SET @replacedSeparator = REPLACE(@replacedSeparator, '[{', '');

-- replace end of stringified array
SET @replacedSeparator = REPLACE(@replacedSeparator, '}]', '');

-- primary split of stringified array to get all persons
DECLARE @splitString TABLE (value NVARCHAR(MAX));
INSERT INTO @splitString (value)
SELECT value FROM STRING_SPLIT(@replacedSeparator, '|');

SELECT value FROM @splitString

推荐解决思路 #

放弃手动拆分字符串的方案，直接利用SQL Server原生的OPENJSON函数解析整个JSON数组，这是更可靠、高效的方式，避免手动处理字符串带来的格式错误风险（比如字段值包含特殊分隔符时会出错）。

直接解析并映射到列 #

使用OPENJSON配合WITH子句，可以直接将JSON数组中的每个对象解析为表的一行数据，同时指定列类型和映射关系：

DECLARE @json NVARCHAR(MAX) = '[{"ID":"1234567890123","FirstName":"John","LastName":"Tester","SecondName":"AVACUM","BirthDate":"1950-04-11T00:00:00","CPAS":"12345678901","PensionType":1,"SurvivorshipType":0,"StartDate":"2010-04-11T00:00:00"}]'

-- 解析JSON并映射到目标列，可直接插入到表中
INSERT INTO YourTargetTable (Id, FirstName, LastName, SecondName, BirthDate, CPAS, PensionType, SurvivorshipType, StartDate)
SELECT *
FROM OPENJSON(@json)
WITH (
    Id NVARCHAR(50) '$.ID',
    FirstName NVARCHAR(100) '$.FirstName',
    LastName NVARCHAR(100) '$.LastName',
    SecondName NVARCHAR(100) '$.SecondName',
    BirthDate DATE '$.BirthDate', -- 自动转换为DATE类型
    CPAS NVARCHAR(50) '$.CPAS',
    PensionType INT '$.PensionType', -- 自动转换为INT类型
    SurvivorshipType INT '$.SurvivorshipType',
    StartDate DATE '$.StartDate'
)

简化.NET端操作 #

不需要对序列化后的JSON字符串做任何修改，直接将JsonConvert.SerializeObject生成的完整JSON数组传入存储过程即可，省去手动替换分隔符的步骤。

关于拆分属性到单行的思路 #

你之前考虑的将每个对象拆分为"property":"value"格式的行完全没必要，OPENJSON已经可以直接完成对象到行的映射，额外拆分只会增加不必要的复杂度。

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