在 EF Core 中操作 PostgreSQL 数据表的 JSONB类型字段

PostgreSQL 中的 JSONB 是数据库管理向前迈出的一大步。它混合了 NoSQL 和常规数据库的优点。本文着眼于 JSONB 在 PostgreSQL 中的作用，以及它如何与 Entity Framework Core 连接，帮助开发人员构建严重依赖数据的复杂应用程序。

了解 PostgreSQL 中的 JSONB

什么是 JSONB？

JSONB 代表 JSON Binary，是 PostgreSQL 中的一种专用数据格式，用于存储 JSON 数据。它与 PostgreSQL 中的传统 json 数据类型的不同之处在于，它以分解的二进制格式存储数据。这种格式允许高效的数据处理，因为它消除了每次访问 JSON 数据时重新解析 JSON 数据的需要。

JSONB 的优势

高效索引：JSONB 支持 GIN （广义倒排索引）和 B 树索引。这意味着搜索速度更快，在查询大型数据集时尤其有用。
数据灵活性：它允许存储和查询半结构化数据。这对于需要架构灵活性的应用程序特别有用。
运营效率：JSONB 提供了多种运算符来查询和操作 JSON 数据。它还支持全文搜索。

JSONB 基元和操作

选择数据

'->' 和 '->>' 运算符用于访问 JSONB 列中的对象字段和数组元素。'->' 运算符返回 JSONB 对象/数组，而 '->>' 返回文本。

SELECT details->'specs' FROM products;

筛选数据

'@>' 运算符检查左侧的 JSONB 值是否在顶层包含正确的 JSONB 路径/值条目。

SELECT * FROM products WHERE details @> '{"category": "Electronics"}';

为性能编制索引

在 jsonb 列上创建 GIN 索引以增强包含检查等操作。

CREATE INDEX idx_jsonb_gin ON products USING GIN (details);

使用嵌套的 JSON 数据

对于嵌套数据，'#>' 和 '#>>' 运算符可以在嵌套的 JSON 对象中导航。

SELECT details#>>'{specs, resolution}' FROM products;

将 JSONB 与 SQL 相结合

JSONB 查询可以与 SQL 功能集成，如 'JOIN'、'GROUP BY' 和聚合函数。

JSONB 聚合函数

jsonb_agg

将一组 JSONB 值中的值聚合到单个 JSON 数组中。

SELECT jsonb_agg(details) FROM products;

jsonb_object_agg

使用键和值将 JSONB 值聚合到单个 JSON 对象中。

SELECT jsonb_object_agg(details->>'name', details->>'price') FROM products;

JSONB 扩展函数

jsonb_each

将最外层的 JSON 对象扩展为一组键值对。

SELECT jsonb_each(details) FROM products;

jsonb_each_text

与 jsonb_each 类似，但以文本形式返回所有值。

SELECT jsonb_each_text(details) FROM products;

JSONB 查询示例

按顶级属性值筛选

过滤 jsonb 列在其顶层包含指定值的记录。

SELECT * FROM products WHERE details->>'brand' = 'Apple';

从 Items 中选择 Specific Attribute Value

从 jsonb 列中选择特定属性的值。

SELECT details->>'price' AS price FROM products;

筛选包含特定属性的项目

过滤在 jsonb 列中包含特定属性的记录。

SELECT * FROM products WHERE details ? 'warranty';

按嵌套属性值筛选

过滤 jsonb 列在嵌套对象中包含指定值的记录。

SELECT * FROM products WHERE details#>>'{specs, memory}' = '16GB';

按数组中的属性筛选

过滤 jsonb 数组包含具有特定属性值的对象的记录。

SELECT * FROM products WHERE details->'colors' @> '["red"]';

对属性使用 IN 运算符

检查 jsonb 属性的值是否在一组值内。

SELECT * FROM products WHERE details->>'category' IN ('Smartphone', 'Tablet');

插入 JSON 对象

添加一条新记录，其中包含包含完整 JSON 对象的 jsonb 列。

INSERT INTO products (details) VALUES ('{"name": "Smart Watch", "price": 250}');

更新/插入属性

修改现有属性或在 jsonb 列中添加新属性。

UPDATE products SET details = jsonb_set(details, '{sale}', 'true', true) WHERE details->>'category' = 'Electronics';

删除属性

从 jsonb 列中删除特定属性。

UPDATE products SET details = details - 'sale';

通过 JSONB 属性联接表

在条件涉及 jsonb 属性的情况下执行 SQL 联接。

SELECT * FROM orders JOIN products ON orders.product_id = (products.details->>'id')::uuid;

使用 EF Core 的 JSONB

EF Core with PostgreSQL 提供了用于管理和查询复杂数据结构的强大功能。其中一项功能是对 JSONB 的支持，JSONB 是 PostgreSQL 中的一种 JSON 二进制格式。

定义实体

我们的主要实体是 Product，代表我们库存中的商品。

public class Product  
{  
    public int Id { get; set; }  
    public string Name { get; set; }  
    public Specifications Specifications { get; set; }  
    public List<Review> Reviews { get; set; } = new();  
    public DateTimeOffset CreatedAt { get; set; } = DateTimeOffset.UtcNow;  
    public DateTimeOffset UpdatedAt { get; set; } = DateTimeOffset.UtcNow;  
    public Dictionary<string, string> Translations { get; set; } = new();   
}

规格： 一个嵌套对象，其中包含材料、颜色和尺寸等产品规格。
评论： 客户评论的集合。
翻译： 用于管理多种语言产品名称的字典。

Specification 类封装有关产品的详细信息。

public class Specifications  
{  
    public string Material { get; set; }  
    public string Color { get; set; }  
    public string Dimensions { get; set; }  
}

Review 类表示客户反馈。

public class Review  
{  
    public string User { get; set; }  
    public string Content { get; set; }  
    public int Rating { get; set; }  
}

配置 DbContext

ProductContext 对于配置 EF Core 以使用 PostgreSQL 和 JSONB 至关重要。

public class ProductContext : DbContext  
{  
    public DbSet<Product> Products => Set<Product>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)  
        => optionsBuilder  
            .UseNpgsql("YourConnectionStringHere");

    protected override void OnModelCreating(ModelBuilder modelBuilder)  
    {  
        modelBuilder  
        .Entity<Product>()  
        .OwnsOne(product => product.Specifications, builder => { builder.ToJson(); })  
        .OwnsMany(product => product.Reviews, builder => { builder.ToJson(); });

    modelBuilder.Entity<Product>()  
        .Property(p => p.Translations)  
        .HasColumnType("jsonb")  
        .IsRequired();  
    }  
}

ToJson（）的此方法指示 EF Core 将规范和评论视为 JSONB。
**Translations 属性：**配置为 JSONB 列以存储字典。

添加带有翻译的产品

using var db = new ProductContext();  
var newProduct = new Product  
{  
    Name = "Ergonomic Chair",  
    Specifications = new Specifications  
    {  
        Material = "Leather",  
        Color = "Black",  
        Dimensions = "24 x 24 x 35 inches"  
    },  
    Reviews = { new Review { User = "Alice", Content = "Very comfortable", Rating = 5 } },  
    Translations = {  
        { "en", "Ergonomic Chair" },  
        { "es", "Silla Ergonómica" }  
    }  
};  
db.Products.Add(newProduct);  
await db.SaveChangesAsync();

查询和更新翻译

var productToUpdate = await db.Products.FirstAsync();  
productToUpdate.Translations["de"] = "Ergonomischer Stuhl";  
await db.SaveChangesAsync();

使用 JSONB 进行投影

var latestProducts = await db.Products  
    .OrderByDescending(x => x.CreatedAt)  
    .Select(x => new { x.Name, x.Specifications.Material })  
    .AsNoTracking()  
    .ToListAsync();

最佳实践和考虑

平衡 JSONB 和规范化数据： 虽然 JSONB 很灵活，但重要的是不要过度使用它。在规范化关系数据和 JSONB 之间取得平衡通常是最有效的方法。
索引策略： 应仔细规划索引。虽然 GIN 索引功能强大，但它们可能是资源密集型的。
查询优化： 定期分析您的查询模式并使用 EXPLAIN 命令优化 JSONB 查询。
写入操作： 虽然 jsonb 对于读取很有效，但与传统的关系数据更新相比，更新嵌套属性等写入操作可能更加耗费资源。
内存使用情况： 聚合大型数据集时，jsonb_agg 等函数可能会消耗大量内存。
数据库迁移： EF Core 将在迁移中将 JSONB 列作为字符串（nvarchar（max））类型处理。
透明使用： 在 EF Core 中，JSONB 支持的属性的使用是无缝的。ORM 自动处理序列化和反序列化。
性能： 使用 JSONB 可以通过减少对多个联接的需求来优化数据检索。