C# Npgsql向PostgreSQL的json[]列插入JSON对象数组问题

现有表结构 #

可正常执行的建表语句：

CREATE TABLE Recipes (
    key SERIAL PRIMARY KEY,
    name varchar(255) NOT NULL,    
    ingredients json[],
    duration int
);

SQL客户端可直接运行的正常插入示例：

INSERT INTO recipes (name, duration, ingredients)
VALUES(
    'title',
    60,
    array['{"name": "in1", "amount": 125, "unit": "g" }',
          '{ "name": "in2", "amount": 75, "unit": "ml" }'
    ]::json[]
);

现有问题代码 #

C# 插入逻辑 #

//创建连接
var connString = $"Host={host};Port={port};Username={user};Password={password};Database={database}";
Npgsql.NpgsqlConnection.GlobalTypeMapper.UseJsonNet();

await using var conn = new NpgsqlConnection(connString);
await conn.OpenAsync();
//创建查询命令
await using var cmd = new NpgsqlCommand("INSERT INTO recipes (name,duration,ingredients) VALUES (@p0,@p1,@p2)", conn)
{
    Parameters =
    {                                                       
        new NpgsqlParameter("p0", recipe.Name),                            
        new NpgsqlParameter("p1", recipe.Duration),                            
        new NpgsqlParameter("p2", recipe.Ingredients)
    }
};
//执行插入
await cmd.ExecuteNonQueryAsync();

实体类定义 #

public class Recipe
{
    public Recipe() { }
    public string Name { get; set; }
    public int Duration { get; set; }

    //尝试过添加TypeName配置，无效
    //[Column(TypeName = "jsonb")]
    public Ingredients[] Ingredients { get; set; }
}

public class Ingredients
{
    public string Name { get; set; }
    public float Amount { get; set; }
    public string Unit { get; set; }
}

已尝试的无效方案 #

• 传入JArray类型的食材集合，插入失败
• 传入JObject[]类型的食材数组，插入失败
• 传入序列化后的JSON字符串数组，抛出类型不匹配异常，提示传入类型为text[]，和目标列的json[]类型不兼容
• 移除ingredients列后插入逻辑可正常执行，确认问题出在json[]类型的参数映射环节

问题核心是Npgsql不会自动推断数组元素的JSON类型，直接传POCO数组、JObject数组或者字符串数组都会被默认映射为text[]或其他不兼容类型，按以下方式修改即可：

1. 显式指定参数的Npgsql类型为数组+JSON组合
   不要让Npgsql自动推断参数类型，给ingredients对应的参数显式声明类型，同时把每个食材对象序列化为JSON字符串后传入：

// 先把每个食材对象序列化为JSON字符串
var ingredientsStrList = recipe.Ingredients
    .Select(ing => JsonConvert.SerializeObject(ing))
    .ToList();

await using var cmd = new NpgsqlCommand("INSERT INTO recipes (name,duration,ingredients) VALUES (@p0,@p1,@p2)", conn)
{
    Parameters =
    {                                                       
        new NpgsqlParameter("p0", recipe.Name),                            
        new NpgsqlParameter("p1", recipe.Duration),
        // 显式声明参数类型为json数组
        new NpgsqlParameter("p2", NpgsqlDbType.Array | NpgsqlDbType.Json)
        {
            Value = ingredientsStrList
        }
    }
};

2. 检查依赖版本匹配
   如果使用Npgsql 6.x及以上版本，UseJsonNet()配置需要额外安装Npgsql.Json.NETNuGet包，在连接打开前完成全局映射配置即可，原有全局映射逻辑在版本匹配的前提下可正常生效。
3. 更推荐的生产实践
   实际开发中很少使用json[]类型，建议把字段类型修改为jsonb直接存储整个JSON数组：

ALTER TABLE Recipes ALTER COLUMN ingredients TYPE jsonb USING ingredients::jsonb;

jsonb类型查询性能更高、支持全套JSON操作符，传参时只需要直接传入序列化后的数组字符串或者JArray对象，指定NpgsqlDbType.Jsonb即可，不需要处理数组嵌套类型映射，代码维护成本更低。

之前传字符串数组报错的原因：Npgsql默认会把C#的string[]映射为PostgreSQL的text[]，就算字符串内容是合法JSON，驱动也不会自动做类型转换，必须显式指定参数对应的数据库类型，驱动才会执行正确的类型转换逻辑。

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