C#对接PostgreSQL将查询结果转换为指定嵌套JSON结构问题

核心思路是将查询得到的平面行数据，转换为以id为键、其余字段组成的对象为值的字典结构，序列化后即可匹配你需要的嵌套格式。

修改后的完整代码 #

[HttpGet]
public JsonResult Get(int id)
{
    // 新增WHERE条件过滤指定id，使用参数化查询避免SQL注入
    string query =
        @"
        select id as ""id"", 
            title as ""title"",
            description as ""description"",
            image as ""image"",
            question as ""question""
        from card where id = @id";

    DataTable dt = new DataTable();
    string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
    NpgsqlDataReader myReader;
    using (NpgsqlConnection myCon = new NpgsqlConnection(sqlDataSource))
    {
        myCon.Open();
        using (NpgsqlCommand myCommand = new NpgsqlCommand(query, myCon))
        {
            // 给SQL添加参数
            myCommand.Parameters.AddWithValue("id", id);
            myReader = myCommand.ExecuteReader();
            dt.Load (myReader);
            myReader.Close();
            myCon.Close();
        }
    }

    // 构造目标结构的字典
    var result = new Dictionary<string, object>();
    if (dt.Rows.Count > 0)
    {
        DataRow row = dt.Rows[0];
        string cardId = row["id"].ToString();
        result[cardId] = new 
        {
            title = row["title"]?.ToString() ?? string.Empty,
            description = row["description"]?.ToString() ?? string.Empty,
            image = row["image"]?.ToString() ?? string.Empty,
            question = row["question"]?.ToString() ?? string.Empty
        };
    }

    return new JsonResult(result);
}

补充说明 #

• 如果你需要返回所有卡片的嵌套结构，去掉SQL的WHERE条件，遍历整个dt.Rows给字典添加键值对即可
• 也可以直接在PostgreSQL侧直接生成目标JSON，减少C#侧的处理逻辑，SQL写法如下：

select jsonb_object_agg(
    id, 
    jsonb_build_object(
        'title', title,
        'description', description,
        'image', image,
        'question', question
    )
) as card_json from card
-- 单条查询可以加 where id = @id

直接读取查询结果的card_json字段返回即可。

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