在C#中从SQL Server获取并格式化嵌套JSON数据的问题

问题诊断 #

你的SQL语句存在两个核心问题：

1. 匹配逻辑完全错误：需求是基于sub.S_tag是否存在于main.Tags的标签列表中，但原语句用CHARINDEX(sub.Tags, main.Tags)检查的是sub的整个标签串是否是main标签串的子串，完全背离需求。
2. 未过滤目标记录：原语句会返回所有学生记录，而你需要的是仅针对ID=1的结果。
3. 存在部分匹配风险：直接用CHARINDEX匹配标签会出现误判（比如tag1会被匹配到tag10）。

修正后的SQL语句 #

方案1：SQL Server 2016+（使用STRING_SPLIT处理标签） #

利用STRING_SPLIT将主记录的标签拆分为单个条目，精准匹配子记录的S_tag：

SELECT
    main.Name AS Name,
    main.Age AS Age,
    main.City AS City,
    (SELECT
         sub.Name AS Name,
         sub.Age AS Age,
         sub.City AS City
     FROM
         tbl_student AS sub
     WHERE
         EXISTS (
             SELECT 1 
             FROM STRING_SPLIT(main.Tags, ',') AS main_tag
             WHERE main_tag.value = sub.S_tag
         )
     FOR JSON PATH) AS Tags
FROM
    tbl_student AS main
WHERE main.ID = 1 -- 仅获取ID=1的记录
FOR JSON PATH

方案2：兼容低版本SQL Server #

如果你的SQL Server版本低于2016，无法使用STRING_SPLIT，可以通过给标签前后加逗号避免部分匹配：

SELECT
    main.Name AS Name,
    main.Age AS Age,
    main.City AS City,
    (SELECT
         sub.Name AS Name,
         sub.Age AS Age,
         sub.City AS City
     FROM
         tbl_student AS sub
     WHERE
         CHARINDEX(',' + sub.S_tag + ',', ',' + main.Tags + ',') > 0
     FOR JSON PATH) AS Tags
FROM
    tbl_student AS main
WHERE main.ID = 1 -- 仅获取ID=1的记录
FOR JSON PATH

验证结果 #

执行修正后的SQL，会生成你预期的嵌套JSON：

[
  {
    "Name": "John Doe",
    "Age": 30,
    "City": "New York",
    "Tags": [
      {
        "Name": "Bob Johnson",
        "Age": 40,
        "City": "Chicago"
      },
      {
        "Name": "Alice Brown",
        "Age": 28,
        "City": "San Francisco"
      },
      {
        "Name": "Jane Smith",
        "Age": 25,
        "City": "Los Angeles"
      }
    ]
  }
]

（若需要固定Tags数组顺序，可在子查询中添加ORDER BY语句）

C#调用示例 #

在C#中执行上述SQL并获取结果：

using System.Data.SqlClient;

var connectionString = "你的SQL Server连接字符串";
var sql = @"-- 此处填入修正后的SQL语句";

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (var cmd = new SqlCommand(sql, conn))
    {
        var jsonResult = cmd.ExecuteScalar()?.ToString();
        // 可直接使用jsonResult，或反序列化为实体类
        Console.WriteLine(jsonResult);
    }
}

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