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

郑子铭

发布于 2024-12-25 14:45:59

2.4K00

代码可运行

文章被收录于专栏：DotNet NB && CloudNativeDotNet NB && CloudNative

运行总次数：0

代码可运行

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 classProductContext:DbContext
{
    publicDbSet<Product> Products =>Set<Product>();

    protectedoverridevoidOnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder  
            .UseNpgsql("YourConnectionStringHere");

    protectedoverridevoidOnModelCreating(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 =newProductContext();
var newProduct =newProduct
{
    Name ="Ergonomic Chair",
    Specifications =newSpecifications
    {
        Material ="Leather",
        Color ="Black",
        Dimensions ="24 x 24 x 35 inches"
    },
    Reviews ={newReview{ User ="Alice", Content ="Very comfortable", Rating =}},
    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();