.NET Core 5中如何从PostgreSQL(Npgsql)表获取JSON数组值

问题背景 #

使用.NET Core 5搭配PostgreSQL，尝试将表中json array类型字段转换为List<MdlSettingsValueOptions>集合时失败，相关信息如下：

表结构 #

CREATE TABLE settings.tbl_settings (
        pk_settings_id BIGSERIAL PRIMARY KEY,
        vhr_settings_sys_name VARCHAR(250) NOT NULL,
        vhr_label VARCHAR(250) NOT NULL,
        vhr_value VARCHAR(500) NOT NULL,
        arj_select_items JSON ARRAY,
        txt_remarks TEXT DEFAULT ''
);

尝试的代码 #

foreach (DataRow objRecord in objDataSet.Tables[0].Rows)
{
     // 尝试1
     mdlSettings.lstSelectItems = JObject.Parse(objRecord["arj_select_items"].ToString());

     // 尝试2
     mdlSettings.lstSelectItems = (DBNull.Value != objRecord["arj_select_items"]) ? JsonSerializer.Deserialize<List<MdlSettingsValueOptions>>(objRecord["arj_select_items"].ToString()) : mdlSettings.lstSelectItems;

     // 尝试3
     mdlSettings.lstSelectItems = (DBNull.Value != objRecord["arj_select_items"]) ? JsonConvert.DeserializeObject<List<MdlSettingsValueOptions>>(objRecord["arj_select_items"].ToString()) : mdlSettings.lstSelectItems;
}

报错信息 #

Unexpected character encountered while parsing value: S. Path '', line 0, position 0.

额外测试 #

执行var a = objRecord["arj_select_items"].ToString();得到结果：

System.String[]

问题原因 #

PostgreSQL的json array字段通过常规DataAdapter读取时，会被映射为.NET的string[]数组类型，而非JSON格式字符串。直接调用ToString()会返回类型名System.String[]，这不是合法的JSON格式，因此JSON解析器会报错。

解决方法 #

方法1：手动转换数组为JSON字符串后反序列化 #

针对已有的DataRow读取方式，先将string[]转为合法JSON数组字符串，再反序列化为List集合：

foreach (DataRow objRecord in objDataSet.Tables[0].Rows)
{
    if (objRecord["arj_select_items"] is not DBNull && objRecord["arj_select_items"] is string[] jsonArray)
    {
        // 使用System.Text.Json序列化数组为JSON字符串
        var jsonString = JsonSerializer.Serialize(jsonArray);
        mdlSettings.lstSelectItems = JsonSerializer.Deserialize<List<MdlSettingsValueOptions>>(jsonString);
    }
    else
    {
        // 字段为空时初始化空集合或保留原有值
        mdlSettings.lstSelectItems = mdlSettings.lstSelectItems ?? new List<MdlSettingsValueOptions>();
    }
}

如果使用Newtonsoft.Json：

foreach (DataRow objRecord in objDataSet.Tables[0].Rows)
{
    if (objRecord["arj_select_items"] is not DBNull && objRecord["arj_select_items"] is string[] jsonArray)
    {
        var jsonString = JsonConvert.SerializeObject(jsonArray);
        mdlSettings.lstSelectItems = JsonConvert.DeserializeObject<List<MdlSettingsValueOptions>>(jsonString);
    }
    else
    {
        mdlSettings.lstSelectItems = mdlSettings.lstSelectItems ?? new List<MdlSettingsValueOptions>();
    }
}

方法2：使用Npgsql驱动直接映射（推荐） #

如果使用Entity Framework Core搭配Npgsql官方驱动，可以直接配置字段映射，无需手动处理：

1. 安装依赖包：Npgsql.EntityFrameworkCore.PostgreSQL 和 Npgsql.EntityFrameworkCore.PostgreSQL.Json.NET
2. 在DbContext配置中启用JsonNet支持：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseNpgsql("你的数据库连接字符串", o => o.UseJsonNet());

3. 实体类中直接定义List属性：

public class MdlSettings
{
    // 其他属性...
    public List<MdlSettingsValueOptions> lstSelectItems { get; set; }
}

4. 在OnModelCreating中配置字段类型：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MdlSettings>()
        .Property(x => x.lstSelectItems)
        .HasColumnType("json[]");
}

这样查询时Npgsql会自动将json array字段映射为List<MdlSettingsValueOptions>集合，无需手动解析。

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