PostgreSQL 中的 JSONB 是数据库管理向前迈出的一大步。它混合了 NoSQL 和常规数据库的优点。本文着眼于 JSONB 在 PostgreSQL 中的作用,以及它如何与 Entity Framework Core 连接,帮助开发人员构建严重依赖数据的复杂应用程序。
JSONB 代表 JSON Binary,是 PostgreSQL 中的一种专用数据格式,用于存储 JSON 数据。它与 PostgreSQL 中的传统 json 数据类型的不同之处在于,它以分解的二进制格式存储数据。这种格式允许高效的数据处理,因为它消除了每次访问 JSON 数据时重新解析 JSON 数据的需要。
'->' 和 '->>' 运算符用于访问 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 对象中导航。
SELECT details#>>'{specs, resolution}' FROM products;
JSONB 查询可以与 SQL 功能集成,如 'JOIN'、'GROUP BY' 和聚合函数。
将一组 JSONB 值中的值聚合到单个 JSON 数组中。
SELECT jsonb_agg(details) FROM products;
使用键和值将 JSONB 值聚合到单个 JSON 对象中。
SELECT jsonb_object_agg(details->>'name', details->>'price') FROM products;
将最外层的 JSON 对象扩展为一组键值对。
SELECT jsonb_each(details) FROM products;
与 jsonb_each 类似,但以文本形式返回所有值。
SELECT jsonb_each_text(details) FROM products;
过滤 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"]';
检查 jsonb 属性的值是否在一组值内。
SELECT * FROM products WHERE details->>'category' IN ('Smartphone', 'Tablet');
添加一条新记录,其中包含包含完整 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 属性的情况下执行 SQL 联接。
SELECT * FROM orders JOIN products ON orders.product_id = (products.details->>'id')::uuid;
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; }
}
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();
}
}
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();
var latestProducts = await db.Products
.OrderByDescending(x => x.CreatedAt)
.Select(x => new { x.Name, x.Specifications.Material })
.AsNoTracking()
.ToListAsync();