如何在C#(Dapper)或SQL中解析多语言JSON字段至目标格式

问题说明 #

数据库中存储的多语言名称字段是带转义的嵌套JSON字符串，通过Dapper查询返回的格式如下：

"firstName":"{
    \"ar-AE\": \"مستم\",
    \"en-US\": \"test\"
}"

需要转换为无转义的嵌套JSON格式：

"firstName":{"ar-AE": "مستم","en-US":"test"}

现有DTO定义：

class DTO
{
    public string FirstName { get; set; }
}

C# 代码层面解决方案 #

方案1：修改DTO类型并反序列化为字典 #

将DTO中的FirstName类型从string改为Dictionary<string, string>，手动处理查询结果完成反序列化：

1. 更新DTO定义：

class DTO
{
    public Dictionary<string, string> FirstName { get; set; }
}

2. 查询并转换数据：

// 使用System.Text.Json的写法
var list = connection.Query<DTO>("select FirstName from TableA")
    .Select(dto => new DTO
    {
        FirstName = JsonSerializer.Deserialize<Dictionary<string, string>>(dto.FirstName)
    })
    .ToList();

// 若用Newtonsoft.Json，替换为：
// FirstName = JsonConvert.DeserializeObject<Dictionary<string, string>>(dto.FirstName)

最终序列化整个列表时，FirstName会呈现为无转义的嵌套JSON对象。

方案2：保留原DTO，处理后重新序列化 #

如果不想修改DTO类型，可在获取数据后先解析字段，再重新序列化为目标格式：

var rawList = connection.Query<DTO>("select FirstName from TableA").ToList();
var processedData = rawList.Select(x => new 
{
    FirstName = JsonSerializer.Deserialize<Dictionary<string, string>>(x.FirstName)
}).ToList();
string finalJson = JsonSerializer.Serialize(processedData);

finalJson即为符合预期的无转义嵌套JSON。

SQL 层面解决方案 #

SQL Server 版本 #

使用JSON_QUERY函数直接解析存储的转义JSON字符串，查询时直接返回嵌套JSON对象：

SELECT JSON_QUERY(FirstName) AS FirstName FROM TableA

后续Dapper映射时，只需将DTO的FirstName改为Dictionary<string, string>，即可直接得到正确结构，无需额外代码处理。

MySQL 版本 #

使用JSON_UNQUOTE函数去除转义，直接返回纯净的JSON结构：

SELECT JSON_UNQUOTE(FirstName) AS FirstName FROM TableA

内容的提问来源于stack exchange，提问作者omi.iqb