SQL Server关联对象JSON格式异常：C#反序列化失败求助

问题场景 #

你在SQL Server 2019中编写了嵌套FOR JSON的查询，期望返回单个关联的Member对象，但实际输出中Member字段是转义后的JSON字符串，导致ASP.NET Core 8.0反序列化时抛出异常。

原查询语句：

SELECT 
    a.Id, a.Title,
    (SELECT m.Id, m.Name, m.MobileNo
     FROM [msm].[MsmMember] AS m
     WHERE m.Id = a.MemberId 
       AND m.OrganizationId = @OrganizationId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Member
FROM 
    msm.MsmArticleSubmission a 
WHERE
    a.OrganizationId = @OrganizationId
    AND a.Id = @Id
FOR JSON PATH

错误输出：

[
  {
    "Id": "A4C6B579-5D0C-4807-9537-53C6B1E8E6BA",
    "Title": "Test Data",
    "Member": "{\"Id\":\"79F87A71-332A-43CB-A944-E8C50C00CFF7\",\"Name\":\"Asma Husain Noora\",\"MobileNo\":\"00000000\"}"
  }
]

解决方案 #

方案1：使用JSON_QUERY包裹子查询结果 #

SQL Server默认会将字符串类型的JSON内容转义，JSON_QUERY可以明确标记该内容是已格式化的JSON，外层FOR JSON处理时不会再对其转义。

修改后的查询：

SELECT 
    a.Id, a.Title,
    JSON_QUERY(
        (SELECT m.Id, m.Name, m.MobileNo
         FROM [msm].[MsmMember] AS m
         WHERE m.Id = a.MemberId 
           AND m.OrganizationId = @OrganizationId
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    ) AS Member
FROM 
    msm.MsmArticleSubmission a 
WHERE
    a.OrganizationId = @OrganizationId
    AND a.Id = @Id
FOR JSON PATH

方案2：通过JOIN和字段别名构建嵌套对象 #

使用JOIN关联表，通过带层级的字段别名（如[Member.Id]）让外层FOR JSON自动生成嵌套的JSON对象，无需嵌套FOR JSON子查询。

修改后的查询：

SELECT 
    a.Id, a.Title,
    m.Id AS [Member.Id],
    m.Name AS [Member.Name],
    m.MobileNo AS [Member.MobileNo]
FROM 
    msm.MsmArticleSubmission a
LEFT JOIN [msm].[MsmMember] AS m
    ON m.Id = a.MemberId 
    AND m.OrganizationId = @OrganizationId
WHERE
    a.OrganizationId = @OrganizationId
    AND a.Id = @Id
FOR JSON PATH

正确输出示例 #

两种方案都会返回如下格式的JSON，Member字段为原生JSON对象，可直接被ASP.NET Core反序列化：

[
  {
    "Id": "A4C6B579-5D0C-4807-9537-53C6B1E8E6BA",
    "Title": "Test Data",
    "Member": {
      "Id": "79F87A71-332A-43CB-A944-E8C50C00CFF7",
      "Name": "Asma Husain Noora",
      "MobileNo": "00000000"
    }
  }
]

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