如何在EF Core与PostgreSQL中实现JSONB大小写不敏感查询？

针对你遇到的JSONB字段中Name属性大小写不敏感查询的问题，结合PostgreSQL特性，提供以下几种高性能方案：

方案一：使用PostgreSQL JSONB路径查询（推荐，无需修改表结构） #

PostgreSQL的jsonb_path_exists函数支持路径表达式的大小写不敏感匹配，可通过EF Core自定义数据库函数调用：

1. 定义自定义数据库函数 #

public static class PostgresJsonExtensions
{
    [DbFunction("jsonb_path_exists", Schema = "pg_catalog")]
    public static bool JsonbPathExists(
        [DbParameter(DbType = "jsonb")] string jsonbColumn,
        [DbParameter(DbType = "text")] string pathExpression)
    {
        throw new NotImplementedException("仅用于EF Core查询翻译，不会在本地执行");
    }
}

2. 在DbContext中注册函数 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.HasDbFunction(typeof(PostgresJsonExtensions).GetMethod(nameof(PostgresJsonExtensions.JsonbPathExists))!);
}

3. 编写查询代码 #

利用like_regex和flag 'i'实现大小写不敏感匹配：

var chapterNames = new[] { "ch1", "Chapter2" };
// 生成路径表达式，匹配数组中任意元素的Name属性
var pathExpressions = chapterNames.Select(name => 
    $"$[*].Name like_regex '{Regex.Escape(name)}' flag 'i'");

var query = context.XYZ
    .Where(x => x.Containers != null && 
        pathExpressions.Any(path => PostgresJsonExtensions.JsonbPathExists(x.Containers, path)));

性能优化 #

创建jsonb_path_ops索引加速路径查询：

CREATE INDEX IF NOT EXISTS idx_xyz_containers_path ON XYZ USING gin (Containers jsonb_path_ops);

方案二：修正表达式索引并使用数组展开查询 #

你之前的索引无效是因为Containers->>'Name'仅提取数组第一个元素的Name，需改用数组展开提取所有元素的Name：

1. 创建正确的表达式索引 #

CREATE INDEX IF NOT EXISTS idx_xyz_containers_names_lower ON XYZ 
USING gin (
    (SELECT array_agg(lower((elem->>'Name')::text)) FROM jsonb_array_elements(Containers) elem)
    gin_trgm_ops
);

2. EF Core查询代码 #

通过原生SQL片段结合ILike实现匹配：

var lowerTargetNames = chapterNames.Select(n => n.ToLower()).ToList();

var query = context.XYZ
    .Where(x => x.Containers != null && 
        EF.Functions.SqlRaw<bool>(
            "EXISTS (SELECT 1 FROM jsonb_array_elements({0}) elem WHERE lower(elem->>'Name') = ANY(@names))",
            x.Containers,
            new NpgsqlParameter("@names", lowerTargetNames)
        ));

方案三：添加生成列（性能最优，需修改表结构） #

如果允许修改表结构，可添加预计算的生成列，存储所有Name的小写数组，避免每次查询解析JSON：

1. 创建生成列和索引 #

-- 添加生成列
ALTER TABLE XYZ ADD COLUMN Containers_Names_Lower text[] 
GENERATED ALWAYS AS (
    ARRAY(SELECT lower((elem->>'Name')::text) FROM jsonb_array_elements(Containers) elem)
) STORED;

-- 创建GIN索引
CREATE INDEX IF NOT EXISTS idx_xyz_containers_names_lower ON XYZ USING gin (Containers_Names_Lower);

2. EF Core映射与查询 #

在XYZ类中添加对应属性：

public class XYZ {
    public List<ABC>? Containers { get; set; }
    public string[]? Containers_Names_Lower { get; set; } // 映射生成列
}

查询代码极简且高性能：

var lowerTargetNames = chapterNames.Select(n => n.ToLower()).ToArray();
var query = context.XYZ
    .Where(x => x.Containers_Names_Lower != null && 
        x.Containers_Names_Lower.Intersect(lowerTargetNames).Any());

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