如何在PostgreSQL中通过C#字典对JSON/JSONB列过滤查询数据？

嘿，我来帮你搞定PostgreSQL里JSONB列的过滤查询问题！

如果你的表现在用的是JSON类型的EntityData列，建议先改成JSONB——它是二进制存储格式，支持索引和更高效的JSON操作，对后续的过滤查询友好太多：

ALTER TABLE your_table_name ALTER COLUMN EntityData TYPE JSONB USING EntityData::JSONB;

如果过滤条件是固定的，比如要找Name为mike且Location为Lagos的行，有两种简单写法：

方法一：用@>操作符（简洁高效） #

这个操作符会直接检查JSONB列是否包含你指定的所有键值对，非常适合精确匹配多个字段的场景：

SELECT ID, EntityData
FROM your_table_name
WHERE EntityData @> '{"Name": "mike", "Location": "Lagos"}'::JSONB;

方法二：逐个键值判断（更灵活） #

如果你需要对单个字段做更复杂的判断（比如模糊匹配），可以用->>提取JSON字段的文本值再比较：

SELECT ID, EntityData
FROM your_table_name
WHERE EntityData->>'Name' = 'mike'
  AND EntityData->>'Location' = 'Lagos';

因为你的过滤条件是Dictionary<string, object>，得动态构建安全的查询（避免SQL注入），这里推荐用Npgsql（PostgreSQL官方的C#驱动）来实现：

步骤1：先装Npgsql包 #

在NuGet里安装Npgsql（原生驱动）或者Npgsql.EntityFrameworkCore.PostgreSQL（如果用EF Core）。

步骤2：原生Npgsql实现动态过滤 #

把Dictionary序列化成JSON字符串，作为JSONB参数传给数据库，既安全又高效：

using Npgsql;
using System.Collections.Generic;
using Newtonsoft.Json;

public List<YourEntity> GetFilteredRows(Dictionary<string, object> filter)
{
    var result = new List<YourEntity>();
    
    // 把Dictionary转成JSON字符串，作为JSONB参数
    var filterJson = JsonConvert.SerializeObject(filter);
    var jsonbParam = new NpgsqlParameter("filter", NpgsqlTypes.NpgsqlDbType.Jsonb)
    {
        Value = filterJson
    };

    // 构建查询SQL
    string sql = @"
        SELECT ID, EntityData
        FROM your_table_name
        WHERE EntityData @> @filter;
    ";

    // 连接数据库执行查询
    using var conn = new NpgsqlConnection("你的数据库连接字符串");
    conn.Open();
    using var cmd = new NpgsqlCommand(sql, conn);
    cmd.Parameters.Add(jsonbParam);
    
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        result.Add(new YourEntity
        {
            ID = reader.GetInt32(0),
            EntityData = JsonConvert.DeserializeObject<Dictionary<string, object>>(reader.GetString(1))
        });
    }
    
    return result;
}

// 定义你的实体类，对应表结构
public class YourEntity
{
    public int ID { get; set; }
    public Dictionary<string, object> EntityData { get; set; }
}

步骤3：EF Core的实现方式 #

如果用EF Core，可以直接用JsonbContains方法动态组合过滤条件：

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;

public class YourDbContext : DbContext
{
    public DbSet<YourEntity> YourTable { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("你的数据库连接字符串");
    }
}

public List<YourEntity> GetFilteredRowsWithEfCore(Dictionary<string, object> filter)
{
    using var dbContext = new YourDbContext();
    
    return dbContext.YourTable
        .Where(e => EF.Functions.JsonbContains(e.EntityData, JsonConvert.SerializeObject(filter)))
        .ToList();
}

如果这个查询很频繁，给EntityData列加个GIN索引，能大幅提升大表的查询速度：

CREATE INDEX idx_entitydata_gin ON your_table_name USING GIN (EntityData);

GIN索引专门针对JSONB的包含查询做了优化，效果很明显。

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