概述：介绍PostgreSQL 中的 JSONB 是数据库管理向前迈出的一大步。它混合了 NoSQL 和常规数据库的最佳部分。本文着眼于 JSONB 在 PostgreSQL 中的作用，以及它如何与 Entity Framework Core 连接，帮助开发人员构建严重依赖数据的复杂应用程序。了解 PostgreSQL 中的 JSONB什么是 JSONB？JSONB 代表 JSON Binary，是 PostgreSQL 中用于存储 JSON 数据的一种专用数据格式。它与 PostgreSQL 中的传统 json 数据类型不同，因为它以分解的二进制格式存储数据。这种格式允许高效的数据处理，因为它消除

介绍

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';

从项目中选择特定属性值

从 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 的支持，这是 PostgreSQL 中的一种 JSON 二进制格式。

定义实体

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

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();   
}

• **规格：**包含产品规格（如材料、颜色和尺寸）的嵌套对象。
• **评论：**客户评论的集合。
• **翻译：**用于管理多种语言产品名称的词典。

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

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

评审类表示客户反馈。

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 可以通过减少对多个连接的需求来优化数据检索。

结论

PostgreSQL 中的 JSONB 与 EF Core 的集成为在关系数据库上下文中处理复杂、嵌套和动态的数据结构提供了可靠的解决方案。通过了解如何使用 JSONB 属性定义实体、配置上下文和执行 CRUD 操作，开发人员可以显著增强其应用程序的数据管理功能。关键是要平衡 JSONB 与传统关系模型的使用，以最大限度地提高灵活性和性能。