如何在ASP.NET Web API中从数据库获取嵌套JSON格式数据？

我的SQL Server数据库里，last_name字段存的是JSON格式的字符串，用ASP.NET Web API查询返回数据时，这个字段是带引号的字符串形式，不是我想要的嵌套JSON对象。

原API代码如下：

[HttpGet]
public IHttpActionResult GetUsers()
{ 
    SqlDataAdapter da = new SqlDataAdapter("select * from users", con); 
    DataTable dt = new DataTable();
    da.Fill(dt);
    if (dt.Rows.Count > 0)
    {
        return Ok(dt);
    }
    else
    {
        return Ok("No Data Found ...");
    }
}

当前返回的JSON格式：

[
  {
   "user_id": 5,
   "frist_name": "John",
   "last_name": "{\"middle_name\":\"Ray\",\"lst_name\":\"Smith\"}",
   "user_name": "john@gmail.com"
  }
]

期望的JSON格式：

[
  {
   "user_id": 5,
   "frist_name": "John",
   "last_name": {
                 "middle_name": "Ray",
                 "lst_name": "Smith"
                },
   "user_name": "john@gmail.com"
  }
]

下面提供几种可行的实现方法：

方法一：定义实体类+JSON反序列化（推荐） #

这种方式更符合Web API的开发规范，便于后续维护和扩展。

1. 先定义对应的数据实体类：

// 对应last_name字段的嵌套结构
public class LastNameInfo
{
    public string middle_name { get; set; }
    public string lst_name { get; set; }
}

// 用户实体类
public class User
{
    public int user_id { get; set; }
    public string frist_name { get; set; }
    public LastNameInfo last_name { get; set; }
    public string user_name { get; set; }
}

2. 修改API方法，手动映射数据并反序列化last_name字段：

using Newtonsoft.Json; // 需要通过NuGet安装Newtonsoft.Json包

[HttpGet]
public IHttpActionResult GetUsers()
{
    List<User> usersList = new List<User>();
    using (SqlDataAdapter da = new SqlDataAdapter("select * from users", con))
    {
        DataTable dt = new DataTable();
        da.Fill(dt);
        
        foreach (DataRow row in dt.Rows)
        {
            User user = new User
            {
                user_id = Convert.ToInt32(row["user_id"]),
                frist_name = row["frist_name"].ToString(),
                user_name = row["user_name"].ToString()
            };
            
            // 反序列化last_name字段的JSON字符串
            string lastNameJson = row["last_name"].ToString();
            if (!string.IsNullOrEmpty(lastNameJson))
            {
                user.last_name = JsonConvert.DeserializeObject<LastNameInfo>(lastNameJson);
            }
            
            usersList.Add(user);
        }
    }
    
    return usersList.Count > 0 ? Ok(usersList) : Ok("No Data Found ...");
}

方法二：直接修改DataTable中的值（无需定义实体类） #

如果不想创建实体类，可以直接把last_name字段的字符串转成JObject，这样序列化时会自动变成嵌套对象：

using Newtonsoft.Json.Linq;

[HttpGet]
public IHttpActionResult GetUsers()
{
    SqlDataAdapter da = new SqlDataAdapter("select * from users", con);
    DataTable dt = new DataTable();
    da.Fill(dt);
    
    foreach (DataRow row in dt.Rows)
    {
        string lastNameJson = row["last_name"].ToString();
        if (!string.IsNullOrEmpty(lastNameJson))
        {
            // 将JSON字符串转为JObject
            row["last_name"] = JObject.Parse(lastNameJson);
        }
    }
    
    return dt.Rows.Count > 0 ? Ok(dt) : Ok("No Data Found ...");
}

方法三：从数据库层面直接返回结构化JSON（SQL Server 2016+） #

如果你的SQL Server版本是2016及以上，可以直接在查询语句中处理JSON，返回已经结构化的结果：

SELECT 
    user_id,
    frist_name,
    JSON_QUERY(last_name) AS last_name, -- 用JSON_QUERY避免JSON字符串被转义
    user_name
FROM users
FOR JSON PATH

然后修改API代码，直接读取并返回这个JSON结果：

[HttpGet]
public IHttpActionResult GetUsers()
{
    string jsonResult = string.Empty;
    using (SqlCommand cmd = new SqlCommand(@"SELECT 
                                                user_id,
                                                frist_name,
                                                JSON_QUERY(last_name) AS last_name,
                                                user_name
                                            FROM users
                                            FOR JSON PATH", con))
    {
        con.Open();
        jsonResult = cmd.ExecuteScalar()?.ToString();
        con.Close();
    }
    
    return !string.IsNullOrEmpty(jsonResult) ? Content(System.Net.HttpStatusCode.OK, jsonResult, "application/json") : Ok("No Data Found ...");
}

注意事项 #

• 使用前两种方法时，需要确保last_name字段存储的是合法的JSON格式，否则反序列化会报错，建议添加try-catch块处理异常
• 方法一中的Newtonsoft.Json包需要通过NuGet安装
• 实体类的字段名要和数据库字段名保持一致，或者可以用[JsonProperty]特性指定映射关系

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