PostgreSQL's jsonb column type is not a glorified text field for stashing serialised strings. It is a binary-indexed, queryable, first-class storage format that gives you document-style flexibility inside a relational schema, without reaching for a separate document database. If you have ever pushed a JSON string into an nvarchar(max) column and then written ugly string parsing to query it, jsonb is the answer you were looking for.
EF Core 8 introduced proper JSON column support that Npgsql maps directly to jsonb. This post walks through defining the mapping, writing LINQ queries that PostgreSQL executes efficiently, and adding the right index so that power is not wasted on full scans.
Before starting, make sure you have:
- .NET 8 SDK or later
- PostgreSQL 14 or later (16 is recommended)
- Npgsql.EntityFrameworkCore.PostgreSQL 8.x NuGet package
- Familiarity with EF Core migrations and basic LINQ queries
ToJson() mapping requires EF Core 7 or later. All examples here use EF Core 8. Npgsql maps ToJson() to a jsonb column by default; it does not emit json (text) or nvarchar.
EF Core 8 uses owned entity types configured with ToJson() to project a C# object into a single JSON column. You define the C# shape normally, then tell EF Core to store it as JSON rather than as a separate table.
The entity model
public class Product
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public decimal Price { get; set; }
// This navigation property will become a single jsonb column
public ProductDetails? Details { get; set; }
}
public class ProductDetails
{
public string? Sku { get; set; }
public int StockQuantity { get; set; }
public List<string> Tags { get; set; } = new();
public Dictionary<string, string> Attributes { get; set; } = new();
}
Configuring the mapping
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<Product> Products => Set<Product>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Product>(entity =>
{
entity.HasKey(p => p.Id);
entity.OwnsOne(p => p.Details, details =>
{
details.ToJson(); // Npgsql emits: "Details" jsonb
});
});
}
}
What the migration generates
// The generated migration contains:
migrationBuilder.AddColumn<string>(
name: "Details",
table: "Products",
type: "jsonb", // Npgsql sets this; SQL Server would emit nvarchar(max)
nullable: true);
Run dotnet ef migrations add AddProductDetails followed by dotnet ef database update. Inspect the generated migration file to confirm the column type is jsonb before applying it.
jsonb stores JSON in a decomposed binary format. PostgreSQL parses and validates the document on write, making writes slightly heavier than the json (plain text) type, but reads are faster because the document does not need re-parsing on every access.
This is where jsonb earns its place. EF Core translates LINQ expressions against the owned type into PostgreSQL JSON path operators, which execute server-side without pulling every row into memory.
Filtering on a scalar JSON property
// Find all products currently in stock
var inStock = await context.Products
.Where(p => p.Details != null && p.Details.StockQuantity > 0)
.OrderBy(p => p.Name)
.ToListAsync();
// PostgreSQL executes something equivalent to:
// WHERE (details ->> 'StockQuantity')::int > 0
Filtering with a JSON array containment check
// Find products tagged as "clearance"
var clearance = await context.Products
.Where(p => p.Details != null && p.Details.Tags.Contains("clearance"))
.ToListAsync();
Combining relational and JSON filters
// Under $50, in stock, and tagged as "sale"
var saleItems = await context.Products
.Where(p =>
p.Price < 50m &&
p.Details != null &&
p.Details.StockQuantity > 0 &&
p.Details.Tags.Contains("sale"))
.Select(p => new
{
p.Id,
p.Name,
p.Price,
p.Details!.Sku
})
.ToListAsync();
Updating a JSON document
// Load, modify in memory, and save — EF Core tracks the change
var product = await context.Products
.FirstOrDefaultAsync(p => p.Id == productId);
if (product?.Details is not null)
{
product.Details.StockQuantity -= quantitySold;
product.Details.Tags.Add("low-stock");
await context.SaveChangesAsync();
}
// EF Core sends the full updated jsonb document back to PostgreSQL
jsonb document on update, not individual fields. For high-frequency partial updates to large documents, consider a raw SQL jsonb_set() call via ExecuteSqlRawAsync to minimise the payload.
Without an index every jsonb query performs a sequential scan. A GIN (Generalised Inverted Index) lets PostgreSQL push filters into the index structure rather than reading every row.
Full-document GIN index via a custom migration
EF Core's fluent API does not expose GIN index creation natively, so you add it in a raw SQL migration.
public partial class AddProductDetailsGinIndex : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(
"""
CREATE INDEX ix_products_details_gin
ON "Products"
USING GIN ("Details");
"""
);
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(
"""DROP INDEX IF EXISTS ix_products_details_gin;"""
);
}
}
Targeted expression indexes for specific paths
A full-document GIN index covers every key in the document but can grow large. If you only filter on one or two paths, a targeted expression index is leaner and faster for those specific queries.
-- Index just the StockQuantity path, cast to integer
CREATE INDEX ix_products_stock_qty
ON "Products" (( ("Details" ->> 'StockQuantity')::int ));
-- GIN index on only the Tags array within the document
CREATE INDEX ix_products_tags_gin
ON "Products"
USING GIN (("Details" -> 'Tags'));
Verifying the index is used
-- Run in psql or pgAdmin after loading representative data
EXPLAIN ANALYZE
SELECT * FROM "Products"
WHERE ("Details" ->> 'StockQuantity')::int > 0;
-- Look for "Index Scan using ix_products_stock_qty" in the output.
-- If you see "Seq Scan", run ANALYZE "Products"; to refresh statistics.
EF Core 8 and Npgsql together make jsonb columns a first-class part of your .NET data model. You get strongly typed C# objects, LINQ translation to efficient PostgreSQL JSON path operators, and index support that keeps reads fast as rows accumulate.
Use jsonb when your schema genuinely varies across rows or when you need semi-structured metadata alongside relational data. Do not use it as an excuse to avoid proper schema design; a relational model still outperforms jsonb for structured data with a known, stable shape.
No comments: