使用 EF Core 的 PostgreSQL 中的 JSONB
使用 EF Core 的 PostgreSQL 中的 JSONB
概述：介绍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 与传统关系模型的使用，以最大限度地提高灵活性和性能。