PostgreSQL JSONB：关系数据库中的文档存储革命

还记得我第一次读到在数据库中存储JSON数据的场景。当时在一本纸质杂志(!)上初次接触NoSQL数据库概念，心想："这想法真奇怪，谁会选择用非表格格式存储数据？"尽管最初持怀疑态度，好奇心还是驱使我尝试了一番。

我使用的第一个NoSQL数据库是MongoDB，它采用BSON（二进制JSON）存储数据。这种无模式存储和闪电般的数据检索体验令人兴奋。但当我看到某开发者对MongoDB和PostgreSQL的基准测试后，热情消退了——他在无索引无约束的单表测试中发现，PostgreSQL性能与MongoDB不相上下。

此后，我偶尔在关系数据库（MS SQL等）中处理JSON数据，效果尚可。如今PostgreSQL对JSON的支持更加强大，值得重新审视其潜力。

PostgreSQL与JSON

PostgreSQL在2014年引入有限的JSON支持，比MongoDB晚两年，但比MS SQL早两年。

PostgreSQL提供两种类型：JSON和JSONB。本文将聚焦JSONB，因为JSON类型功能有限，仅适用于需要保留JSON空格的场景。本质上，JSON类型只是带验证的文本类型，性能平平。真正的强者是其孪生兄弟JSONB。

JSONB

JSONB以二进制格式存储数据，支持高效索引和查询。它提供丰富的操作符和函数：包含检查、存在性验证、路径查询等。通过GIN（通用倒排索引）实现JSON文档的快速检索。

在解析过程中会删除无关空格，这意味着JSONB不保留缩进或换行格式。优势在于访问时无需重复解析，与JSON类型形成鲜明对比。

应用场景

JSONB主要适用于：

存储半结构化数据
处理多态数据模型
支持演进式架构典型场景包括元数据、用户偏好设置、产品属性和事件日志。其JSON格式也与CQRS、事件溯源等架构模式完美契合。

你可能会问："这跟.NET有什么关系？" 作为多数.NET开发者，我日常主要使用Microsoft SQL。但我想探究最爱的工具——Dapper和Entity Framework——如何与PostgreSQL的JSONB协同工作。

Entity Framework Core集成

Entity Framework Core（EF Core）通过Npgsql提供程序原生支持JSONB。可将JSONB列映射到.NET类型（类或字典），EF Core自动处理序列化与反序列化。

多种映射方式：

简单字符串（需手动序列化）
EF拥有实体（复杂对象首选）
JsonDocument/JsonElement
强类型POCO（已弃用）
Utf8JsonReader底层解析

首选方案：EF拥有实体

public class UserSetting
{
  public string DisplayName { get; set; } = "Default User";
  public NotificationSettings Notifications { get; set; } = new();
}

public class NotificationSettings
{
  public bool EmailEnabled { get; set; } = true;
  public bool SmsEnabled { get; set; } = false;
  public bool PushEnabled { get; set; } = true;
}

// 映射配置：
// UserProfile是包含Settings列的标准实体
userProfile.OwnsOne(e => e.Settings, settings =>
{
  settings.ToJson(); // 标记为JSONB列
  settings.OwnsOne(s => s.Notifications); // 嵌套配置
});

属性查询示例：

var profiles = await context.UserProfiles
  .Where(p => p.Settings.Notifications.EmailEnabled) // 深度嵌套查询
  .ToArrayAsync();

动态架构方案 对于动态结构，可存储为字符串或JsonDocument：

public class UserProfile
{
  public JsonDocument? Events { get; set; }
}

// 数据存储：
var userProfile = new UserProfile
{
  Events = JsonDocument.Parse(
    """
       {
         "lastLogin": "2025-01-03T08:45:30", 
         "passwordChanged": "2025-01-02T16:20:15"
       }
    """)
};

注意：JsonDocument为只读API。如需修改需等待JsonNode支持（当前版本尚未实现）

JSON查询方法：

// 精确匹配查询
var profiles = await context.UserProfiles.Where(
   p => p.Events != null 
        && EF.Functions.JsonContains(p.Events, """{"lastLogin": "2025-01-03T08:45:30"}"""))
  .ToArrayAsync();

// 路径存在性查询
var profiles = await context.UserProfiles.Where(
  p => p.Events != null 
       && EF.Functions.JsonExists(p.Events, "$.logged"))
  .ToArrayAsync();

底层解析方案 使用Utf8JsonReader提升性能：

var sql = "SELECT \"Settings\" FROM \"UserPreference\"";
await using var command = new NpgsqlCommand(sql, connection);
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
  var utf8Data = reader.GetFieldValue<byte[]>(0);
  var jsonReader = new Utf8JsonReader(utf8Data);
  while (jsonReader.Read())
  {
    switch (jsonReader.TokenType)
    {
      case JsonTokenType.PropertyName:
        var propertyName = jsonReader.GetString();
        jsonReader.Read(); // 移动到值
        switch (propertyName)
        {
          case "Theme": // 主题属性处理...

Dapper集成

Dapper处理更直接，基础方案是映射为字符串：

using (var connection = new NpgsqlConnection(connectionString))
{
  var result = connection.Query("SELECT \"Settings\" FROM \"UserPreference\" WHERE id = @Id", 
                                new { Id = 1 });
  foreach (var item in result)
  {
    var jsonData = item.Settings; // Settings为字符串类型
  }
}

数据存储方案：

var settingsJson = JsonSerializer.Serialize(settings); // 手动序列化

const string insertSQL = 
  """
    INSERT INTO "UserPreference" ("Settings", "CreatedAt", "UpdatedAt")
    VALUES (@settingsJson::jsonb, @CreatedAt, @UpdatedAt) -- 类型转换
    RETURNING "Id";
    """;

var newId = await connection.ExecuteScalarAsync<int>(insertSQL, new {
    settingsJson, 
    CreatedAt = DateTime.UtcNow
});

Dapper无内置JSONB支持，需手动处理序列化或实现自定义转换器。

EF多态JSON映射

利用JSONB实现多态类型存储：

// 模型定义
public abstract class PaymentMethod
{
  public string Id { get; set; } = Guid.NewGuid().ToString();
  public abstract string Type { get; }
  public bool IsDefault { get; set; }
}

public class BankAccount : PaymentMethod
{
  public override string Type => "BankAccount";
  public string AccountNumber { get; set; } = "";
}

// 配置
modelBuilder.Entity<UserPaymentProfile>(entity =>
{      
  entity.OwnsMany(e => e.PaymentMethods, builder => builder.ToJson());
});

查询限制及解决方案：

// 此查询无效
var users = await context.UserPaymentProfiles
  .Where(p => p.PaymentMethods.Any(pm => pm is CreditCard))
  .ToArrayAsync();

// 有效替代方案
var users = await context.UserPaymentProfiles
  .Where(p => EF.Functions.JsonExists(p.PaymentMethods, 
          "$[*] ? (@.Type == \"CreditCard\")"))
  .ToArrayAsync();

多态JSON体验不如标准表流畅，替代方案是使用[JsonDerivedType]特性手动处理。

JSONB与（物化）视图

作为关系数据库熏陶的开发者，我习惯通过视图将JSONB数据规范化：

CREATE VIEW UserPreferenceTheme AS
SELECT
  id,
  (data->'theme')->>'action' AS theme  -- JSON路径提取
FROM UserPreference;

高性能方案——物化视图（需手动刷新）：

CREATE MATERIALIZED VIEW MV_UserPreferences AS
SELECT id, (data->'notifications')->>'email' AS email_pref
FROM UserPreference;

PostgreSQL 17的JSON_TABLE函数可简化此过程

JSONB内部原理

虽然JSONB操作简便，但了解其内部机制很有必要（本节不涉及.NET，可选读）。

存储机制

二进制存储优化读取性能
更新操作重写整个值（即使修改单个字段）
适合读多写少场景

索引优化 通过GIN索引大幅提升查询性能：

默认GIN：通用JSONB索引
专用GIN：针对包含查询优化

CREATE INDEX idx_gin_settings ON UserPreference USING GIN (Settings);

TOAST存储 JSONB属于TOASTable类型：

默认阈值2KB
超限数据自动压缩存入TOAST表
查看大小：

SELECT pg_column_size(settings) AS size_bytes 
FROM UserPreference WHERE id = 123;

若你使用PostgreSQL却未尝试JSONB，强烈建议立即体验。它在保持关系型优势的同时，为半结构化数据提供了强大支持。现代PostgreSQL完美架起关系型与文档型数据库的桥梁，实现双模型下的高效存储与查询。